I am creating a report from a very large table. I created a very simple report that simply has one table in it. The table does seem to allow paging but every page load takes so long that it seems as if it was pulling the entire table and then paging locally.
I thought I might have to add the paging to the query myself but this doesn't work because the page variables can only be used in the header or footer
="SELECT * FROM MyTable OFFSET " & =Globals!PageNumber & " * 20 LIMIT 20"
I haven't used SSRS before so I'm not sure if there is something really obvious that I am missing.
Any help with how to do this properly would be appreciated.
If you are using any pagination related expressions, SSRS must retrieve all of the data before it display the first page.
For example, if you are trying to do "Page 1 of X" in the footer, SSRS must figure out how many pages exists (by retrieving and pre-rendering all pages) before it can display page 1.
Also, if you don't need all of the columns, don't use Select *. That will force SSRS to load every column into its cache even if they are not all used. You can cut down on the data retrieval execution time.
Finally, if you have access to the SSRS reporting database, you can take a look at the execution statistics for your report to see where the performance problem is happening. https://msdn.microsoft.com/en-us/library/ms159110.aspx