Hi all together,
in my company we are using reporting services to provide some statistics and reports to our customers. In this special case we wanted to offer a report that displays some information that is grouped by day of week - date - hour. The report is based on a single table that contains around 40 million entries at the moment.
First the report took 5 minutes to finish and that is to slow in the opinion of our customer. We tested the query in the management studio and saw that collecting the data took around 2 minutes there. We created a suitable index on the table and then the statement was finished in 3 seconds, a good result!
Now we checked the report again, but it took still more than 2 minutes to finish. In the execution plan table of the reporting services it was clearly visible that most of the time was spent in fetching the data (~2 minutes) and that rendering the report only took some seconds.
Further investigations showed, that the report does a full table scan to collect the data, even if a perfect index for the query exists. After some more hours of work we added a hint to the query that this special index should be used. Now the report is rendered in around 55 seconds. In the execution plan we see around 45 seconds for collection the data. That is much better.
But here are the questions:
1. Why do we have to give a hint to use the index. What is the difference between a query inside the reporting services and inside the management studio.
2. Why does the query still take so long? It is around 15 times slower than expected.
3. Any more ideas to speed the process up a little bit more?