How to improve this procedure??

MikeS 2017-11-08 00:26:22

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

SDyckes2 2017-11-08 21:19:55
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

MikeS 2017-11-09 23:12:35
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).
SDyckes2 2017-11-09 23:14:26
Maybe it will be a feature in a future version SQL with AI, but it currently does not.
MikeS 2017-11-13 01:06:31

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.