Posted by Erland Sommarskog on 08/22/05 13:37

martin_rendell@hotmail.com (martin_rendell@hotmail.com) writes:
> A stored procedure was running slowly so I took the code, removed the
> subselect and included a join, then took the max and included as part
> of a correlated subquery.
>
> The result is below, however, this is no improvement over the original.
> An advice would be greatly appreciated.

Without knowledge of the tables and index, and what sizes they are, it
is impossible to say "fix this!". It could be that you need a new index.
It could be that statistics are poor. It could be that everything is up
to shape, but the optimizer makes an incorrect estimate.

What you can do on your own, is to put the query from Query Analyzer and
press CTRL-L. This gives you the estimated execution plan.

However, if this code is in a stored procedure, I suspect that several of
the numbers below are parameters or variables. This has an impact on the
query plan. In such case, it is better to run the stored procedure, and
prior that press CTRL-K to get the actual execution plan.

Once you have the execution plan, you might be able to make some findings.

If you can't make it out, please post:

o CREATE TABLE statements for the tables.
o CREATE INDEX statements for the tables.
o Approx no of rows per table.
o The output from SET STATIISTICS_PROFILE ON. Run this command first,
the procedure.



--
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]