• Replies 5 replies
  • Subscribers 338 subscribers
  • Views 42 views
  • Users 0 members are here
Related

Report performance issue

Former Member
Former Member $organization

Hoping some of you server-savvy types can help me out.

I have a report that is running slowly.  I keep tweaking the SQL to improve the efficiency of the query.  Seems to work great.  I make the changes and run the report 3 or 4 times.  Render time drops from 5 minutes to 40 seconds.  Great!

An hour later, it's taking 5 minutes again without any additional changes having been made.

When I run the query from Management Studio it is plenty fast.  Using the same parameters, I get results in 10 seconds.  From BIDS or from Tessitura, it is several minutes.

Where should I be looking for the bottle neck?

Parents
  • $organization

    Levi,

     

    You can see the execution of the ssrs reports by using the SP:CacheHit and RPC:Completed events.  However I can’t remember where the login name to filter on was coming from. If I remember correctly it was either the RS execution user name or the user name used in starting the service. In our environment though, it is not the tessapi login as I first expected.

     

     

    -doug

     

     

    From: Levi Sauerbrei [mailto:bounce-levisauerbrei7046@tessituranetwork.com]
    Sent: Thursday, June 06, 2013 1:44 PM
    To: Doug Jones
    Subject: Re: [Tessitura Technical Forum] Report performance issue

     

    David,

    Profiler is one of my favorite tools.  Unfortunately with SSRS reports, I'm not able to see the actual SP execution as I could with Infomaker.  Now all I see is an execute statement for LP_PREPROCESS_URL at which point everything is handed over to the SSRS engine and I don't see the subsequent calls being used in the report.  We are on RAMP, so I'm assuming that those calls are happening on a server I can't see.  But it is a frustration.

    I'm sure this is a problem with how SSRS is rendering the data it's getting.  The query itself is plenty fast (I think). But I'm not familiar enough with what's under the hood of SSRS to know what might be causing a performance issue there.

    From: David Frederick <bounce-davidfrederick9045@tessituranetwork.com>
    Sent: 6/6/2013 1:55:21 PM

    Hi Levi,

    Have you used SQL Profiler while running the report in SSRS? If not, I would suggest doing that using the Tuning template (or whatever template/configuration you like to use for such things). You should be able to pinpoint whether it is your query or something else that is making it run long. That would be the first thing I would be checking if I ran into something like this.

    Thanks,
    David




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Reply
  • $organization

    Levi,

     

    You can see the execution of the ssrs reports by using the SP:CacheHit and RPC:Completed events.  However I can’t remember where the login name to filter on was coming from. If I remember correctly it was either the RS execution user name or the user name used in starting the service. In our environment though, it is not the tessapi login as I first expected.

     

     

    -doug

     

     

    From: Levi Sauerbrei [mailto:bounce-levisauerbrei7046@tessituranetwork.com]
    Sent: Thursday, June 06, 2013 1:44 PM
    To: Doug Jones
    Subject: Re: [Tessitura Technical Forum] Report performance issue

     

    David,

    Profiler is one of my favorite tools.  Unfortunately with SSRS reports, I'm not able to see the actual SP execution as I could with Infomaker.  Now all I see is an execute statement for LP_PREPROCESS_URL at which point everything is handed over to the SSRS engine and I don't see the subsequent calls being used in the report.  We are on RAMP, so I'm assuming that those calls are happening on a server I can't see.  But it is a frustration.

    I'm sure this is a problem with how SSRS is rendering the data it's getting.  The query itself is plenty fast (I think). But I'm not familiar enough with what's under the hood of SSRS to know what might be causing a performance issue there.

    From: David Frederick <bounce-davidfrederick9045@tessituranetwork.com>
    Sent: 6/6/2013 1:55:21 PM

    Hi Levi,

    Have you used SQL Profiler while running the report in SSRS? If not, I would suggest doing that using the Tuning template (or whatever template/configuration you like to use for such things). You should be able to pinpoint whether it is your query or something else that is making it run long. That would be the first thing I would be checking if I ran into something like this.

    Thanks,
    David




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Children
No Data