• 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?

  • Former Member
    Former Member $organization

    It might be SSRS. I find that it slows down the processing. I tried running a report every hour but that doesn’t help.

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Levi Sauerbrei
    Sent: Wednesday, June 05, 2013 3:22 PM
    To: Gloria Ormsby
    Subject: [Tessitura Technical Forum] Report performance issue

     

    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?




    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!

  • $organization

    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

  • Former Member
    Former Member $organization in reply to David Frederick

    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.

  • $organization

    Hi Levi,

     

    Ok – yeah I can see how that would be a challenge. Have you tried running a version of this report tweaked so it can run outside of Tessitura in another browser, like Chrome, assuming you’re even able to do that on RAMP? I know of situations where reports that are rendering slow in IE render quickly in other browsers. That doesn’t provide a solution, but could narrow down where the problem is.

     

    I know you said it renders slowly in BIDS as well… And I don’t know if BIDS is using IE behind the scenes for its report preview functionality (I suspect it does, but I don’t know for sure)…  If BIDS doesn’t use IE, then this theory is may not be relevant!

     

    Thanks,

    David

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Levi Sauerbrei
    Sent: Thursday, June 6, 2013 1:49 PM
    To: David Frederick
    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!

  • $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!