Post reply

Query takles longer time in SSRS than in query analyzer

  • March 25, 2009 at 12:59 am

    #207720

    Hello all,

    I have a query that has been used to get a report in SSRS.. It takes a very long time (more than 40 mins) to run an d fetch the report in SSRS for just 6 thousand records. However, when I run the same query in the query analyzer window (actually, I use toad as this is an oracle database) it just takes 4 secs.

    Please note : This happens only after adding one particular table in to the query, which totally has 6lacs records.

    Please help me to find a solution.

    Thanks in advance,

    Regards,

    blah baby

  • April 3, 2009 at 12:18 pm

    #971764

    What are you doing with the 6000 rows once they get to your report server? Are you doing aggregation on the report that you could do in the database thus limiting the number of rows returned to your SSRS instance?

    Not an Oracle guy so take this with a grain of salt... I'm sure Oracle have something akin to profiler? can you look at the execution plan when sent from TOAD and when sent from SSRS? How are they different?

    An additional troubleshooting step... MS will tell you to export data from your reportserver database and not to query it directly, and to get to the execution information like this.

    This is great if you want to have easy access to this all the time. If you just need it this one time, go directly to your reportserver database. A query like the following should help you..

    SELECT c.[Name]

    ,[InstanceName]

    ,[ReportID]

    ,[UserName]

    ,[RequestType]

    ,[Format]

    ,[Parameters]

    ,[TimeStart]

    ,[TimeEnd]

    ,[TimeDataRetrieval]

    ,[TimeProcessing]

    ,[TimeRendering]

    ,[Source]

    ,[Status]

    ,[ByteCount]

    ,[RowCount]

    FROM [dbo].[ExecutionLog] el

    INNER JOIN [dbo].[Catalog] c

    ON el.[ReportID] = c.[ItemID]

    WHERE el.[UserName] = 'domain\user'

    AND c.[Name] = 'Your Report Title'

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

  • May 17, 2010 at 7:13 pm

    #1168549

    Not sure if you are still looking for a work around for this but I posted something here: http://www.sqlservercentral.com/Forums/Topic859015-150-1.aspx it might get you over the issue. I read on another post someone saying that SSRS appeared to make the variable nvarchar(4000) which may be the problem.

Viewing 3 posts - 1 through 2 (of 2 total)

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