How to avoid Table Spool and Hash Match.

Birr 2015-04-23 20:17:35


Is there any way I can optimize this query?


SQLkiwi 2015-04-24 03:56:13
There are a number of things you should do:

  1. Update your statistics, especially on dbo.PropVal
  2. Consider a nonclustered index on dbo.PropVal (Item, Value, Symbol)
  3. Review whether the Value column really needs to be a LOB type (e.g. nvarchar(max))
  4. Stop sprinkling NOLOCK hints everywhere and be sure you understand the risks
  5. Consider a computed column on dbo.Crons SUBSTRING([CronID],1,CHARINDEX('.',[CronID])-1)
  6. Create a nonclustered index on that computed column, [Date] and [Value].
  7. Correct the mismatched data types in the query, for example on expressions comparing [Value], [Symbol], and [Date]. Pay particular attention to Unicode strings and date/time types.

It is difficult to solve all the issues in that query in one step based on a single execution plan, but hopefully those ideas will lead you in the right direction. You may also find it helpful to break this query into smaller parts while you are tuning it – even if you subsequently recombine the blocks into a single monolithic query. Finding problems and addressing them is always easier when the problem is smaller 🙂

Birr 2015-04-24 15:43:17
Thank you so much Paul. I did the following:

  1. Statistics are up-to-date. They are updated daily.
  2. There is a nonclustered index on dbo.ProbVal (Item, Symbol). The Value column is nvarchar(max).
  3. Yes the Value column is nvarchar(max).
  4. I will make sure I remove them before I deploy it to production. This was only for test.
  5. Created a computed column on dbo.Crons SUBSTRING([CronID],1,CHARINDEX('.',[CronID])-1)
  6. Created nonclustered index on the computed column and Date, in this order. The dbo.Crons table does not have Value column, which is on the dbo.PropVal table. After all these corrections, the performance went down from 3.5 minutes to 3 seconds.
  7. I am trying to correct the mismatch data types..

After all these steps, the performance went down from 3.5 minutes to 3 seconds. I know the impact of the "WITH (NOLOCK)", which gets uncommitted transaction from cache (dirty pages)… I was testing the script and posted the script without removing the table hints.

Thank you so much once again Paul.


Kevin 2015-04-30 21:41:37
Just out of curiousity, Birr, I'd love to see the "after" execution plan following the addition of these suggestions.