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 12»»

SQL Query runs fast, SSRS runs S L O W . . . . Expand / Collapse
Author
Message
Posted Thursday, October 3, 2013 4:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 PM
Points: 12, Visits: 31
GentleGuri,

I am running a scorecard report for a team of supervisors and their field reps.
It comes from single SQL select statement from 1 table, returns 647 rows (for the supervisor I picked) in < 1 second.

There are 3 parameters - the start and end weeks (Integers) plus the Supervisor name (varchar).

In SSRS the report takes about 4.5 minutes to run, 99.9% of that time being in the 'Data Retrieval' category.
The final report has 95 pages, with page breaks after each rep (95 reps), plus up & down trend arrows for each metric in each row.
The report gets exported as Excel format, and is run in the field by users with slow connections, so I am under great pressure to speed it up.

I saw some fixes in the 'net (parameter sniffing) and made the suggested changes, but still no love.

Anyone have any thoughts?

Thanks in advance!

Regards,

David
Post #1501375
Posted Friday, October 4, 2013 7:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 12:48 PM
Points: 2,818, Visits: 2,566
You have done pretty much all the basics I would do to troubleshoot an issue like this. I would consider constraining the report with a REP parameter so that the dataset query returned even less data and the slow connection would only have one page pushed over it rather than 95. Good luck.
Post #1501590
Posted Friday, October 4, 2013 9:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 12:39 PM
Points: 147, Visits: 424
Do you have this line at the top of your sp? That's helped me in the past.

SET NOCOUNT ON;
Post #1501659
Posted Friday, October 4, 2013 11:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 PM
Points: 12, Visits: 31
Thanks for this, but I don't use an SP - it is a simple select statement.
Post #1501711
Posted Friday, October 4, 2013 11:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 PM
Points: 12, Visits: 31
Appreciate your reply, and it's a good idea, but the Supervisors don't want it segregated by rep - they need their whole team.
Post #1501713
Posted Friday, October 4, 2013 11:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
david.kotchie (10/4/2013)
Thanks for this, but I don't use an SP - it is a simple select statement.


I am a little confused here. You say you don't have this in a stored proc but in your original post you stated:


I saw some fixes in the 'net (parameter sniffing) and made the suggested changes, but still no love.


Parameter sniffing is all about stored procs so either you are calling one or you aren't. Also if you made the suggested changes you should tell us what those are. I have seen lots of places online that suggest "fixes" for parameter sniffing that are actually worse.

A query that runs fast in SSMS but slow in the application is the classic sign of parameter sniffing. Can you post your query? It would also be helpful if you can post the actual execution plan both from SSMS and when a report is run.


_______________________________________________________________

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 #1501717
Posted Friday, October 4, 2013 2:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 PM
Points: 12, Visits: 31
Ok, here we go:

NO SP - it is a very simple query -

select sed.RM,
SED.DM 'Supervisor',
SED.[week] 'LCL_Weekno',
SED.projectNumber,
SED.ProjectName 'ProjName',
SES.Total_Shifts,
SED.Staff,
SED.shifts 'Shift',
SED.daysdelay,
SED.locationnumber,
SED.earliestStartDate,
case when DATEPART(DW, SED.earlieststartdate) = 1 then 'Sunday' else
case when DATEPART(DW, SED.earlieststartdate) = 2 then 'Monday' else
case when DATEPART(DW, SED.earlieststartdate) = 3 then 'Tuesday' else
case when DATEPART(DW, SED.earlieststartdate) = 4 then 'Wednesday' else
case when DATEPART(DW, SED.earlieststartdate) = 5 then 'Thursday' else
case when DATEPART(DW, SED.earlieststartdate) = 6 then 'Friday' else
case when DATEPART(DW, SED.earlieststartdate) = 7 then 'Saturday' end end end end end end
end as 'Weekday',
SED.connections 'Your_Connections',
SES.Project_Connections,
SED.SamplesTarget,
SED.samples 'Your_Samples',
SES.AveProjSamples,
SED.Sales 'YourSales',
ses.ProjSale$,
SED.Units 'YourUnits',
SES.ProjUnits,
SES.ProjUnits/SES.Total_Shifts as 'AverageProjectSales',
case when SED.samples = 0 then 0 else SED.Units/SED.samples end as 'Conversion',
case when SED.samples = 0 then 0 else ses.projUnits/(SES.AveProjSamples*SES.Total_Shifts) end as 'ProjectConversion',
case when SED.daysdelay > 0 then 0 else 10 end as 'DaysDelayScore',
case when SED.samples >= SED.SamplesTarget then 30 else
(sed.samples/sed.samplestarget) * 30 end as 'SamplesScore',
case when sed.samples = 0 then 0 else
case when (SED.Units/SED.samples) >= .25 then 30 else
((SED.Units/SED.samples)/.25)* 30 end end as 'ConversionScore',
case when SED.Units >= (SES.ProjUnits/SES.Total_Shifts)*2 then 30 else
SED.Units/((SES.ProjUnits/SES.Total_Shifts)*2) *30 end as 'SalesScore'
from dbo.SalesExecDetail SED inner join
(select projectnumber,
SUM(shifts) 'Total_Shifts',
SUM(connections) 'Project_Connections',
Sum(Samples)/SUM(Shifts) as 'AveProjSamples',
SUM(sales) 'ProjSale$',
SUM(units) 'ProjUnits'
from dbo.SalesExecDetail
group by ProjectNumber) as SES on sed.projectnumber = ses.projectnumber
where sed.dm = @Supervisor
and sed.[week] between @StartDate and @EndDate
and SED.earliestStartDate >= '2013-01-01'


The fix I tried was in include separate parameters and link them to the report parameters - made no difference (since it is not an SP). This query runs in < 1 sec from SQL analyser, I have incl;uded the execution plan. Not sure how to get it out of SSRS....





Sean Lange (10/4/2013)
david.kotchie (10/4/2013)
Thanks for this, but I don't use an SP - it is a simple select statement.


I am a little confused here. You say you don't have this in a stored proc but in your original post you stated:


I saw some fixes in the 'net (parameter sniffing) and made the suggested changes, but still no love.


Parameter sniffing is all about stored procs so either you are calling one or you aren't. Also if you made the suggested changes you should tell us what those are. I have seen lots of places online that suggest "fixes" for parameter sniffing that are actually worse.

A query that runs fast in SSMS but slow in the application is the classic sign of parameter sniffing. Can you post your query? It would also be helpful if you can post the actual execution plan both from SSMS and when a report is run.


  Post Attachments 
pascorecard.sqlplan (10 views, 97.42 KB)
Post #1501766
Posted Wednesday, October 23, 2013 3:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 PM
Points: 12, Visits: 31
Ok Guys,

I have posted the SQL (See prev post) and the execution plan. I have added the missing indexes, and finally I have added a 'rep' parameter so that the report only returns one rep at a time. Again, the RAW SQL returns in < 1 second. SSRS takes 3 + minutes to return 1 rep, max 30 rows of data.

I'm completely lost here...

Any brilliant ideas?

Lost in Toronto....
Post #1507833
Posted Thursday, October 24, 2013 3:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:27 AM
Points: 243, Visits: 2,738
Have you tried using Profiler to see if the query is really taking that long as opposed to the SSRS web service? If the problem is actually with the rendering (SSRS), you may want to see what is going on with the memory on the machine.
Post #1507940
Posted Thursday, October 24, 2013 7:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 PM
Points: 12, Visits: 31
Tx,

I'll try that, but this is a 64 bit server with 64 GB RAM, I've never seen it go beyond 50% mem useage. When I look at the report server tables they report 99% of the duration is in data retrieval...
Post #1508035
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse