Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query response taking lot of time Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 5:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:19 AM
Points: 200, Visits: 1,138

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
Post #1505145
Posted Wednesday, October 16, 2013 5:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:15 PM
Points: 13,731, Visits: 10,692
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505147
Posted Wednesday, October 16, 2013 6:22 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:52 AM
Points: 906, Visits: 2,856
Further to the request for DDL and an actual execution plan, can you also supply view definitions.

VW_FACT_NLTRANS
VW_DIM_EXPENSECODE
VW_DIM_SUPPLIER
VW_FACT_CURRENCY




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1505154
Posted Wednesday, October 16, 2013 7:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128

See link below as to how best to post performance problems:

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1505176
Posted Wednesday, October 16, 2013 7:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:32 PM
Points: 13,455, Visits: 12,318
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/


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1505185
Posted Wednesday, October 16, 2013 8:14 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 9:40 AM
Points: 985, Visits: 1,326
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, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1505227
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse