Sql have required indexes.But still it is running slow.How to increase performance of the query

Sasi 2014-01-29 15:23:40

Aaron Bertrand 2014-01-29 15:49:23
Are statistic up to date? You have some pretty crazy discrepancies in actual vs. estimated rows, most notably the index scan on [M360_dev4].[dbo].[CMGMT_CONSTITUENT_OWNER].[CMGMT_CONSTITUENT_OWNER_cnst_office_primary].(NonClustered) and the table spool that occurs later in that plan (the statement with 70 second duration). Also you are joining 13 tables there, as well as in other statements, and a lot of it seems quite redundant. I'm not sure there's much you can do to refactor the query alone and improve performance so that this query runs faster than a couple of minutes. Actually I'm quite surprised and impressed that this does run that quickly.
Dan Holmes 2014-01-29 15:29:04
Is this SQL in a proc? Are the variables local? Have you tried OPTION(RECOMPILE)?
Sasi 2014-01-29 17:07:51
yes I am using this in a stored procedure
Kevin 2014-02-14 18:28:19
The statistics are clearly messed up.

Also, there are some possibly unnecessary bits of code in there that you might be able to drop out. For example, the WHERE 1=1 clauses always evaluate to TRUE and are entirely unneeded. The ORDER BY statements add more steps to the plan. If you can move the ORDER BY from the INSERT statements to the SELECT * FROM #constituents, you'll save a lot of work. The most draining, though, are the GROUP BY clauses. I don't see any use of aggregate function calls, so can you live without the GROUP BY clauses and still get the same result set? If so, you'll save a TON of processing.