Slow queries in reporting services! Bug or feature?

Question

Slow queries in reporting services! Bug or feature?

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

jegu SSC Veteran Points: 246 More actions · Answer 1

Hi,

I don't have any links as of now and i hope to update in the future. In order to go deeper we have to fully understand how the reporting service is executing the queries.

I suggest you to use SP's for ease of maintainability. Changing the RDL file is not easy once it's deployed. So whenever you develop reports in Reporting services design in a such way that you are not touching the RDL when you want to implement new changes.

Thanks,

Jegatheesh

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 2

Just for the record, I'd note that the RS SQL Statement had the index hint, while the MS SQL Statement did not.

sp_executesql generates a query plan "more likely to be reused". (http://msdn.microsoft.com/en-us/library/ms175170.aspx). It's possible, that based on your tests, the state of your statistics and and your stored procedure cache, you were simply stuck with different query plans.

I'd look at the query plans for these statements separately. One technical point, is that in RS, your parameters were datetime while the MS parameters are string (hard coded).

Also, in general I'd like to understand what you're trying to get at with the "DATEADD" criteria in your where statement. I'm not sure if the optimizer would pick up the index properly (assuming XSALESDATETIME is part of the special index.) I think you're doing a date range, but I'm not sure.

Oliver Heilmann SSC Enthusiast Points: 119 More actions · Answer 3

Hi,

thanks to all for their help and tips.

You are right, the RS statement still had the index hint, but it did not help. The result is the same without the index hint.

You are also right, that there are slight differences. Yes, the date is hard coded as string in managements studio, but is a datetime type in RS. I hope this does not make a difference.

The date part of the statement looks a little bit strange, I know. The original data has a timestamp with hour, minute and second, but we are only interested in the data for the whole day. I read about this little trick somewhere (maybe here at SQLCentral...). But I am sure the index works with this, because without it, the same statement takes some minutes to finish...

I am happy with the result now. All I want to understand is, why is SQL Server 2005 behaving differently if the statement is executed directly or in a stored procedure. I will search the web for some more information.

Regards

Oliver

torkaman Newbie Points: 8 More actions · Answer 4

I have the same problem with reporting services. I am not much of a write, so I send you to the following URL, it talks about a phenomenon called parameter sniffing, word reading

http://www.lockergnome.com/sqlsquirrel/2007/12/14/victim-of-parameter-sniffing/

torkaman Newbie Points: 8 More actions · Answer 5

:cool:I have the same problem with reporting services. I am not much of a write, so I send you to the following URL, it talks about a phenomenon called parameter sniffing, word reading

http://www.lockergnome.com/sqlsquirrel/2007/12/14/victim-of-parameter-sniffing/

ddebski Mr or Mrs. 500 Points: 557 More actions · Answer 6

In SSMS guery is fast but in SSRS gouery is slow. I understand what I should to do.

I have some query. In MSSQL I run this guery and I have result after 1 second. When I run report in Reporting Services, I have result after 18 second.

What should I do?

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 7

ddebski (8/7/2013)
In SSMS guery is fast but in SSRS gouery is slow. I understand what I should to do.
I have some query. In MSSQL I run this guery and I have result after 1 second. When I run report in Reporting Services, I have result after 18 second.
What should I do?

Read this whole thread and try to understand what these Bi masters are saying about this? 😀