We have an SSRS Report with a subscription. The report gets emailed out to 400+ users once a week. We run a query to get the list of emails to send it to.
Originally, we ran this report for each user and then email it out, even though the report was the same for each user. It took over 5 hours to send out all of the reports.
We now changed the report to cache first and then send the report out to all of the emails in the list.
It now distributes the report in just over an 1 hr.. I think this is still slow, but maybe I am wrong. I'm thinking this is something that should take minutes to run, not hours.
The report varies in size from 250 kb to 750 kb.
I have another report that does something similiar, but for only 8 reports, but every report is different for each user. This all happens almost instantly. Not sure what the difference is.
Any suggestions on where to look to figure out why this takes so long. Is there something built into SSRS to slow down the distribution or delay it?
Nothing built into SSRS that should slow this down. I'd take a look at the connection between your SQL server and the SMTP server that SSRS is using. I suspect the problem is the data in the email, usually as an attachment, and simply getting that data to the SMTP server.
Also, you can set this up to send one email to multiple users or send an email to each user individually: Have you considered the one email approach?