SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query response taking lot of time


Query response taking lot of time

Author
Message
Smash125
Smash125
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 1381
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
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27221 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1766 Visits: 3432
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
OTF
OTF
Mr or Mrs. 500
Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)

Group: General Forum Members
Points: 500 Visits: 4128
See link below as to how best to post performance problems:

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25954 Visits: 17519
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 Modens 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)
Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1382 Visits: 1398
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search