SSRS: Performance Improvements: SELECT TOP
- Joel Lipman
- SQL Server Reporting Services
- Hits: 9661
This article details a method we used on a search form in a SQL Server Reporting Services (SSRS) environment. The requirement (amongst others) was that the report ran in less than a few seconds.
Analyzing the report through the ReportServer databases (internal auditing) only showed how long the SQL query would take to recover the data and present it in a report, which would average about 0.4 seconds.
We're obviously not interested in shaving off time from 0.4 seconds; no, we're more interested in the fact that 0.4 Microsoft seconds seems to translate to about 10 minutes in the real world. Where do the extra 9 minutes come from? Not the SQL as this runs in 00:00 seconds in SQL Server Management Studio. Not the internal processing and rendering as the report server says it did this in 0.4 seconds. So where's the problem?
How?