Consulting 101-105, SQL Server Performance Tuning by Pinal Dave

Consulting 101 – Why Do I Never Take Control of Computers Remotely?

Consulting 102 – Why Do I Give 100% Guarantee of My Service?

Consulting 103 – Why Do I Assure SQL Server Performance Optimization in 4 Hours?

Consulting 104 – Why Do I Give All the Performance Tuning Scripts to My Customers?

Consulting 105 – Why Don’t I Want My Customers to Return for the Same Problem?

Advertisements

SQL Server function to return the “Xrd” day of a month and year.

USE [Tools]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- select dbo.XrdDay(4,'thursday', 'november', 2018)	-- Thanksgiving
-- select dbo.XrdDay(4,'thursday', 'november', 2019)	-- Thanksgiving
-- select dbo.XrdDay(4,'thursday', 'november', 2020)	-- Thanksgiving

-- Must have the day of week and the name of month spelled correctly.
alter function [dbo].[XrdDay](
	@xrd int				--3rd, 4th, 5th, etc.
	,@dayname varchar(8)	--'saturday'
	,@month varchar(10)		--'february'
	,@year smallint			--year
)
returns date
as 
begin
	-- declare @xrd int = 3; declare @dayname varchar(8) = 'monday'; declare @month varchar(10) = 'september'; declare @year smallint = 2019;

	declare @Result date=  @month + ' ' + convert(varchar(64),(@xrd*7) - 6) + ',' + convert(varchar(64), @year);	-- get Sunday.
	--select @Result, datename(weekday, @Result);

	--I'm fairly sure this is inefficient, but I can't seem to recall of another way around it.
	--to make it better, we need to calc @sunday+int==dayname in one go, not this while loop.
	while datename(weekday, @Result) collate SQL_Latin1_General_CP1_CI_AS != @dayname set @Result = dateadd( day, 1, @Result );
	--select @Result, datename(weekday, @Result);

	return @Result;
end;

Slightly Annoying…

So. Our team worked to improve the performance of the company’s two 16-year old desktop C# applications.. I focused my work on the applications and on the database procedures.

I personally made the main application load in 1 second on an Intel i5 with 8gb of ram running around 3ghz.. when it used to take ~10 seconds to load on the same types of computers. Literally 10 times faster!

I also cut the codebase literally in half, coalescing logic that was duplicated across multiple methods. Also fixed all known errors (and found some traps that would have been bad for the company.. such as calculating taxes wrong).

I changed the main internal logic to use awaits and async where appropriate. I also added error trapping (catching & logging logic/network/user exceptions) to almost the entire codebase.

I updated the .NET framework to the latest version.

I found and fixed all possible SQL injections.. the company had had no idea that the injections could even have happened. I made the database calls clean, fast, and error free. Cached static (information that rarely changed) responses so the geographically remote branches only had to pull information once from the main database. And there used to be methods that made the same database calls information repeatedly.. sometimes a single keypress would fire off a chain of duplicated database calls.

I created unit tests.. the CIO of the company had no idea what a “unit test” was.. or how to create & use LINQ in C#. I fixed most of the Array(s) of Objects that they stuffed Controls into because they wanted a “dynamic” interface. (man it was horrible!)

The rest of the team and I (mostly 40% me and 60% them) also reworked the database tables to use the proper types. For example: changing bigint or int down to tinyint where appropriate. And tinyint to ints. Datetimes to Date where the “time” part was not needed.

We normalized almost all of the tables.. removed any unused or duplicate indexes.. the applications performances’ became very fast and responsive.


I spent the entire year fixing this mess they had created with multiple so-called ‘programmers‘ over the last 16 years. I worked nearly constantly.. never spent more than a minute or two away from coding. Excluding meetings, sick days, bathroom breaks, and lunch of course. If I was at my desk, then I was working.

I also took on the task with half the salary it should have been because I liked the company. Big Mistake™ on my part.. I blindly ignored the fact that companies can and will replace you with a cheaper programmer on a whim.

And can you guess what they did on the day I fixed the last known bug? They let me “go“. As in, “See yah, now we don’t need yah!”. Didn’t even get a thank you “for your hard work and saving the company thousands of dollars!” 🗯

I was like “Really? How rude..!

But I also figured, “Okay.. they can pay someone else to keep this application maintained and updated by the newbie programmer they hired next.. the bosses’s son.”

Professional nepotism.. and that’s what is annoying.

Oh well.. I’ve updated my résumé and moved on from that nightmare of a mess we had fixed.

Thanks for reading my ‘venting’.

SQL Server: What is Data Warehousing?

A data warehouse is the main repository of a company’s historical data.

Data warehouses can contain a variety of data that presents a coherent picture of the business’s conditions at a point in time.

The main factor of using a data warehouse is that analysts can perform complex queries (data mining) on the information without slowing down the production database servers.