Query response taking lot of time
-
Following query taking lots of time to execute. I am executing this in SSRS. I tried my level best to optimize it. Can this be optimized more
if @PeriodSelected is null and @ExpenseType='Non Chargeable'
begin
SELECT EC.EXPENSE_DESC,SUP.SUPPLIER_DESC,
SUM((NL.BASEVAL)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END) TOTALCLAIM
FROM
VW_FACT_NLTRANS NL
INNER JOIN
VW_DIM_EXPENSECODE EC
ON NL.EXPENSE_SK=EC.EXPENSE_SK AND NL.COMPANY_SK=EC.COMPANY_SK
INNER JOIN VW_DIM_SUPPLIER SUP ON
NL.SOURCEACC=SUP.SUPPLIERID AND NL.COMPANY_SK=SUP.COMPANY_SK
LEFT OUTER JOIN VW_FACT_CURRENCY CURR
ON NL.COMPANY_SK=CURR.COMPANY_SK AND CURR.CURRENCY IN ('STER','GBP')
WHERE NL.PERIODID<=@PeriodEnd AND NL.PERIODID>=@PeriodStart
AND (EC.EXPENSEGROUP NOT IN ('2-###-##') AND EC.EXPENSECODE NOT IN ('8-010-02','8-010-01'))
AND NL.COMPANY_SK IN (@Company)
AND NL.[DOC-ID] IN ('EXIN','EXCN') AND SUP.LEDGER='EX'
GROUP BY EC.EXPENSE_DESC,SUP.SUPPLIER_DESC
ORDER BY SUM((NL.BASEVAL)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END) DESC
end
if @PeriodSelected is not null and @ExpenseType='Non Chargeable'
begin
SELECT EC.EXPENSE_DESC,SUP.SUPPLIER_DESC,
SUM((NL.BASEVAL)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END) TOTALCLAIM
FROM
VW_FACT_NLTRANS NL
INNER JOIN
VW_DIM_EXPENSECODE EC
ON NL.EXPENSE_SK=EC.EXPENSE_SK AND NL.COMPANY_SK=EC.COMPANY_SK
INNER JOIN VW_DIM_SUPPLIER SUP ON
NL.SOURCEACC=SUP.SUPPLIERID AND NL.COMPANY_SK=SUP.COMPANY_SK
LEFT OUTER JOIN VW_FACT_CURRENCY CURR
ON NL.COMPANY_SK=CURR.COMPANY_SK AND CURR.CURRENCY IN ('STER','GBP')
WHERE NL.PERIODID=@PeriodSelected AND (EC.EXPENSEGROUP NOT IN ('2-###-##') AND EC.EXPENSECODE NOT IN ('8-010-02','8-010-01'))
AND NL.COMPANY_SK IN (@Company)
AND NL.[DOC-ID] IN ('EXIN','EXCN') AND SUP.LEDGER='EX'
GROUP BY EC.EXPENSE_DESC,SUP.SUPPLIER_DESC
ORDER BY SUM((NL.BASEVAL)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END) DESC
end
if @PeriodSelected is null and @ExpenseType='Chargeable'
begin
SELECT EC.EXPENSE_DESC,SUP.SUPPLIER_DESC,
SUM((NL.BASEVAL)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END) TOTALCLAIM
FROM
VW_FACT_NLTRANS NL
INNER JOIN
VW_DIM_EXPENSECODE EC
ON NL.EXPENSE_SK=EC.EXPENSE_SK AND NL.COMPANY_SK=EC.COMPANY_SK
INNER JOIN VW_DIM_SUPPLIER SUP ON
NL.SOURCEACC=SUP.SUPPLIERID AND NL.COMPANY_SK=SUP.COMPANY_SK
LEFT OUTER JOIN VW_FACT_CURRENCY CURR
ON NL.COMPANY_SK=CURR.COMPANY_SK AND CURR.CURRENCY IN ('STER','GBP')
WHERE NL.PERIODID<=@PeriodEnd AND NL.PERIODID>=@PeriodStart
AND (EC.EXPENSEGROUP IN ('2-###-##'))
AND NL.COMPANY_SK IN (@Company)
AND NL.[DOC-ID] IN ('EXIN','EXCN') AND SUP.LEDGER='EX'
GROUP BY EC.EXPENSE_DESC,SUP.SUPPLIER_DESC
ORDER BY SUM((NL.BASEVAL)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END) DESC
end
if @PeriodSelected is not null and @ExpenseType='Chargeable'
begin
SELECT EC.EXPENSE_DESC,SUP.SUPPLIER_DESC,
SUM((NL.BASEVAL)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END) TOTALCLAIM
FROM
VW_FACT_NLTRANS NL
INNER JOIN
VW_DIM_EXPENSECODE EC
ON NL.EXPENSE_SK=EC.EXPENSE_SK AND NL.COMPANY_SK=EC.COMPANY_SK
INNER JOIN VW_DIM_SUPPLIER SUP ON
NL.SOURCEACC=SUP.SUPPLIERID AND NL.COMPANY_SK=SUP.COMPANY_SK
LEFT OUTER JOIN VW_FACT_CURRENCY CURR
ON NL.COMPANY_SK=CURR.COMPANY_SK AND CURR.CURRENCY IN ('STER','GBP')
WHERE NL.PERIODID=@PeriodSelected AND (EC.EXPENSEGROUP IN ('2-###-##'))
AND NL.COMPANY_SK IN (@Company)
AND NL.[DOC-ID] IN ('EXIN','EXCN') AND SUP.LEDGER='EX'
GROUP BY EC.EXPENSE_DESC,SUP.SUPPLIER_DESC
ORDER BY SUM((NL.BASEVAL)*CASE WHEN CURR.RATE IS NULL THEN 1 ELSE CURR.RATE END) DESC
end
-
It might.
Hard to tell without knowing actual table DDLs and their indexes and the actual query plan.
About how many rows are we talking here?
How long is "lots of time"?
Also, with your queries unformatted like that, it is hard to spot the differences between each query.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
See link below as to how best to post performance problems:
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
-
Here is the query after some formatting.
IF @PeriodSelected IS NULL
AND @ExpenseType = 'Non Chargeable'
BEGIN
SELECT EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
,SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) TOTALCLAIM
FROM VW_FACT_NLTRANS NL
INNER JOIN VW_DIM_EXPENSECODE EC ON NL.EXPENSE_SK = EC.EXPENSE_SK
AND NL.COMPANY_SK = EC.COMPANY_SK
INNER JOIN VW_DIM_SUPPLIER SUP ON NL.SOURCEACC = SUP.SUPPLIERID
AND NL.COMPANY_SK = SUP.COMPANY_SK
LEFT JOIN VW_FACT_CURRENCY CURR ON NL.COMPANY_SK = CURR.COMPANY_SK
AND CURR.CURRENCY IN (
'STER'
,'GBP'
)
WHERE NL.PERIODID <= @PeriodEnd
AND NL.PERIODID >= @PeriodStart
AND (
EC.EXPENSEGROUP NOT IN ('2-###-##')
AND EC.EXPENSECODE NOT IN (
'8-010-02'
,'8-010-01'
)
)
AND NL.COMPANY_SK IN (@Company)
AND NL.[DOC-ID] IN (
'EXIN'
,'EXCN'
)
AND SUP.LEDGER = 'EX'
GROUP BY EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
ORDER BY SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) DESC
END
IF @PeriodSelected IS NOT NULL
AND @ExpenseType = 'Non Chargeable'
BEGIN
SELECT EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
,SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) TOTALCLAIM
FROM VW_FACT_NLTRANS NL
INNER JOIN VW_DIM_EXPENSECODE EC ON NL.EXPENSE_SK = EC.EXPENSE_SK
AND NL.COMPANY_SK = EC.COMPANY_SK
INNER JOIN VW_DIM_SUPPLIER SUP ON NL.SOURCEACC = SUP.SUPPLIERID
AND NL.COMPANY_SK = SUP.COMPANY_SK
LEFT JOIN VW_FACT_CURRENCY CURR ON NL.COMPANY_SK = CURR.COMPANY_SK
AND CURR.CURRENCY IN (
'STER'
,'GBP'
)
WHERE NL.PERIODID = @PeriodSelected
AND (
EC.EXPENSEGROUP NOT IN ('2-###-##')
AND EC.EXPENSECODE NOT IN (
'8-010-02'
,'8-010-01'
)
)
AND NL.COMPANY_SK IN (@Company)
AND NL.[DOC-ID] IN (
'EXIN'
,'EXCN'
)
AND SUP.LEDGER = 'EX'
GROUP BY EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
ORDER BY SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) DESC
END
IF @PeriodSelected IS NULL
AND @ExpenseType = 'Chargeable'
BEGIN
SELECT EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
,SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) TOTALCLAIM
FROM VW_FACT_NLTRANS NL
INNER JOIN VW_DIM_EXPENSECODE EC ON NL.EXPENSE_SK = EC.EXPENSE_SK
AND NL.COMPANY_SK = EC.COMPANY_SK
INNER JOIN VW_DIM_SUPPLIER SUP ON NL.SOURCEACC = SUP.SUPPLIERID
AND NL.COMPANY_SK = SUP.COMPANY_SK
LEFT JOIN VW_FACT_CURRENCY CURR ON NL.COMPANY_SK = CURR.COMPANY_SK
AND CURR.CURRENCY IN (
'STER'
,'GBP'
)
WHERE NL.PERIODID <= @PeriodEnd
AND NL.PERIODID >= @PeriodStart
AND (EC.EXPENSEGROUP IN ('2-###-##'))
AND NL.COMPANY_SK IN (@Company)
AND NL.[DOC-ID] IN (
'EXIN'
,'EXCN'
)
AND SUP.LEDGER = 'EX'
GROUP BY EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
ORDER BY SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) DESC
END
IF @PeriodSelected IS NOT NULL
AND @ExpenseType = 'Chargeable'
BEGIN
SELECT EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
,SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) TOTALCLAIM
FROM VW_FACT_NLTRANS NL
INNER JOIN VW_DIM_EXPENSECODE EC ON NL.EXPENSE_SK = EC.EXPENSE_SK
AND NL.COMPANY_SK = EC.COMPANY_SK
INNER JOIN VW_DIM_SUPPLIER SUP ON NL.SOURCEACC = SUP.SUPPLIERID
AND NL.COMPANY_SK = SUP.COMPANY_SK
LEFT JOIN VW_FACT_CURRENCY CURR ON NL.COMPANY_SK = CURR.COMPANY_SK
AND CURR.CURRENCY IN (
'STER'
,'GBP'
)
WHERE NL.PERIODID = @PeriodSelected
AND (EC.EXPENSEGROUP IN ('2-###-##'))
AND NL.COMPANY_SK IN (@Company)
AND NL.[DOC-ID] IN (
'EXIN'
,'EXCN'
)
AND SUP.LEDGER = 'EX'
GROUP BY EC.EXPENSE_DESC
,SUP.SUPPLIER_DESC
ORDER BY SUM((NL.BASEVAL) * CASE
WHEN CURR.RATE IS NULL
THEN 1
ELSE CURR.RATE
END) DESC
END
What you have here is a stored proc with multiple execution paths. Read this article from Gail that explains this situation and a great way to deal with the sometime performance issues you are experiencing.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]
_______________________________________________________________
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/ -
Sean;
You are ahead of me with your post. I just got finished formatting the code as well. At first glance, not only the multiple execution paths, I would almost guarantee the views are not indexed.
I'm not a big fan of calculations & case logic in Order clauses...
Can we get an idea what efforts were done to tune this script?
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply