Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Slow queries in reporting services! Bug or feature? Expand / Collapse
Author
Message
Posted Monday, July 7, 2008 12:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2014 10:12 AM
Points: 9, Visits: 93
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?
Post #529068
Posted Monday, July 7, 2008 2:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2014 10:12 AM
Points: 9, Visits: 93
Hi again,

now I am confused a little bit more! I did the tests on Saturday and wanted to repeat them and verify the results today. Today I can remove the index hint from the query and the report has the same speed as with hint. On Sunday the index wad rebuilt automatically. Did the Reporting Services recognize it now!?!

Post #529113
Posted Monday, July 7, 2008 3:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Could be fragmentation, could be out of date statistics. There are reasons for SQL not to use an index that looks perfect.
Edit: Sounds like it could also be parameter sniffing.

Could you post the proc, the table design and the indexes that you have on it if you want suggestions on it?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #529133
Posted Monday, July 7, 2008 4:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2014 10:12 AM
Points: 9, Visits: 93
Thanks for your answer GilaMonster!

I am afraid I can tell you no details, but I think this isn't necessary. The index was built just before the tests on Saturday and no new data was inserted during the tests, so the index was up to date.
I built it close to the statement the report uses. I took all the attributes from the where clause and made them the columns for the index. I also took the queried values and added them as additional attributes. So the query could take all data from the (non clustered) index.
I think it was proven that the index works, because the statement speed up in the management studio as expected. I also took a look in the execution plan the management studio displayed and I saw that the index was used as expected.
I am just wondering why the reporting services need much more time to read the data. What do you mean with parameter sniffing? I do not use a stored procedure in the report, it is a plain SELECT statement.

Thanks
Post #529162
Posted Tuesday, July 8, 2008 2:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:57 PM
Points: 317, Visits: 3,615
Rebuilding indexes also updates statistics. My guess is that your statistics weren't up to date and the query optimizer therefore did not use the index.

Regards,
Martin.



Post #529823
Posted Tuesday, July 8, 2008 3:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2014 10:12 AM
Points: 9, Visits: 93
Hi,

you are right, but in my opinion it does not explain why it is fast in the Management Studio (3 seconds) and slow in the reporting services (60 seconds). The execution plan displayed in the management studio shows that everything is fine and the index is used.

At the moment the report server takes always the same time (~60 seconds), with or without hint.

Regards
Oliver
Post #529835
Posted Tuesday, July 8, 2008 3:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:57 PM
Points: 317, Visits: 3,615
Are you using an ODBC connection in Reporting Services rather than the native OLEDB connection? That could be the reason...


Post #529837
Posted Tuesday, July 8, 2008 3:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2014 10:12 AM
Points: 9, Visits: 93
Thanks for the good idea, but no, I am using the direct SQL Server connection (OLEDB). I also played around with the other connection types, but it did not change anything.

Regards
Oliver
Post #529840
Posted Tuesday, July 8, 2008 3:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:57 PM
Points: 317, Visits: 3,615
That's pretty weird...

For consistency I would log into Management Studio with the same user that Reporting Services use (unless you are already using Windows Authentication).

The only other alternative I can think of is to create a stored proc and call that rather than running the native query through Reporting Services.

Hope this helps...



Post #529846
Posted Tuesday, July 8, 2008 4:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Can you run profiler against the server and look at exactly what reporting servces is doing and how long the queries take?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #529864
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse