|
Home > Archive > Microsoft SQL Server forum > August 2005 > TSQL Help
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| martin_rendell@hotmail.com 2005-08-22, 7:24 am |
| 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
| |
|
|
| Erland Sommarskog 2005-08-22, 7:24 am |
| 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
|
|
|
|
|