Click here to monitor SSC
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 (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 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
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

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

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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'
         Wink
   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'
            Wink
         Wink
      AND NL.COMPANY_SK IN (@Company)
      AND NL.[DOC-ID] IN (
         'EXIN'
         ,'EXCN'
         Wink
      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'
         Wink
   WHERE NL.PERIODID = @PeriodSelected
      AND (
         EC.EXPENSEGROUP NOT IN ('2-###-##')
         AND EC.EXPENSECODE NOT IN (
            '8-010-02'
            ,'8-010-01'
            Wink
         Wink
      AND NL.COMPANY_SK IN (@Company)
      AND NL.[DOC-ID] IN (
         'EXIN'
         ,'EXCN'
         Wink
      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'
         Wink
   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'
         Wink
      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'
         Wink
   WHERE NL.PERIODID = @PeriodSelected
      AND (EC.EXPENSEGROUP IN ('2-###-##'))
      AND NL.COMPANY_SK IN (@Company)
      AND NL.[DOC-ID] IN (
         'EXIN'
         ,'EXCN'
         Wink
      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)
Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1396
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