SQL query taking a long time depending on the option

Question

I'm working on this stored proc and the thing is that with one of the options it take 5 minutes to run, but with all of the others it takes only 5 seconds.

DECLARE @rptType varchar(75)
SET @rptType = 'Audits'

    SELECT CLT.[cltNum] AS [Client Number] ,CLT.[cltEng] AS [Engagement] ,CLT.[cltName] AS [Client Name] , CLT.[CSPLname] AS [Assurance Partner], CLT.[cmgLName], e.[DDEventDesc] AS [Project Type], Budget.[CBudProjectDesc] AS [Project Description], DUE.[CDTargetAmount] AS [Budget], MIN(WIP.[wdate]) [1st Date], CAST(SUM(WIP.[wRate]*WIP.[wHours]) AS decimal(34,2)) [Billable WIP], CAST( SUM(AR.[ARProgress])AS decimal(34,2)) [Progress], CAST((SUM(WIP.[wRate]*WIP.[wHours]) - SUM(AR.[ARProgress]))AS decimal(34,2)) [Net Wip],  CAST(sum(Bucket.[cinvar])AS decimal(34,2)) [AR Balence], MAX(inv.[InvDate]) AS [Last InvoiceDate], due.[CDDateDelivered] AS [Project OTD Date]From [sab].[dbo].[WIP] AS WIP
    Join [sab].[dbo].Clients AS CLT
      ON WIP.[wcltID] = CLt.[ID]
    Join [sab].[dbo].[cltdue] AS DUE
      ON DUE.[CDID] = WIP.[wDue]
    join [sab].[dbo].[DDEvents] AS E
      ON DUE.[cdEventID] = e.[ID]
    join [sab].[dbo].[Invoice] AS Inv
      ON WIP.[wInvNum] = INV.[invNumber]
    Join [sab].[dbo].[AcctsRec] AS AR
      ON INV.[invNumber] = AR.[ARApplyTo]
    Join [sab].[dbo].[ClientBuckets] AS Bucket
      ON CLT.ID = Bucket.ID
    JOIN [sab].[dbo].[cltBudget] AS Budget 
      ON clt.ID = cBudCltID 



    WHERE (@rptType = 'Audit - Payroll' AND e.[DDEventDesc] = 'Audit - Payroll' AND AR.[ARType] = 1 AND (CLT.[cmaster]=1 OR CLT.[cinvIndivEng] = 0) ) OR
          (@rptType = 'Audits' AND (e.[ID] =   '132' OR e.[ID] = '133' OR e.[ID] = '134' OR e.[ID] = '135' OR e.[ID] = '139' OR e.[ID] = '140' OR e.[ID] = '142' OR e.[ID] = '178')
                                                        AND AR.[ARType] = 1 AND (CLT.[cmaster]=1 OR CLT.[cinvIndivEng] = 0)
                                                       ) OR
          (@rptType = 'Reviews & Comps' AND e.[DDEventDesc] IN  ('Audit - Review', 'Audit -Comp/Disc','Audit - Comp w/o Disc') AND AR.[ARType] = 1 AND (CLT.[cmaster]=1 OR CLT.[cinvIndivEng] = 0)) OR
          (@rptType = 'Assur Tax Returns' AND e.[DDEventDesc] IN ('5500','720-PCORI','8955-SSA','Campaign Report','Corporate (1120-POL)','LM-1','LM-2','LM-3','LM-4','LM-10','LM-30','Non-Profit (990)','Non-Profit (990 EZ)','Non-Profit (990-N)','Non-Profit (990-T)','Schedule C Letters','Section 104D Letter')  AND AR.[ARType] = 1 AND (CLT.[cmaster]=1 OR CLT.[cinvIndivEng] = 0) )

    GROUP BY 
         CLT.[cltnum],
         CLT.[clteng],
         CLT.[cltName],
         CLT.[CSPLname],
         e.[DDEventDesc],
         Budget.[CBudProjectDesc],
         DUE.[CDTargetAmount],
         DUE.[CDDateDelivered],
         DUE.[CDDateReceived], 
         CLT.[CMGLname]
    Having sum(Bucket.[cinvar])>0

'Audits' is the one that is taking very long.

Does Audits return significantly more or significantly less data than the other options? If so, you should look up parameter sniffing. — Matthew Jaspers, Apr 22 '15 at 15:34
possible duplicate of [SQL Server: Query fast, but slow from procedure](http://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure) — Matthew Jaspers, Apr 22 '15 at 15:37
yes, it is returning about 3 times the amount of data it should. 1500 records — 3xGuy, Apr 22 '15 at 16:25
**You need to show us the table and index definitions**, as well as row counts for each of the tables. Maybe your tables are defined poorly. Maybe the indexes aren't created correctly. Maybe you don't have an index on that column you thought you did. Without seeing the table and index definitions, we can't tell. We need row counts because that can affect query planning. If you know how to do an `EXPLAIN` or get an execution plan, put the results in the question as well. If you have no indexes, visit http://use-the-index-luke.com. — Andy Lester, Apr 22 '15 at 16:53

score 0 · Answer 1 · answered Apr 22 '15 at 15:38

0

Presuming the issue is when @rpType is "Audits", the thing that I notice that is different in this case is that you query on column "[ID]" from table [sab].[dbo].[DDEvents].

There are any number of things that could cause this to run significantly slower. The first thing to check is whether or not an index has been created on column [ID]. If no index, then this would be why it is taking so long.

Another possibility is that it is just returning a lot more data, so taking longer would be normal.

As a side note, instead of that excessive use of "OR" in this part:

AND (e.[ID] =   '132' OR e.[ID] = '133' OR e.[ID] = '134' OR e.[ID] = '135' OR e.[ID] = '139' OR e.[ID] = '140' OR e.[ID] = '142' OR e.[ID] = '178')

You could use "IN":

AND e.[ID] IN ('132', '133',' 134', '135', '139', '140', '142', '178')

The difference shouldn't affect performance--but it does make the code cleaner.

answered Apr 22 '15 at 15:38

Russ

4,091
21
32

russ, IN takes the same amount of time as the or statements. – 3xGuy Apr 22 '15 at 16:27
thank you for everyone input, this wasn't answered in the post above SQL Server: Query fast, but slow from procedure; this runs slow in both. I have Identified that some of the criteria is much slower than others; I have switched back to using an indexed column that is called DDEventDesc it is a varchar(50). not an int. can someone tell me why Audit - personal property tax takes :03 and Audit - EMB takes 2 min? – 3xGuy Apr 23 '15 at 14:54
I have tried to cast to varchar(50) made no difference – 3xGuy Apr 23 '15 at 14:58
but Audit - AUP takes 5 sec – 3xGuy Apr 23 '15 at 15:00

score 0 · Accepted Answer · answered Apr 23 '15 at 22:13

Digging deeper into this I figured out that the reason that some were taking longer than the rest was because of grouping. when I changed the group to an aggregate function it reduced the time down to 3 sec.

new SQL:

DECLARE @rptType varchar(75)
SET @rptType = 'Audits'

SELECT CLT.[cltNum] AS [Client Number] ,
       CLT.[cltEng] AS [Engagement] ,
       CLT.[cltName] AS [Client Name] , 
       CLT.[CSPLname] AS [Assurance Partner], 
       CLT.[cmgLName] , 
       e.[DDEventDesc] AS [Project Type], 
       Budget.[CBudProjectDesc] AS [Project Description], 
       sum(DUE.[CDTargetAmount]) AS [Budget],
        MIN(WIP.[wdate]) [1st Date], 
        CAST(SUM(WIP.[wRate]*WIP.[wHours]) AS decimal(34,2)) [Billable WIP],
        CAST( SUM(AR.[ARProgress])AS decimal(34,2)) [Progress], 
        CAST((SUM(WIP.[wRate]*WIP.[wHours]) - SUM(AR.[ARProgress]))AS decimal(34,2)) [Net Wip],  
        CAST(sum(Bucket.[cinvar])AS decimal(34,2)) [AR Balence], 
        MAX(inv.[InvDate]) AS [Last InvoiceDate], 
        Max(due.[CDDateDelivered]) AS [Project OTD Date]

From [sab].[dbo].[WIP] AS WIP
Join [sab].[dbo].Clients AS CLT
  ON WIP.[wcltID] = CLt.[ID]
Join [sab].[dbo].[cltdue] AS DUE
  ON DUE.[CDID] = WIP.[wDue]
join [sab].[dbo].[DDEvents] AS e
  ON DUE.[cdEventID] = e.[ID]
join [sab].[dbo].[Invoice] AS Inv
  ON WIP.[wInvNum] = INV.[invNumber]
Join [sab].[dbo].[AcctsRec] AS AR
  ON INV.[invNumber] = AR.[ARApplyTo]
Join [sab].[dbo].[ClientBuckets] AS Bucket
  ON CLT.ID = Bucket.ID
JOIN [sab].[dbo].[cltBudget] AS Budget 
  ON clt.ID = cBudCltID 



WHERE (@rptType = 'Audit - Payroll' AND e.[DDEventDesc] = 'Audit - Payroll' AND AR.[ARType] = 1 AND (CLT.[cmaster]=1 OR CLT.[cinvIndivEng] = 0) ) OR
      (@rptType = 'Audits' AND e.[ID] IN (132, 133, 134, 135, 139, 140, 142, 178) AND AR.[ARType] = 1 AND (CLT.[cmaster]=1 OR CLT.[cinvIndivEng] = 0)) OR
      (@rptType = 'Reviews & Comps' AND e.[DDEventDesc] IN  ('Audit - Review', 'Audit -Comp/Disc','Audit - Comp w/o Disc') AND AR.[ARType] = 1 AND (CLT.[cmaster]=1 OR CLT.[cinvIndivEng] = 0)) OR
      (@rptType = 'Assur Tax Returns' AND e.[DDEventDesc] IN ('5500','720-PCORI','8955-SSA','Campaign Report','Corporate (1120-POL)','LM-1','LM-2','LM-3','LM-4','LM-10','LM-30','Non-Profit (990)','Non-Profit (990 EZ)','Non-Profit (990-N)','Non-Profit (990-T)','Schedule C Letters','Section 104D Letter')  AND AR.[ARType] = 1 AND (CLT.[cmaster]=1 OR CLT.[cinvIndivEng] = 0) )

GROUP BY 
     CLT.[cltnum],
     CLT.[clteng],
     CLT.[cltName],
     CLT.[CSPLname],     
     Budget.[CBudProjectDesc],
     e.[DDEventDesc],
     CLT.[CMGLname]
Having sum(Bucket.[cinvar])>0

SQL query taking a long time depending on the option

2 Answers2