Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stored Procedure takes 30 seconds but Report takes 10 minutes

Posted on 2014-10-08
3
Medium Priority
?
94 Views
Last Modified: 2015-01-13
I am using SQL Server 2005 and I have been trying to fix this for a while now. I have tried the parameter sniffing and with recompile but neither have worked.

When executing the stored procedure, it takes 30 seconds and the report takes around 10 minutes. I'm not going to post the whole stored procedure because it is 300 lines, but I'll post the first bit of it. I am lost, and so are my coworkers as to why this is so slow.

    ALTER PROCEDURE [dbo].[sp_PermitAllData]
        @datey datetime
    with recompile
    AS
    BEGIN
       Set NoCount On
    
       Declare @date datetime
       Set @date = @datey

       --Permit11
       select 
           MDate,
           isnull(NumbBreaker, 0) NumbBreaker,
           isnull(Steel20T, 0) Steel20T,
           isnull(Steel9T, 0) Steel9T,
           isnull(Ductile, 0) Ductile,
           isnull(IMF,0) IMF,
           isnull((select sum(NumbBreaker) from Permit p 
                   where MDate>= dateadd(month,-11,Permit.MDate) and MDate<= dateadd(month,-1,Permit.MDate)),0) NumbBreaker11,
           isnull((select sum(Steel20T) from Permit p 
                   where MDate>= dateadd(month,-11,Permit.MDate) and MDate<=dateadd(month,-1,Permit.MDate) and MDate>='10/1/2008'),0) Steel20T11,
           isnull((select sum(Steel9T) from Permit p where MDate>= dateadd(month,-11,Permit.MDate) and MDate<= dateadd(month,-1,Permit.MDate)and MDate>='10/1/2008'),0) Steel9T11,
           isnull((select sum(Ductile) from Permit p where MDate>= dateadd(month,-11,Permit.MDate) and MDate<= dateadd(month,-1,Permit.MDate)),0) Ductile11,
           isnull((select sum(IMF) from Permit p where  MDate>= dateadd(month,-11,Permit.MDate) and MDate<= dateadd(month,-1,Permit.MDate)),0) IMF11
        Into #Permit11 
        from 
           Permit 
        where 
           MDate between dateadd(month,-2,@date) AND @date
    
        --270
      
       SELECT dateadd(month,datediff(month,0,fdate),0) Month,-1*SUM(fqty) 'A270',
           (select -1*sum(fqty) 
            from M2MDATA01.dbo.intran t  INNER JOIN
                 M2MDATA01.dbo.inmast m ON t.fpartno = m.fpartno INNER JOIN 
                 M2MDATA01.dbo.jomast j ON t.ftojob = j.fjobno INNER JOIN
                 M2MDATA01.dbo.inmast m2 on j.fpartno=m2.fpartno and j.fpartrev=m2.frev and j.fac=m2.fac
                 --PEntries e ON j.fpartno = e.Sand 
            where dateadd(month,datediff(month,0,t.fdate),0) BETWEEN dateadd(month,-11,dateadd(month,datediff(month,0,intran.fdate),0)) and dateadd(month,-1,dateadd(month,datediff(month,0,intran.fdate),0)) AND
                 fdate>='6/1/2008' AND
                  t.ftype = 'I' AND 
                  (m.fdescript in('TECHNISET 6435 PART 2 339749','TECHNISET F6000 PART 1 347944') OR m.fdescript LIKE 'ACTIVATOR%')  AND 
                  m2.fcusrchr2='270') 'A27011'
                  
    Into #270 
    FROM   M2MDATA01.dbo.intran intran  INNER JOIN
           M2MDATA01.dbo.inmast inmast ON intran.fpartno = inmast.fpartno INNER JOIN 
           M2MDATA01.dbo.jomast jomast ON intran.ftojob = jomast.fjobno INNER JOIN
            M2MDATA01.dbo.inmast m2 on jomast.fpartno=m2.fpartno and jomast.fpartrev=m2.frev and jomast.fac=m2.fac
          -- PEntries ON jomast.fpartno = PEntries.Sand 
    WHERE  dateadd(month,datediff(month,0,fdate),0) BETWEEN dateadd(month,-2,@date) and @date AND
           intran.ftype = 'I' AND 
          (inmast.fdescript in('TECHNISET 6435 PART 2 339749','TECHNISET F6000 PART 1 347944') OR inmast.fdescript LIKE 'ACTIVATOR%') AND 
           m2.fcusrchr2='270'
    GROUP BY dateadd(month,datediff(month,0,fdate),0), dateadd(month,-11,dateadd(month,datediff(month,0,intran.fdate),0)), dateadd(month,-1,dateadd(month,datediff(month,0,intran.fdate),0))
    ORDER BY dateadd(month,datediff(month,0,fdate),0)

Open in new window

0
Comment
Question by:hscast
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 40369412
I think you need to rewrite the queries to reduce overhead.

Here's a possible rewrite of the first query to check out:


    ALTER PROCEDURE [dbo].[sp_PermitAllData]
        @datey datetime
    with recompile
    AS
    BEGIN
       Set NoCount On
   
       Declare @date datetime
       Set @date = @datey
       
       If Object_Id('tempdb..#PermitMonths') is not null
           Drop Table #PermitMonths

       Select
            IDENTITY(int, 1, 1) AS id,
           dateadd(month,datediff(month, 0, MDate), 0) AS MDateMonth,
           sum(NumbBreaker) AS NumbBreaker,
           sum(Steel20T) AS Steel20T,
           sum(Steel9T) AS Steel9T,
           sum(Ductile) AS Ductile,
           sum(IMF) AS IMF
        Into #PermitMonths
        from
           Permit
        where
           MDate between dateadd(month,-14,@date) AND @date
        group by
           MDate
       

       --Permit11
       select
           MDate,
           isnull(NumbBreaker, 0) NumbBreaker,
           isnull(Steel20T, 0) Steel20T,
           isnull(Steel9T, 0) Steel9T,
           isnull(Ductile, 0) Ductile,
           isnull(IMF,0) IMF,
           isnull((select sum(NumbBreaker) from #PermitMonths pm
                   where pm.MDate>= dateadd(month,-11,Permit.MDate) and pm.MDate<= dateadd(month,-1,Permit.MDate)),0) NumbBreaker11,
           isnull((select sum(Steel20T) from #PermitMonths pm
                   where pm.MDate>= dateadd(month,-11,Permit.MDate) and pm.MDate<=dateadd(month,-1,Permit.MDate) and pm.MDate>='20081001'),0) Steel20T11,
           isnull((select sum(Steel9T) from #PermitMonths pm where pm.MDate>= dateadd(month,-11,Permit.MDate) and pm.MDate<= dateadd(month,-1,Permit.MDate)and pm.MDate>='20081001'),0) Steel9T11,
           isnull((select sum(Ductile) from #PermitMonths pm where pm.MDate>= dateadd(month,-11,Permit.MDate) and pm.MDate<= dateadd(month,-1,Permit.MDate)),0) Ductile11,
           isnull((select sum(IMF) from #PermitMonths pm where  pm.MDate>= dateadd(month,-11,Permit.MDate) and pm.MDate<= dateadd(month,-1,Permit.MDate)),0) IMF11
        Into #Permit11
        from
           Permit
        where
           MDate between dateadd(month,-2,@date) AND @date
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40370125
How many records returns this SP? And the report's processing data (transformations, calculations,...)?
You may also check for network bottlenecks.
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 1000 total points
ID: 40449608
Hi,
As earlier explained,
:: the procedure code can be improved further
:: the number of records returned by SP will make a considerable difference
A few more points that you can check
:: lot of formatting in SSRS will make the the report rendering slow (conditional data change will further impact performance, try to change data in SQL SP only)
:: disable the [Print Layout] option in your report and then try to run and see its performance
:: check the trace/profiler, to identify the actual time taken by SP (when run from the SSRS)
:: try to modify the SP to return only top few (e.g. 10) rows, and then run the SSRS report to see, if large number of rows is actually making the performance hit.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question