Posted by Erland Sommarskog on 07/09/05 01:28

sjoshi (sjoshi@ingr.com) writes:
> Now when I try this query, it's taking 8-10mins.

While we got the tables and the query, we did not get any details on
the amount of data involved. For instance, how many rows could we
expect in the involved tables?

Here are some assorted thoughts:

o Replace table variable with temp tables. Temp tables have statistics,
and this may the optimizer a second chance for a better job.
o Add a WHERE clause to insert only the dispid of interest into
@COREBSTRAttribute
o Leave out Value from @COREBSTRAttribute. That serves to make the
table slower.
o Add a clustered index on dispid. (Possible for temp table.)
o Long IN clauses are takes long time to optimize. Could be a better
idea to put the guids into a temp table instead.
o You insert into a table variable @t. When you insert into temp
variables, you kill parallelism. Someties parallelism is good for
your queries. (Sometimes it is not!)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]