How to improve this procedure??

I'm just learning Performance Tuning and would REALLY appreciate any assistance or suggestions.

avatar image By MikeS 1 asked Nov 08, 2017 at 12:26 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

First, check the statistics are up to date on the Contracts table. The Estimated Rows are significantly lower than the Actual Rows. This can indicate the statistics are out of date and cause poor performance.

In the stored procedure, proc_GetContractsDataPerTimeZone, the same basic block of code is used as a sub-query multiple times. By building a temp table with this information, it can be built once and used as many times as needed, saving the time and resources used to build it again and again.

Build the temp table with all the data you will need to return throughout its use as well as any columns that may be used in additional filters, like the UTCmatched column. You may have to use it in an additional sub-query to build a new sub-set of data from it with a new ROW_NUMBER () generated when needed.

This is the block I am referencing: alt text

avatar image By SDyckes2 96 answered Nov 08, 2017 at 09:19 PM
more ▼
(comments are locked)
avatar image MikeS Nov 09, 2017 at 11:12 PM

Thanks, this is awesome, I will look into it and post results. Much appreciated though (I had thought about making a temp table but thought that perhaps SQL Server was smart enough to know the same block of code was being repeated and thus could be re-used).

avatar image SDyckes2 Nov 09, 2017 at 11:14 PM

Maybe it will be a feature in a future version SQL with AI, but it currently does not.

10|10000 characters needed characters left

Improved procedure has been re-submitted at with the following note:

Performance improved from implementing the suggestions of @SDyckes2 to build an #TempTable inside the procedure.

Further improvements (seemed) to result from adding PK to the #temptables that the input parameters were inserted into BUT performance (seemed) to diminish from adding a PK to the internally created #TempTable.

Nonetheless I still have two (seeming) problems in which the Est. Rows vs. Actual Rows differ (by 10x underestimated for the insertions into #TempTable and 140X overestimated for the data output).

Thanks in advance for any and all suggestions.

avatar image By MikeS 1 answered Nov 13, 2017 at 01:06 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

We are Moving!


Follow this question



asked: Nov 08, 2017 at 12:26 AM

Seen: 53 times

Last Updated: Nov 13, 2017 at 01:06 AM