SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slow queries in reporting services! Bug or feature?


Slow queries in reporting services! Bug or feature?

Author
Message
Oliver Heilmann
Oliver Heilmann
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 96
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?
Oliver Heilmann
Oliver Heilmann
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 96
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!?!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220569 Visits: 46279
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, MVP, M.Sc (Comp Sci)
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


Oliver Heilmann
Oliver Heilmann
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 96
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
Martin Schoombee
Martin Schoombee
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4147 Visits: 4546
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.



Oliver Heilmann
Oliver Heilmann
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 96
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
Martin Schoombee
Martin Schoombee
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4147 Visits: 4546
Are you using an ODBC connection in Reporting Services rather than the native OLEDB connection? That could be the reason...



Oliver Heilmann
Oliver Heilmann
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 96
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
Martin Schoombee
Martin Schoombee
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4147 Visits: 4546
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...



GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220569 Visits: 46279
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search