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


SQL Query runs fast, SSRS runs S L O W . . . .


SQL Query runs fast, SSRS runs S L O W . . . .

Author
Message
david.kotchie
david.kotchie
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7742 Visits: 2629
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.
adams.squared
adams.squared
Mr or Mrs. 500
Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)

Group: General Forum Members
Points: 505 Visits: 441
Do you have this line at the top of your sp? That's helped me in the past.

SET NOCOUNT ON;
david.kotchie
david.kotchie
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 31
Thanks for this, but I don't use an SP - it is a simple select statement.
david.kotchie
david.kotchie
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60415 Visits: 17954
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 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)
david.kotchie
david.kotchie
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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.

Attachments
pascorecard.sqlplan (16 views, 97.00 KB)
david.kotchie
david.kotchie
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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....w00t
Scott Murray-240410
Scott Murray-240410
SSC Eights!
SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)

Group: General Forum Members
Points: 959 Visits: 3142
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.
david.kotchie
david.kotchie
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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...
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