May 13, 2004 at 7:29 am
I am currently testing putting some long running reports which would have the same execution plan over and over into sprocs. I have had varying success. First run it takes a while since it has to compile and determine execution plan. Second time definite improved performance. Then its all over the place. 3min, 10min, 13min, 8min, 1min all within the same day. This is running the exact same SQL with no input parameters. Also I run update stats on each database nightly. From what I have read I am concerned it has to recompile/re-optimize anytime after updating stats. Any suggestions or documents on this topic would be greatly appreciated.
cortec
May 13, 2004 at 7:34 am
Have you looked at other queries that are running when executing your proc that could account for variations in run times?
May 13, 2004 at 7:49 am
I ran this in a test environment and was pretty isolated. The box was not being slammed nor was it competing with other processes.
May 13, 2004 at 11:18 pm
First off, if the sprocs are using cursors and don't do any updates (i.e. update using the cursor), ensure that the cursors are declared as read-only.
From the symptoms described, it is just possible that the sprocs are running into lock contentions...
May 14, 2004 at 5:57 am
Can you post your query (edit security items to another name if needs be). May be a logic issue that throws it like this that someone can help with. Either way thou if we can see it we can better understand the potential issues.
May 14, 2004 at 8:50 am
I do not think its the code. I am testing sprocs with two completely different sets of code in two environments. One on a SAN and one not. This one is on the SAN. I created a job to run this in SQL Agent so I could run it again and again and keep a history of run times. First run this morning 8min. Second run shortly after 9min. returning 18K rows.
select distinct
E.LOCATION
,D.CUST_ID
,RTRIM(E.LOCATION) + RTRIM(E.DEPTID) + RTRIM(D.CUST_ID) + RTRIM(E.PAYGROUP)
,E.DEPTID
,E.EFFDT
,B.NATIONAL_ID
,RTRIM(B.NATIONAL_ID_TYPE)
,A.EMPLID
,C.EMPL_RCD
,C.TASKGROUP
,E.BUSINESS_UNIT
,E.SETID_LOCATION
,E.SETID_DEPT
,E.SETID_JOBCODE
,E.JOBCODE
,D.BUSINESS_UNIT_PC
,D.PROJECT_ID
,D.SETID_CUST
,D.ACTIVITY_ID
,A.NAME
,D.FO_CUST_NAME
,F.TRC
,F.FO_BILL_RATE
,F.FO_PAY_RATE
,E.COMPANY
,E.PAYGROUP
,H.PAY_END_DT
,H.PAY_BEGIN_DT
,CONVERT(CHAR(10),EX.EFFDT,112)
,EX.EFFDT
,CONVERT(CHAR(10),H.PAY_END_DT,20)
,CONVERT(CHAR(10),H.PAY_BEGIN_DT,112)
FROM PS_PERSON_NAME A,
PS_PERS_NID B,
PS_TL_EMPL_DATA C,
PS_FO_EMPX_CUST_VW D,
PS_JOB E,
PS_JOB EX,
PS_FO_PRJ_RATES F,
PS_PAY_CALENDAR H
WHERE A.EMPLID = B.EMPLID
AND B.PRIMARY_NID = 'Y'
AND C.EMPLID = A.EMPLID
AND C.EFFDT = (SELECT MAX(C_ED.EFFDT)
FROM PS_TL_EMPL_DATA C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.EMPL_RCD = C_ED.EMPL_RCD
AND C_ED.EFFDT <= H.PAY_END_DT)
AND C.EMPLID = D.EMPLID
AND C.EMPL_RCD = D.EMPL_RCD
AND D.EMPLID = E.EMPLID
AND D.EMPL_RCD = E.EMPL_RCD
AND(
( E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_JOB E_ED
WHERE E.EMPLID = E_ED.EMPLID
AND E.EMPL_RCD = E_ED.EMPL_RCD
AND E_ED.EFFDT <= H.PAY_END_DT)
AND E.EFFSEQ = (SELECT MAX(E_ES.EFFSEQ) FROM PS_JOB E_ES
WHERE E.EMPLID = E_ES.EMPLID
AND E.EMPL_RCD = E_ES.EMPL_RCD
AND E.EFFDT = E_ES.EFFDT)
AND E.EMPL_STATUS NOT IN ('T', 'R'))
OR
( E.EFFDT = (SELECT MAX(E_EDX.EFFDT) FROM PS_JOB E_EDX
WHERE E.EMPLID = E_EDX.EMPLID
AND E.EMPL_RCD = E_EDX.EMPL_RCD
AND E_EDX.EFFDT BETWEEN H.PAY_BEGIN_DT AND H.PAY_END_DT
AND E_EDX.ACTION IN ('TER', 'RET'))
AND E.EFFSEQ = (SELECT MAX(E_ESX.EFFSEQ) FROM PS_JOB E_ESX
WHERE E.EMPLID = E_ESX.EMPLID
AND E.EMPL_RCD = E_ESX.EMPL_RCD
AND E.EFFDT = E_ESX.EFFDT
AND E_ESX.ACTION IN('TER', 'RET')) )
)
AND EX.EMPLID = E.EMPLID
AND EX.EMPL_RCD = E.EMPL_RCD
AND EX.EFFDT = (SELECT MAX(EX1.EFFDT)
FROM PS_JOB EX1
WHERE EX1.EMPLID = EX.EMPLID
AND EX1.EMPL_RCD = EX.EMPL_RCD
AND EX1.EFFDT <= H.PAY_END_DT
AND EX1.ACTION in ('ADL', 'HIR'))
AND EX.EFFSEQ = (SELECT MAX(EX2.EFFSEQ)
FROM PS_JOB EX2
WHERE EX2.EMPLID = EX.EMPLID
AND EX2.EMPL_RCD = EX.EMPL_RCD
AND EX2.EFFDT = EX.EFFDT
AND EX2.ACTION in ('ADL', 'HIR'))
AND F.BUSINESS_UNIT_PC = D.BUSINESS_UNIT_PC
AND F.PROJECT_ID = D.PROJECT_ID
AND F.ACTIVITY_ID = D.ACTIVITY_ID
AND F.EFFDT = (SELECT MAX(F_ED.EFFDT) FROM PS_FO_PRJ_RATES F_ED
WHERE F_ED.BUSINESS_UNIT_PC = F.BUSINESS_UNIT_PC
AND F_ED.PROJECT_ID = F.PROJECT_ID
AND F_ED.ACTIVITY_ID = F.ACTIVITY_ID
AND F_ED.EFFDT <= H.PAY_END_DT)
AND F.TRC = (CASE B.NATIONAL_ID_TYPE
WHEN 'CON' THEN '901'
ELSE '001'
END)
AND E.COMPANY = H.COMPANY
AND E.PAYGROUP = H.PAYGROUP
AND H.PAY_END_DT = (SELECT MIN(G_ED.PAY_END_DT)
FROM PS_PAY_CALENDAR G_ED
WHERE G_ED.COMPANY = H.COMPANY
AND G_ED.PAYGROUP = H.PAYGROUP
AND G_ED.PAY_END_DT > (SELECT MAX(G_EDX.PAY_END_DT) FROM PS_PAY_CALENDAR G_EDX
WHERE G_EDX.COMPANY = G_ED.COMPANY
AND G_EDX.PAYGROUP = G_ED.PAYGROUP
AND G_EDX.PAY_CONFIRM_RUN = 'Y'))
ORDER BY E.LOCATION, D.CUST_ID, E.DEPTID, E.PAYGROUP, A.NAME
May 17, 2004 at 2:46 pm
1) Make sure all the referenced tables are indexed on columns used in joins or in the where clause of the sub-queries. Updating statistics won't help if appropriate indexes don't exist.
2) Make the following changes to the join criteria.
AND C.EMPLID = D.EMPLID -> AND A.EMPLID = D.EMPLID
AND EX.EMPLID = E.EMPLID -> AND EX.EMPLID = A.EMPLID
3) Use Query Analyzer to view the query execution plan. Are the indexes the plan is showing the best indexes for the query?
4) Turn on IO and TIME statistics when executing the query. Review the statistics to determine which table(s) are causing the performance hit.
SET STATISTICS IO ON
SET STATISTICS TIME ON
5) Does the "physical reads" IO statistic vary from execution to execution? Usually the "physical reads" IO statistic will be high the first time a query is run and be lower the next run because the data pages are cached. If there is a delay between the executions of the procedure, SQL Server may have loaded other data pages into cache resulting in an increase of "physical reads" for the execution or the SP.
6) With STATISTICS on you will be able to determine if the procedure is being recompiled each time it is run. In my experience, parse and compilation time is negligible when compared to execution times, especially in queries that return a large data set.
7) You can also review statistics to determine if worktables are being created by the SP. If large worktables are created, the stored procedure may benefit from reengineering. Often, performance can be improved by breaking the query into two or more pieces and using temp tables or table variables to store intermediate results. That is what SQL does with worktables. Query performance can be improved because you control the data that is put into temp tables.
8) You’ve not mentioned how much memory the SQL Server has. Too little memory can cause performance problems because SQL Server is constantly moving data-pages in and out of cache.
TL Broadbent
May 17, 2004 at 4:06 pm
First off I notice you have a lot of subqueries and that you are using the older join methodology. I am still working on the subqueries but try this with regards to the joins to see if it helps any or as at least the same. I think the subqueries have the biggest answer in them.
SELECT DISTINCT
E.LOCATION
,D.CUST_ID
,RTRIM(E.LOCATION) + RTRIM(E.DEPTID) + RTRIM(D.CUST_ID) + RTRIM(E.PAYGROUP)
,E.DEPTID
,E.EFFDT
,B.NATIONAL_ID
,RTRIM(B.NATIONAL_ID_TYPE)
,A.EMPLID
,C.EMPL_RCD
,C.TASKGROUP
,E.BUSINESS_UNIT
,E.SETID_LOCATION
,E.SETID_DEPT
,E.SETID_JOBCODE
,E.JOBCODE
,D.BUSINESS_UNIT_PC
,D.PROJECT_ID
,D.SETID_CUST
,D.ACTIVITY_ID
,A.NAME
,D.FO_CUST_NAME
,F.TRC
,F.FO_BILL_RATE
,F.FO_PAY_RATE
,E.COMPANY
,E.PAYGROUP
,H.PAY_END_DT
,H.PAY_BEGIN_DT
,CONVERT(CHAR(10),EX.EFFDT,112)
,EX.EFFDT
,CONVERT(CHAR(10),H.PAY_END_DT,20)
,CONVERT(CHAR(10),H.PAY_BEGIN_DT,112)
FROM
PS_PERSON_NAME A
INNER JOIN
PS_PERS_NID B
ON
A.EMPLID = B.EMPLID AND
B.PRIMARY_NID = 'Y'
INNER JOIN
PS_TL_EMPL_DATA C
ON
C.EMPLID = A.EMPLID
INNER JOIN
PS_FO_EMPX_CUST_VW D
INNER JOIN
PS_JOB E
INNER JOIN
PS_JOB EX
ON
EX.EMPLID = E.EMPLID
AND EX.EMPL_RCD = E.EMPL_RCD
INNER JOIN
PS_PAY_CALENDAR H
ON
E.COMPANY = H.COMPANY
AND E.PAYGROUP = H.PAYGROUP
ON
D.EMPLID = E.EMPLID
AND D.EMPL_RCD = E.EMPL_RCD
ON
C.EMPLID = D.EMPLID
AND C.EMPL_RCD = D.EMPL_RCD
INNER JOIN
PS_FO_PRJ_RATES F
ON
F.TRC = (CASE B.NATIONAL_ID_TYPE WHEN 'CON' THEN '901' ELSE '001' END)
AND F.BUSINESS_UNIT_PC = D.BUSINESS_UNIT_PC
AND F.PROJECT_ID = D.PROJECT_ID
AND F.ACTIVITY_ID = D.ACTIVITY_ID
WHERE
C.EFFDT = (SELECT MAX(C_ED.EFFDT)
FROM PS_TL_EMPL_DATA C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.EMPL_RCD = C_ED.EMPL_RCD
AND C_ED.EFFDT <= H.PAY_END_DT)
AND(
( E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_JOB E_ED
WHERE E.EMPLID = E_ED.EMPLID
AND E.EMPL_RCD = E_ED.EMPL_RCD
AND E_ED.EFFDT <= H.PAY_END_DT)
AND E.EFFSEQ = (SELECT MAX(E_ES.EFFSEQ) FROM PS_JOB E_ES
WHERE E.EMPLID = E_ES.EMPLID
AND E.EMPL_RCD = E_ES.EMPL_RCD
AND E.EFFDT = E_ES.EFFDT)
AND E.EMPL_STATUS NOT IN ('T', 'R'))
OR
( E.EFFDT = (SELECT MAX(E_EDX.EFFDT) FROM PS_JOB E_EDX
WHERE E.EMPLID = E_EDX.EMPLID
AND E.EMPL_RCD = E_EDX.EMPL_RCD
AND E_EDX.EFFDT BETWEEN H.PAY_BEGIN_DT AND H.PAY_END_DT
AND E_EDX.ACTION IN ('TER', 'RET'))
AND E.EFFSEQ = (SELECT MAX(E_ESX.EFFSEQ) FROM PS_JOB E_ESX
WHERE E.EMPLID = E_ESX.EMPLID
AND E.EMPL_RCD = E_ESX.EMPL_RCD
AND E.EFFDT = E_ESX.EFFDT
AND E_ESX.ACTION IN('TER', 'RET')) )
)
AND EX.EFFDT = (SELECT MAX(EX1.EFFDT)
FROM PS_JOB EX1
WHERE EX1.EMPLID = EX.EMPLID
AND EX1.EMPL_RCD = EX.EMPL_RCD
AND EX1.EFFDT <= H.PAY_END_DT
AND EX1.ACTION in ('ADL', 'HIR'))
AND EX.EFFSEQ = (SELECT MAX(EX2.EFFSEQ)
FROM PS_JOB EX2
WHERE EX2.EMPLID = EX.EMPLID
AND EX2.EMPL_RCD = EX.EMPL_RCD
AND EX2.EFFDT = EX.EFFDT
AND EX2.ACTION in ('ADL', 'HIR'))
AND F.EFFDT = (SELECT MAX(F_ED.EFFDT) FROM PS_FO_PRJ_RATES F_ED
WHERE F_ED.BUSINESS_UNIT_PC = F.BUSINESS_UNIT_PC
AND F_ED.PROJECT_ID = F.PROJECT_ID
AND F_ED.ACTIVITY_ID = F.ACTIVITY_ID
AND F_ED.EFFDT <= H.PAY_END_DT)
AND H.PAY_END_DT = (SELECT MIN(G_ED.PAY_END_DT)
FROM PS_PAY_CALENDAR G_ED
WHERE G_ED.COMPANY = H.COMPANY
AND G_ED.PAYGROUP = H.PAYGROUP
AND G_ED.PAY_END_DT > (SELECT MAX(G_EDX.PAY_END_DT) FROM PS_PAY_CALENDAR G_EDX
WHERE G_EDX.COMPANY = G_ED.COMPANY
AND G_EDX.PAYGROUP = G_ED.PAYGROUP
AND G_EDX.PAY_CONFIRM_RUN = 'Y'))
ORDER BY E.LOCATION, D.CUST_ID, E.DEPTID, E.PAYGROUP, A.NAME
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply