October 3, 2013 at 4:25 pm
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
October 4, 2013 at 7:19 am
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.
October 4, 2013 at 9:56 am
Do you have this line at the top of your sp? That's helped me in the past.
SET NOCOUNT ON;
October 4, 2013 at 11:39 am
Thanks for this, but I don't use an SP - it is a simple select statement.
October 4, 2013 at 11:41 am
Appreciate your reply, and it's a good idea, but the Supervisors don't want it segregated by rep - they need their whole team.
October 4, 2013 at 11:47 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 4, 2013 at 2:03 pm
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.
October 23, 2013 at 3:16 pm
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:
October 24, 2013 at 3:34 am
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.
October 24, 2013 at 7:15 am
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...
October 24, 2013 at 7:55 am
First thing to check - is the query on SSMS and SSRS definitely running against the same database?
Second, connection options can make a difference to query performance. Things like ANSI_NULLS and transaction isolation level can influence how the query is run. Check what settings are in place when you run it on SSMS, and what settings the SSRS account uses.
Third, it's possible there are two different execution plans - one for the SSMS query and one for SSRS. Here's a crude way of finding out - you may need to tweak it a little:
SELECT c.*, q.query_plan
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_query_plan(c.plan_handle) q
WHERE CAST(q.query_plan AS varchar(max)) LIKE '%InsertaUniquePassageFromYourQueryHere%'
Finally, I noticed in the plan you posted two table scans on the same table. One way of avoiding that may be to select the data you need from that table into a temp table before you start. You'd need to test whether that makes the query more efficient. Others may chip in with better ways to avoid the double scan.
John
October 24, 2013 at 8:17 am
I sure don't see anything that indicates the speed issue is from the query side of this. Generating a 95 page excel doc is going to take some time for sure. Then pushing it down the pipe over the slow connections isn't going to help either.
This isn't a performance thing but that whole big mess of nested case expressions to get the weekday can simplified to:
DATENAME(weekday, SED.earlieststartdate) as 'Weekday',
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 24, 2013 at 8:22 am
Tx Sean, you're right.
I have done away with the weekday name and reduced the report to a single page (maybe 2) by creating a rep parameter as a filter.
Still takes 3 - 4 minutes per rep. The Query still runs in SQL at < 1 second.
Foxed in Ontario...
October 24, 2013 at 8:51 am
Thanks for your advise. I am looking at it now.
Please note that the SSRS server is on the actual SQL Server box - and I am certain they both use the same database & tables.
So there should be zero network latency as I am running the reports through Remote Desktop on the actual SQL Server.
I can't believe I'm the only one seeing these kinds of issues...
Thanks again.
October 24, 2013 at 9:44 am
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',
could be rewritten?
case DATEPART(DW, SED.earlieststartdate) when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
else /*7*/ 'Saturday'
end as 'Weekday'
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply