December 29, 2004 at 3:58 pm
Hello,
We have an application (Report Smith) that is having performance issues.
The code below when ran via the app (Using users SQL Login) takes approx 45 + (Used to take 5-7) minutes to return the data. Each user has views that they are the owners of that reference the base tables the report(s) are querying.
The same code ran within Query Analyzer (As SA) comes back in 7 seconds.
Profiler shows two entirely different Execution Plans for the Queries (The apps is roughly three times as long) even though the code in the the SQLStatement is identical???
Any ideas why??? Can the app not use the indexes that Query Analyzer can? Are indexed views necessary?
SELECT
SUBSTRING(PS_JOB.DEPTID, 1, 3) , SUBSTRING(PS_JOB.DEPTID, 4, 3) , SUBSTRING(PS_JOB.DEPTID, 7, 3) , CASE WHEN PS_CC_ELITE_TITLE.DESCR = 'Executive (N/S)' THEN 'Executive'
WHEN PS_CC_ELITE_TITLE.DESCR <> 'Executive (N/S)' THEN
PS_CC_ELITE_TITLE.DESCR
END,
PS_PERSONAL_DATA.EMPLID, PS_PERSONAL_DATA.NAME, PS_PERSONAL_DATA.ORIG_HIRE_DT, PS_JOB.DEPTID, PS_JOB.EMPL_STATUS, PS_JOB.LOCATION, PS_EMPLOYMENT.TERMINATION_DT, PS_CC_JOB_1.ELITE_TITLE, PS_CC_JOB_1.COP_LEVEL, PS_CC_JOB_1.COP_PATH, PS_CC_JOB_1.COP_TITLE, PS_CC_JOB_1.ELECTED_TITLE, PS_DEPT_TBL.DESCR, PS_CC_EMPLOYMENT_1.INDEPENDENCE_TEST, PS_CC_ELITE_TITLE.DESCR, PS_CC_COP_PATH.DESCR, PS_CC_COP_TITLE.DESCR, PS_CC_ELECTED_TBL.DESCR, PS_CC_SECPS_DESIG.DESCR
FROM
PS_PERSONAL_DATA, PS_JOB, PS_EMPLOYMENT, PS_CC_JOB_1, PS_DEPT_TBL, PS_CC_EMPLOYMENT_1, PS_CC_ELITE_TITLE, PS_CC_COP_PATH, PS_CC_COP_TITLE, PS_CC_ELECTED_TBL, PS_CC_SECPS_DESIG
WHERE
((((PS_JOB.EFFSEQ= (
SELECT MAX(INNERALIAS.EFFSEQ)
FROM PS_JOB INNERALIAS
WHERE INNERALIAS.EMPLID = PS_JOB.EMPLID
AND INNERALIAS.EMPL_RCD_NBR = PS_JOB.EMPL_RCD_NBR
AND INNERALIAS.EFFDT = PS_JOB.EFFDT)
AND
PS_JOB.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_JOB INNERALIAS
WHERE INNERALIAS.EMPLID = PS_JOB.EMPLID
AND INNERALIAS.EMPL_RCD_NBR = PS_JOB.EMPL_RCD_NBR
AND INNERALIAS.EFFDT <= GetDate()))) AND
((PS_CC_JOB_1.EFFSEQ= (
SELECT MAX(INNERALIAS.EFFSEQ)
FROM PS_CC_JOB_1 INNERALIAS
WHERE INNERALIAS.EMPLID = PS_CC_JOB_1.EMPLID
AND INNERALIAS.EMPL_RCD_NBR = PS_CC_JOB_1.EMPL_RCD_NBR
AND INNERALIAS.EFFDT = PS_CC_JOB_1.EFFDT)
AND
PS_CC_JOB_1.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_CC_JOB_1 INNERALIAS
WHERE INNERALIAS.EMPLID = PS_CC_JOB_1.EMPLID
AND INNERALIAS.EMPL_RCD_NBR = PS_CC_JOB_1.EMPL_RCD_NBR
AND INNERALIAS.EFFDT <= GetDate()))) AND
((PS_DEPT_TBL.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_DEPT_TBL INNERALIAS
WHERE INNERALIAS.DEPTID = PS_DEPT_TBL.DEPTID
AND INNERALIAS.EFFDT <= GetDate()))) AND
((PS_CC_ELITE_TITLE.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_CC_ELITE_TITLE INNERALIAS
WHERE INNERALIAS.ELITE_TITLE = PS_CC_ELITE_TITLE.ELITE_TITLE
AND INNERALIAS.EFFDT <= GetDate()))) AND
((PS_CC_COP_PATH.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_CC_COP_PATH INNERALIAS
WHERE INNERALIAS.COP_PATH = PS_CC_COP_PATH.COP_PATH
AND INNERALIAS.EFFDT <= GetDate()))) AND
((PS_CC_COP_TITLE.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_CC_COP_TITLE INNERALIAS
WHERE INNERALIAS.COP_TITLE = PS_CC_COP_TITLE.COP_TITLE
AND INNERALIAS.EFFDT <= GetDate()))) AND
((PS_CC_ELECTED_TBL.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_CC_ELECTED_TBL INNERALIAS
WHERE INNERALIAS.ELECTED_TITLE = PS_CC_ELECTED_TBL.ELECTED_TITLE
AND INNERALIAS.EFFDT <= GetDate()))) AND
((PS_CC_SECPS_DESIG.EFFDT = (
SELECT MAX(INNERALIAS.EFFDT)
FROM PS_CC_SECPS_DESIG INNERALIAS
WHERE INNERALIAS.SECPS_DESIGNATION = PS_CC_SECPS_DESIG.SECPS_DESIGNATION
AND INNERALIAS.EFFDT <= GetDate()))) AND
(PS_JOB.EMPL_STATUS IN( 'A', 'L', 'P')) AND
(PS_JOB.COMPANY IN( 'CC', 'CG')) AND
(PS_JOB.PAYGROUP <> 'NPY') AND
(PS_CC_JOB_1.ELITE_TITLE NOT IN ( '004', '005', '007', '013', '027', '036', '037'))))
AND
(PS_PERSONAL_DATA.EMPLID = PS_JOB.EMPLID) AND (PS_PERSONAL_DATA.EMPLID = PS_EMPLOYMENT.EMPLID) AND (PS_PERSONAL_DATA.EMPLID = PS_CC_JOB_1.EMPLID) AND (PS_JOB.DEPTID = PS_DEPT_TBL.DEPTID) AND (PS_PERSONAL_DATA.EMPLID = PS_CC_EMPLOYMENT_1.EMPLID) AND (PS_CC_EMPLOYMENT_1.SECPS_DESIGNATION = PS_CC_SECPS_DESIG.SECPS_DESIGNATION) AND (PS_CC_JOB_1.ELITE_TITLE = PS_CC_ELITE_TITLE.ELITE_TITLE) AND (PS_CC_JOB_1.COP_PATH = PS_CC_COP_PATH.COP_PATH) AND (PS_CC_JOB_1.COP_TITLE = PS_CC_COP_TITLE.COP_TITLE) AND (PS_CC_JOB_1.ELECTED_TITLE = PS_CC_ELECTED_TBL.ELECTED_TITLE)
ORDER BY
PS_PERSONAL_DATA.NAME
December 30, 2004 at 7:38 am
Check in the SQL server error log for memory errors similar to "not enough contiguous memory". I can't remember the exact message now.
Once you get this message the query runs veeeeeery slowly and everyone else on the system has response problems also. This usually happens after SQL server has been running for a while and the memory is fragmented, so it doesn't usually show up when the query is run in Query Analyser.
The problem is due to some 'merge' operations in the query needing contiguous memory. I had the problem after adding just a few more left joins to an already big query. What happened for me was that the execution plan in query analyser showed some new 'Hash Merge's and 'Right Merge's and these seem to need a lot of contiguous memory.
The solution was to rewrite the query until the Hash and Right Merges disappeared. Try breaking the query into two smaller queries in the application.
Hope this helps
Peter Tillotson
Time flies like an arrow. Fruit flies like a banana. (Groucho Marx)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply