dire performance...

  • hi all

    I've had a look around and cant seem to find any answers to this. Well, I've found answers but no solutions I should say so I was wondering if maybe this just normal.

    Reports that I am running are taking a fairly useless amount of time ie I have one running now which renders 261 rows (by 15 columns) from a database and takes about 17 mins to run. There are 7 queries executed in it, all stored procedures, NOCOUNT is off in those procedures, the main query (261/15) takes about 2 seconds to run as its just a simple select * from tbl_foo query, all the heavy sql is done prior to getting to the report and placed into tbl_foo for easy access. There are images being rendered as well but only a few

    When I run it through the reportserver it is equally as dire. I'm sure it shouldnt be like this. I've tried all the solutions I can find but no improvements to be had.

    SQL 2008 R2 server seperate from webserver. Processing power is no issue neither is RAM both are ridiculously large. I added some event logging into the code (C#) and its when we get to render section that it keels over.#

    time here:13:03:14

    var result = rsExec.Render(format, deviceInfo,

    out extension, out encoding,

    out mimeType, out warnings, out streamIDs);

    time here :13:19:27

    obv not very user friendly!

    Any thoughts very much appreciated

    TIA

    tim

  • There is something called parameter sniffing that frequently has very negative consequences on SSRS reports. Fortunately there is an easy way around it.

    In all the stored procedures that receive parameter input from the report, take that parameter input and convert it to a variable local to the sp. Lets say you have SP Input parameters of startdate and enddate.

    @StartDate DATETIME,

    @EndDate DATETIME

    AS

    BEGIN

    SET NOCOUNT ON;

    Take the values from those inputs and convert them to local:

    @StartDate DATETIME,

    @EndDate DATETIME

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @Start DATETIME

    DECLARE @End DATETIME

    SET @Start = @StartDate

    SET @End = @EndDate

    Then edit the rest of the code in your sp to use the local variables instead of the input parameters.

    Hope this helps.

  • what does the execution log (reportserver database|Table ExecutionLog) say of this report? It should give you some clues as to where you are actually seeing the issue. At the very least it should point you in the direction of your problem.

    Pay particular attention to the TimeDateRetrieval and TimeRenderign Columns.

    If TimeDataRetrieval is high then you need to tune your query, take a trace of the database and see exactly what execution plans etc. your report i using, if timeRendering is high then it's something with the way the report is designed. There are ways to optimize that as well, but let's first find where the problem is, then try to address it.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • hi thanks both for getting back to me,

    i changed all the stored procs to get rid of the param sniffing, which unfortunately didnt make any diff here is the output from the execution log (this is the one I mentioned that took 17mins to run).

    output in attached file, but the main problem seems to be the TimeProcessing value, the rendering one is ok I guess 261 PDF pages so 13 or so seconds seems reasonable, query ran in just over 4 seconds, so I just need to work out the processing part of it, however I do that....

  • yeah it's your time processing that's really really high. 954 seconds there... the question then becomes what is happening on your report? Are you doing a lot of data aggregation on your report, lots of grouping levels, many subreports, etc... If so, can any of these be done in your query/stored procedures etc? What about custom logic, custom code being referenced on your report? These are all things that may contribute to a rather lengthy processing period. Can you move any of the aggregation or formatting or other such things to you TSQL code and have it happen more quickly there?

    Here is a link to the SQL CAT team and some recommendations on how you might be able to approach troubleshooting the performance of this report.

    http://sqlcat.com/technicalnotes/archive/2009/01/14/reporting-services-performance-optimizations.aspx

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • hi luke

    yeah I kind of figured the processing was a bit insane, theres about 6 sub reports and some other sprocs, so I figured 261*11 (or so queries) was a bad thing. Personally I'd have done the grunt work outside and reduced my data lookups to 1 or 2 ideally.

    I was given this so I didnt have any control over the original, Ive just been tasked with making it fast.... eek.

    Time for sql fun me thinks, I'll check that link and work on the sql tomorrow.

    thanks again to all

    tim

  • yeah it sounds like you've been given a load of crap and told to make it look like gold. Most likely the best thing to do is start over. Do your aggregations and calculated columns in TSQL if you can and then just make SSRS responsible for output and appearance. Subreports are fine, and can work well, but be cognizant of some of their performance implications. Perhaps you might need to look more at drill throughs rather than straight subreports? A lot of it depends on how the report will be used.

    You mentioned earlier that it amounts to 261 PDF Pages... Will someone actually be using each of those 261 pages? In my experience most report consumers won't weed through that many pages, and if they do it's typically just to aggregate them further or to store the report for some historical or reference purpose. Perhaps there will be better way to display the data in a more usable format. If you are stuck using this format, and are having no luck with getting your SQL to preaggregate you data, you might consider setting the report up as a snapshot or put it into the cache. Basically execute it once in the middle of the night when no one will notice the slowness and then display the cached version for that day. There's a decent amount of information in BOL here. http://msdn.microsoft.com/en-us/library/ms155927.aspx

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply