TSQL Help

[martin_rendell]

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.

SELECT FSALT.FUNDING_LINE_TYPE_ID,
A.PAYMENT_PERIOD_ID,
A.CASH AS CASH,
A.VOLUME AS VOLUME
FROM ACTUALS A
INNER JOIN (SELECT MAX(COLLECTION_PAYMENT_PERIOD_*ID) AS CPP FROM
ACTUALS ACT WHERE COLLECTION_PAYMENT_PERIOD_ID<=*456) AS O ON O.CPP =
A.COLLECTION_PAYMENT_PERIOD_ID
INNER JOIN FS_ACTUAL_LINE_TYPES FSALT ON FSALT.FS_ACTUAL_LINE_TYPE_ID =

A.FS_ACTUAL_LINE_TYPE_ID
INNER JOIN PAYMENT_PERIODS PP ON PP.PAYMENT_PERIOD_ID =
A.PAYMENT_PERIOD_ID
WHERE
A.ORG_ID=24771
AND A.LSC_ORG_ID=5816
AND PP.FUNDING_STREAM_ID=5
AND PP.FUNDING_PERIOD_ID=6
GROUP BY
FSALT.FUNDING_LINE_TYPE_ID,
A.PAYMENT_PERIOD_ID,
A.CASH,
A.VOLUME

[Chandra]

hi
try to implement index on COLLECTION_PAYMENT_PERIOD_ID
this might improve the performance of ur query

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

ma************@hotmail.com (ma************@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, es****@sommarskog.se

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