Search code examples
stored-proceduresreporting-servicespreviewfreeze

SSRS preview mode hangind

I have a piece of SQL that takes around 8 secs to load (Pretty chunky).

When Using this as a proc in a new report it hangs while trying to use the preview mode.

I have restarted the reporting services and deleted the re add the data set but it completely kills everything for a good 5-10 minutes.

There is nothing that it can strip back as this is the only data set and there are no sub reports running off this.

Solution

  • I would Look at converting the stored procedure to create a dataset table instead and populate a table isntead of running on the fly in SSRS (assuming this is what happens). So that when you run the SSRS report it queries a flat table. Schedule the stored procedure to update at regular intervals to keep the report up to date.

    Also check how many rows are being returned and if the SSRS report has paging set up (where it limits how many rows it will display)