June 27, 2013 at 12:02 pm
I am looking at this query, which is very old, and uses ISNULL all over, when this query runs from QA it runs in 4 seconds, when it runs s a SP it runs in 1 1/2 minutes, i am wondering if the ISNULLS may have something to do with it?
DECLARE @CRITERIA VARCHAR(20)
DECLARE @CRITERIA_VALUE VARCHAR(50)
SET @CRITERIA_VALUE = 'IN006'
DECLARE @WEEK_ENDING DATETIME
SET @WEEK_ENDING = '6/22/2013'
DECLARE @VIEW_TYPE VARCHAR(50)
DECLARE @CRITERIA_VALUE1 VARCHAR(50)
SET @CRITERIA_VALUE1 = '0'
DECLARE @CRITERIA_VALUE2 VARCHAR(50)
SET @CRITERIA_VALUE2 = '0'
SELECT CASE WHEN C.Week_Ending IS NOT NULL THEN C.Week_Ending ELSE
CASE WHEN PMS.Week_Ending IS NOT NULL THEN PMS.Week_Ending ELSE PMR.Week_Ending END END AS Week_Ending,
ISNULL(Sum(Contract_Week_Sales),0) AS Contract_Week_Sales, ISNULL(Sum(Week_Spread),0) AS Week_Spread,
ISNULL(Sum(PMS.PERMS_Total_Sales),0)+ISNULL(Sum(PMR.PERMS_Total_Sales),0) AS Perms_Week_Sales, ISNULL(Sum(Pass_Week_Sales),0) AS Pass_Week_Sales,
ISNULL(Sum(Contract_Week_Sales),0)+ISNULL(Sum(PMS.PERMS_Total_Sales),0)+ISNULL(Sum(PMR.PERMS_Total_Sales),0) AS Total_Sales,
ISNULL(SUM(TOTAL_COST),0) AS TOTAL_COST,
ISNULL(Sum(Contractor_Week_Total_Cost),0) AS Contractor_Week_Total_Cost,
ISNULL(Sum(C1099_TOTAL_COST),0) AS C1099_TOTAL_COST,
ISNULL(Sum(Bench_Week_Total_Cost),0) AS Bench_Week_Total_Cost,
ISNULL(MAX(MarkUp),0) AS MarkUp,
ISNULL(MAX(Margin),0) AS Margin, ISNULL(Sum(TOTALADJSPREAD),0) AS TOTALADJSPREAD
FROM
(
SELECT CASE WHEN C.Week_Ending IS NOT NULL THEN C.Week_Ending ELSE PS.Week_Ending END AS Week_Ending,
ISNULL(Sum(CONTRACT_Total_Sales),0) AS Contract_Week_Sales, ISNULL(Sum(CONTRACT_Total_Spread),0)+ISNULL(Sum(PASS_Total_Spread),0) AS Week_Spread,
ISNULL(Sum(PASS_Total_Sales),0) AS Pass_Week_Sales,
ISNULL(Sum(CONTRACT_Total_Sales),0)+ISNULL(Sum(PASS_Total_Sales),0) AS Total_Sales,
ISNULL(SUM(TOTAL_COST),0) AS TOTAL_COST,
ISNULL(Sum(CASE WHEN LTRIM(RTRIM(C.EMPLCLAS)) IN ('CONTRACTOR','') THEN ISNULL(((TOTAL_COST)),0) ELSE 0 END*ISNULL(C.LOADSMALL,PS.LOADSMALL)),0) AS Contractor_Week_Total_Cost,
ISNULL(Sum(CASE WHEN LTRIM(RTRIM(C.EMPLCLAS)) = '1099' THEN ISNULL(((TOTAL_COST)),0) ELSE 0 END*ISNULL(C.LOADSMALL,PS.LOADSMALL)),0) AS C1099_TOTAL_COST,
ISNULL(Sum(CASE WHEN LTRIM(RTRIM(C.EMPLCLAS)) = 'BENCH' THEN ISNULL(((TOTAL_COST)),0) ELSE 0 END*ISNULL(C.LOADSMALL,PS.LOADSMALL)),0) AS Bench_Week_Total_Cost,
CASE WHEN ISNULL(SUM(TOTAL_COST),0) <> 0 THEN ((ISNULL(SUM((TOTALSPREAD)),0))/(ISNULL(SUM((TOTAL_COST)),0)))*100 ELSE 0 END AS MarkUp,
CASE WHEN SUM(PRODUCTION) <> 0 THEN (ISNULL(SUM((PRODUCTION)),0) - ISNULL(SUM((TOTAL_COST)),0) -
(SUM(CASE WHEN LTRIM(RTRIM(C.EMPLCLAS)) IN ('CONTRACTOR','BENCH','') THEN ISNULL(((TOTAL_COST)),0)*ISNULL(C.LOADSMALL,PS.LOADSMALL) ELSE ISNULL(((TOTAL_COST)),0)*ISNULL(C.LOADSMALL,PS.LOADSMALL) END)))/
ISNULL(SUM((PRODUCTION)),0)*100 ELSE 0 END AS Margin, ISNULL(Sum(TOTALADJSPREAD),0)+ISNULL(Sum(PASS_Total_AdjSpread),0) AS TOTALADJSPREAD
FROM
(
SELECT Week_Ending, EMPLCLAS, LOADSMALL,
ISNULL(Sum(PRODUCTION),0) AS PRODUCTION, ISNULL(Sum(TOTALSPREAD),0) AS TOTALSPREAD, ISNULL(Sum(TOTAL_COST),0) AS TOTAL_COST,
ISNULL(Sum(PRODUCTION),0) AS CONTRACT_Total_Sales, ISNULL(Sum(TOTALSPREAD),0) AS CONTRACT_Total_Spread, ISNULL(Sum(TOTALADJSPREAD),0) AS TOTALADJSPREAD
FROM (
SELECT OFFICE, EMPLCLAS, Week_Ending, PHASE, QUARTER, LOADSMALL,
ISNULL(SUM((DISCOUNT_BILL*(PROD_PERC/100))/Devider),0) AS PRODUCTION,
ISNULL(SUM((TOTAL_COST*(PROD_PERC/100))/Devider),0) AS TOTAL_COST,
ISNULL(SUM((DISCOUNT_BILL*(PROD_PERC/100))/Devider),0) - ISNULL(SUM((TOTAL_COST*(PROD_PERC/100))/Devider),0) AS TOTALSPREAD,
ISNULL(SUM(CASE WHEN EMPLCLAS = '1099' THEN (DISCOUNT_BILL-(TOTAL_COST/100)*LOADPER)*B.PROD_PERC/100 ELSE (DISCOUNT_BILL-(TOTAL_COST/100)*LOADPER) * B.PROD_PERC/100 END/Devider),0) AS TOTALADJSPREAD
FROM (
SELECT PRICSHED, EMPLCLAS, Week_Ending, PHASE, QUARTER, SPLIT_ID,
TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadper',LoadFactor) AS LOADPER,
TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadsmall',LoadFactor) AS LOADSMALL,
CASE WHEN Week_Ending < '5/06/2006' THEN 1 ELSE 1 END AS Devider,
DISCOUNT_BILL, TOTAL_COST as TOTAL_COST, SPREAD
FROM TECH.dbo.JUDGE_HOURSALLBILLRATES
WHERE Week_Ending BETWEEN DATEADD(WEEK,-51,@WEEK_ENDING) AND @WEEK_ENDING
AND (ISNULL(Hours,0) <> 0 Or ISNULL(Adjust,0) <> 0 Or ISNULL(NonBillable,0) <> 0)
AND PTWEEKENDING IS NULL
UNION
SELECT PRICSHED, EMPLCLAS, Week_Ending, PHASE, QUARTER, SPLIT_ID,
TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadper',LoadFactor) AS LOADPER,
TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadsmall',LoadFactor) AS LOADSMALL,
CASE WHEN Week_Ending < '5/06/2006' THEN 1 ELSE 1 END AS Devider,
DISCOUNT_BILL*CAN2US_RATE AS DISCOUNT_BILL, TOTAL_COST*CAN2US_RATE as TOTAL_COST, SPREAD*CAN2US_RATE AS SPREAD
FROM TECH.dbo.JUDGE_HOURSALLBILLRATES_CAN
WHERE Week_Ending BETWEEN DATEADD(WEEK,-51,@WEEK_ENDING) AND @WEEK_ENDING
AND (ISNULL(Hours,0) <> 0 Or ISNULL(Adjust,0) <> 0 Or ISNULL(NonBillable,0) <> 0)
AND PTWEEKENDING IS NULL
) A
INNER JOIN
(
SELECT A.ITEMNMBR, A.SPLIT_ID, B.EMPLOYID, B.PROD_PERC, B.COMM_PERC, OFFICE, LOC_COSTCENTER, START_DATE, END_DATE
FROM (SELECT DISTINCT ITEMNMBR, SPLIT_ID, START_DATE, ISNULL(END_DATE,'12/31/2999') AS END_DATE FROM TAP.dbo.CIR_MASTER_SPLIT_TABLE
) A
INNER JOIN
(SELECT EMPLOYID, SPLIT_ID, PROD_PERC, COMM_PERC, LOC_SHORT_NAME AS OFFICE, LOC_ID AS LOC_COSTCENTER FROM TAP.dbo.CIR_DETAIL_SPLIT_TABLE D
INNER JOIN TAP.dbo.CIR_LOCATION_DEPARTMENT L ON D.LOC_ID = L.ID WHERE DB_UNIT_GP IN ('TECH','CATEC')
) B
ON A.SPLIT_ID = B.SPLIT_ID
) B
ON A.SPLIT_ID = B.SPLIT_ID
WHERE EMPLOYID = @CRITERIA_VALUE
GROUP BY OFFICE, EMPLCLAS, Week_Ending, PHASE, QUARTER, LOADSMALL
) CONTRACT
GROUP BY Week_Ending, EMPLCLAS, LOADSMALL
) C
FULL JOIN
(
SELECT Week_Ending, EMPLCLAS, LOADSMALL, ISNULL(Sum(PRODUCTION),0) AS PASS_Total_Sales, ISNULL(Sum(TOTALSPREAD),0) AS PASS_Total_Spread, ISNULL(Sum(TOTALADJSPREAD),0) AS PASS_Total_AdjSpread
FROM (
SELECT OFFICE, EMPLCLAS, Week_Ending, PHASE, QUARTER, LOADSMALL,
0 AS PRODUCTION,
ISNULL(SUM((DISCOUNT_BILL*(PROD_PERC/100))/Devider),0) - ISNULL(SUM((TOTAL_COST*(PROD_PERC/100))/Devider),0) AS TOTALSPREAD,
ISNULL(SUM(CASE WHEN EMPLCLAS = '1099' THEN (DISCOUNT_BILL-(TOTAL_COST/100)*LOADPER)*B.PROD_PERC/100 ELSE (DISCOUNT_BILL-(TOTAL_COST/100)*LOADPER) * B.PROD_PERC/100 END/Devider),0) AS TOTALADJSPREAD
FROM (
SELECT PRICSHED, EMPLCLAS, Week_Ending, PHASE, QUARTER, SPLIT_ID,
TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadper',LoadFactor) AS LOADPER,
TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadsmall',LoadFactor) AS LOADSMALL,
CASE WHEN Week_Ending < '5/06/2006' THEN 1 ELSE 1 END AS Devider,
DISCOUNT_BILL, TOTAL_COST as TOTAL_COST, SPREAD
FROM TECH.dbo.JUDGE_HOURSALLBILLRATES
WHERE Week_Ending BETWEEN DATEADD(WEEK,-51,@WEEK_ENDING) AND @WEEK_ENDING
AND (ISNULL(Hours,0) <> 0 Or ISNULL(Adjust,0) <> 0 Or ISNULL(NonBillable,0) <> 0)
AND PTWEEKENDING IS NOT NULL
UNION
SELECT PRICSHED, EMPLCLAS, Week_Ending, PHASE, QUARTER, SPLIT_ID,
TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadper',LoadFactor) AS LOADPER,
TAP.dbo.PopulateLoadNew(Week_Ending,PAYRCORD,EMPLCLAS,'loadsmall',LoadFactor) AS LOADSMALL,
CASE WHEN Week_Ending < '5/06/2006' THEN 1 ELSE 1 END AS Devider,
DISCOUNT_BILL*CAN2US_RATE AS DISCOUNT_BILL, TOTAL_COST*CAN2US_RATE as TOTAL_COST, SPREAD*CAN2US_RATE AS SPREAD
FROM TECH.dbo.JUDGE_HOURSALLBILLRATES_CAN
WHERE Week_Ending BETWEEN DATEADD(WEEK,-51,@WEEK_ENDING) AND @WEEK_ENDING
AND (ISNULL(Hours,0) <> 0 Or ISNULL(Adjust,0) <> 0 Or ISNULL(NonBillable,0) <> 0)
AND PTWEEKENDING IS NOT NULL
) A
INNER JOIN
(
SELECT A.ITEMNMBR, A.SPLIT_ID, B.EMPLOYID, B.PROD_PERC, B.COMM_PERC, OFFICE, LOC_COSTCENTER, START_DATE, END_DATE
FROM (SELECT DISTINCT ITEMNMBR, SPLIT_ID, START_DATE, ISNULL(END_DATE,'12/31/2999') AS END_DATE FROM TAP.dbo.CIR_MASTER_SPLIT_TABLE
) A
INNER JOIN
(SELECT EMPLOYID, SPLIT_ID, PROD_PERC, COMM_PERC, LOC_SHORT_NAME AS OFFICE, LOC_ID AS LOC_COSTCENTER FROM TAP.dbo.CIR_DETAIL_SPLIT_TABLE D
INNER JOIN TAP.dbo.CIR_LOCATION_DEPARTMENT L ON D.LOC_ID = L.ID
) B
ON A.SPLIT_ID = B.SPLIT_ID
) B
ON A.SPLIT_ID = B.SPLIT_ID
WHERE EMPLOYID = @CRITERIA_VALUE
GROUP BY OFFICE, EMPLCLAS, Week_Ending, PHASE, QUARTER, LOADSMALL
) PASS
GROUP BY Week_Ending, EMPLCLAS, LOADSMALL
) PS ON C.Week_Ending = PS.Week_Ending AND C.EMPLCLAS = PS.EMPLCLAS
GROUP BY CASE WHEN C.Week_Ending IS NOT NULL THEN C.Week_Ending ELSE PS.Week_Ending END
) C
FULL JOIN
(
SELECT Week_Ending, ISNULL(Sum(DISCOUNT_BILL),0) AS PERMS_Total_Sales
FROM TECH.dbo.JUDGE_YTD_PERMS_SPLITS_PASS P
WHERE Splits_Perms = 'PERMS' AND Salesperson IS NOT NULL
AND Salesperson = @CRITERIA_VALUE
GROUP BY Week_Ending
HAVING Week_Ending BETWEEN DATEADD(WEEK,-51,@WEEK_ENDING) AND @WEEK_ENDING
) PMS ON C.Week_Ending = PMS.Week_Ending
FULL JOIN
(
SELECT Week_Ending, ISNULL(Sum(DISCOUNT_BILL),0) AS PERMS_Total_Sales
FROM TECH.dbo.JUDGE_YTD_PERMS_SPLITS_PASS P
WHERE Splits_Perms = 'PERMS' AND Recruiter IS NOT NULL
AND ISNULL(Recruiter,'') = @CRITERIA_VALUE AND ISNULL(Salesperson,'') <> @CRITERIA_VALUE
GROUP BY Week_Ending
HAVING Week_Ending BETWEEN DATEADD(WEEK,-51,@WEEK_ENDING) AND @WEEK_ENDING
) PMR ON C.Week_Ending = PMR.Week_Ending
GROUP BY CASE WHEN C.Week_Ending IS NOT NULL THEN C.Week_Ending ELSE
CASE WHEN PMS.Week_Ending IS NOT NULL THEN PMS.Week_Ending ELSE PMR.Week_Ending END END
--EXECUTE REPORTS.dbo.JUDGE_WEEKLY_SNAPSHOT_ALL 'emp','IN006','6/22/2013','Combined'
June 27, 2013 at 12:29 pm
I wouldn't suspect of the ISNULLs as the main problem. I would check on all the full joins to check if they're really necessary and maybe to change the subqueries into temp tables.
However, the previous advices are not guaranteed to help. To obtain help on performance you should check the following article:
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 27, 2013 at 12:53 pm
Many calls to the same scalar function (dbo.PopulateLoadNew). Is UNION neccesary, or can you use UNION ALL. There is a lot of low hanging fruit for performance in here.
AND (
ISNULL(Hours, 0) <> 0
OR ISNULL(Adjust, 0) <> 0
OR ISNULL(NonBillable, 0) <> 0
)
Should be equivalent to:
AND (
Hours <> 0
OR Adjust <> 0
OR NonBillable <> 0
)
There is no need to add ISNULL here because NULL <> 0.
In general this looks like it continually hits the same tables over and over again with slightly different criteria. Then it seems that each set of criteria is retrieved multiple times as various other subselects. Since you said this runs super fast in SSMS and when you run the stored proc it is very slow I suspect you have some parameter sniffing going on. That is the classic symptom. I would bet that you can also check some indexes and update stats which may also help.
Check out Gail's article about parameter sniffing. http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url] (Make sure you read all 3 installments)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2013 at 12:54 pm
Thanks, is there any relevance to the issue of it taking so long as a SP vs running it in QA?
June 27, 2013 at 1:01 pm
TryingToLearn (6/27/2013)
Thanks, is there any relevance to the issue of it taking so long as a SP vs running it in QA?
Did you read my response? I mention that is the classic symptom of parameter sniffing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2013 at 1:01 pm
What's the difference in the workload? How many lines are you using on each?
June 27, 2013 at 1:47 pm
Thanks folks,
i am looking into those things, one thing I noticed is that in the join, two likes table(different countries) have different indexes, could that effect performance? should the indexes be the same?
June 27, 2013 at 5:14 pm
Thanks folks,
i am looking into those things, one thing I noticed is that in the UNION, two like table(different countries) have different clustered indexes, could that effect performance? the indexes should be the same i would assume?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply