I'm just learning Performance Tuning and would REALLY appreciate any assistance or suggestions.
By MikeS 1 asked Nov 08, 2017 at 12:26 AM
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:
By SDyckes2 81 answered Nov 08, 2017 at 09:19 PM
Improved procedure has been re-submitted at https://answers.sqlperformance.com/questions/4812/how-to-improve-this-procedure-part-2.html 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.
By MikeS 1 answered Nov 13, 2017 at 01:06 AM