.net_2.0

SQL Server and ADO - Query Performance

As of late I have been knee deep in both C# and SQL performance optimizations and tuning.  From the C# side of the client's application, I was able to make enough improvements that the application ran incredibly stable at 99% cpu utilization; however, the ability to determine the actual extent the coding changes would add to performance (if any) was being negated by some of the SQL queries being used. 

The major identified issue is there is a query that runs on each page invoked by the application, that in the end actually imposes a bottleneck as the queries execution time is exponentially incremented as the test cycle procedes in duration. 

Tackling the Situation

Before changing code to reflect a ton of changes, the first step is to use the SQL Execution Plan and Client Statistics options available when running a new query window.  What I do first is use the SQL Profiler tool to capture the 'exec sp_executesql' being sent from the web application.  Clip and paste into a new query window and view the Execution Plan and Client Statistics.  Next make changes to the sql being executed to include the performance changes to be tested - capture like statistics and compare the results.  It is important to note that even when the proposed changes seem to indicate that a performance benefit may be had - it will still have to be rolled into the application and stress tested.  However, it gives a decent baseline to compare a simple scenario and see what the differences are....

1. The first changes were ading the proper ownership to the table names referenced.  For example if you "Select * From Accounts" then you would might for example change this to "Select * From dbo.Accounts". 

Example Test:

This was a quick test by manually executing the queries...as can be see there is a difference - and the larger table data indicated that if the test was run for more iterations the with dbo scenario Wait Time on Server replies would actually decrease.  There is a difference and this particular routine is run on each page load so in the end the expected result to see in a load test would be around a 7-10% perf improvement in regards to SQL which eeks out about 1-2% with the C# application.  Making those changes globally however, should result in a bigger return on the investment...

2. Re-thinking SQL queries when using nested Select statements...

The issue with not using SQL stored procedures and streaming instead the sql through the commandtext is that you may or may not et to fully utilize the SQL caching and optimization features.  This can result in a huge performance hit when it is all said and done.

Let's give you a scenario...

You have a "Select * from dbo.Orders where account_id = (Select accountID from dbo.Accounts where UserID = @UserID) ..."

One would think this would be the most efficient way to handle this kind of data retrieval.  However, in high load enviroments and where you have a large number of records in the corresponding tables - the index locks remain until the very last Index is searched which unexpectedly is the getting the account_id.  Here is an example Execution Plan:

Note that the sql being executed shows that the last action is the index search for the account_id.

TIP #1: If you only need a single record - use the SELECT TOP 1 so that the select stops as soon as the first record is found.  For instance if userID has to be unique and you know that there is only one - this tip will immediately cause SQL to release any index locks or other locks that you may have defined as soon as the match is made...

Tip #2: If you are not using stored procedures break up the nested select statements and instead do something like this:

Declare @AccountID int; SET @AccountID=(Select accountID from dbo.Accounts where UserID = @UserID); Select * from dbo.Orders where account_id = @AccountID

This may not work all the time for all scenarios but if you are not relying on the need to do inner, outter and other joins.... this breaks up the SQL Execution Plan so that it optimizes for getting the account id as well as the select for orders.  The performance benefit can be huge as illustrated here:

 With Nested Selects:

Compare to the TIP #2

 Here is the new Execution Plan...

While not tested yet under load - however, this does look promising...And it is not quite what I expected in terms of the huge difference in the computed results...If the SQL stats being reported are accurate then projected I can substaintially reduce the workload on SQL and my client's web application will see a tremendous boost in performance...

These are just a couple of tips if using ADO - instead of SQL SPs....  and test these tips out for yourself.  If indexes and the likes are all up to par and you are trying to reduce the bottlneck you know exists these may work or may not.  With SQL it all depends on how complex the queries are and how large or small your tables may be...

NOTE: The examples are presented using shorthand.  For instance never use Select * - instead specifically list out the coulmns you want to retrieve even if it means all of them... I used shorthand as there was no need to add complexity to a simple illustration.  The tests that were run utilized listing out all columns and using the Top 1 tip where applicable...

I'll update this blog entry once I get a chance to evaluate how the SQL Client Statistics Reports compare to actual results in a stress test enviroment.....