Would indexes on any of the Temp tables help this query ?

Pearljammer1 2016-01-15 16:22:07

I am trying to elliminate the HASH Matches. Would adding indexes on any of the temp tables help here ? If so which ones ?

SQLkiwi 2016-01-16 02:59:53
I can't see any particular reason to avoid the hash joins here.

As far as the indexing of the temporary tables is concerned, most tables benefit from a clustered index. As a minimum, I would suggest you create a unique clustered index (or clustered primary key) on each temporary table.

For the ones I looked at, the obvious candidate was the id column. As a general guide, use whatever column or combination of columns is needed to define a key for the table. If subsequent analysis suggests an alternate key, or additional indexing, consider each on its merits.

The query has too many views (without definitions) and joins to make a proper analysis. The plan wasn't collected by running the query directly from Plan Explorer either, so is missing performance information.

Pearljammer1 2016-01-16 19:57:14
Ok thanks for your response. When you say the plan is missing performance information because it wasn't run directly in Plan Explorer can you elaborate please ? It is true I ran it within ssms then used the 'open with plan explorer' option from within ssms. Is there a better way of doing this which yields more information ?
SQLkiwi 2016-01-17 00:30:18
Simply put the command in the Command Text and click Get Actual Plan.