0

I have a weird issue with one of the data driven subscriptions on SSRS. The subscription is a timed subscription that generates invoices (pdf/excel) and gets triggered by a stored procedure.

The issue we are facing is that the first run always takes 30-60 minutes regardless of how many invoices are being generated. Once the first run has completed the subsequent runs are completed under a minute throughout the day.

There is a second version of the same report that is run manually and it runs fine(ruling out any delays with the data extraction bit).

I have looked at some other questions here but that didnt help identify the problem:

SQL Reporting services: First call is very slow

SSRS report subscription not working sometime

Community
  • 1
  • 1
Zeb
  • 1
  • There doesn't seem to be a problem here. Or a question. It's working, right? – R. Richards May 17 '16 at 11:54
  • It works fine after the first execution finishes but the first run takes almost an hour which is quite frustrating for most users. By first run I mean the very first time the subscription is triggered on a day regardless of what time during the day. – Zeb May 17 '16 at 12:05
  • What is the difference between the initial run and subsequent runs? Is there a huge difference in the amount of data the report is processing? Without more information, I am not sure you are going to get an answer from any one. You may have to do more detailed analysis of what is actually happening during that initial run. Any way to log what is going on in the stored procedure? Maybe run a trace to see what is happening at the time? Your DBA (if you have one) should be able to help. – R. Richards May 17 '16 at 12:28
  • unfortunately do not have a DBA. There is no difference between the initial and subsequent runs. The subscription gets triggered by another report that has lists of invoices. After an hour of the first run when it has finished if i run the subscription for the same invoices it takes less than a minute to generate all the invoices that took about an hour in the first run. Running it any time during the day after it has run the first time takes no time. There is no data caching pulling the data every time it is triggered. – Zeb May 17 '16 at 14:19

1 Answers1

0

Without knowing more about the query, data, database setup, other process, etc.; it will be quite difficult to say for sure. But if I had to guess, based on your description, it sounds like the query plan cache is lost and is rebuilt on the first run of the day. Without the plan the query can be less efficient. Each subsequent run will use the plan created on the first run, and will therefor run more quickly. There are a number of reasons that could cause the query plan to be wiped from cache. A recompile, other queries using too much memory, not enough system memory to begin with etc.

Hope that helps!

Corby
  • 1
  • 3