TSQL Help

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_PAYME
NT_PERIOD_=ADID) AS CPP FROM
ACTUALS ACT WHERE  COLLECTION_PAYMENT_P
ERIOD_ID<=3D=AD456) AS O ON O.CPP =3D
 A=2ECOLLECTION_PAYME
NT_PERIOD_ID
INNER JOIN  FS_ACTUAL_LINE_TYPES
 FSALT ON FSALT. FS_ACTUAL_LINE_TYPE_
ID =3D

 A=2EFS_ACTUAL_LINE_T
YPE_ID
INNER JOIN PAYMENT_PERIODS PP ON PP.PAYMENT_PERIOD_ID =3D
 A=2EPAYMENT_PERIOD_I
D
WHERE
A.ORG_ID=3D24771
AND A.LSC_ORG_ID=3D5816
AND PP. FUNDING_STREAM_ID=3D
5
AND PP. FUNDING_PERIOD_ID=3D
6
GROUP BY
FSALT. FUNDING_LINE_TYPE_ID
,
A. PAYMENT_PERIOD_ID,=2
0
A.CASH,=20
A.VOLUME

hi
try to implement index on  COLLECTION_PAYMENT_P
ERIOD_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.droptable.com ***

martin_rendell@hotma
il.com  (martin_rendell@hotm
ail.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/techin.../2000/books.asp