TSQL Help

[martin_rendell@hotmail.com]

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_L INE_TYPE_ID,
A.PAYMENT_PERIO D_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_PAYM ENT_PERIOD_ID<= 456) AS O ON O.CPP =
A.COLLECTION_PA YMENT_PERIOD_ID
INNER JOIN FS_ACTUAL_LINE_ TYPES FSALT ON FSALT.FS_ACTUAL _LINE_TYPE_ID =

A.FS_ACTUAL_LIN E_TYPE_ID
INNER JOIN PAYMENT_PERIODS PP ON PP.PAYMENT_PERI OD_ID =
A.PAYMENT_PERIO D_ID
WHERE
A.ORG_ID=24771
AND A.LSC_ORG_ID=58 16
AND PP.FUNDING_STRE AM_ID=5
AND PP.FUNDING_PERI OD_ID=6
GROUP BY
FSALT.FUNDING_L INE_TYPE_ID,
A.PAYMENT_PERIO D_ID,
A.CASH,
A.VOLUME

[Chandra]

Re: TSQL Help

hi
try to implement index on COLLECTION_PAYM ENT_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]

Re: TSQL Help

martin_rendell@ hotmail.com (martin_rendell @hotmail.com) writes:[color=blue]
> 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.[/color]

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_PRO FILE ON. Run this command first,
the procedure.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp