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 Tuesday, July 8, 2008 7:05 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 again for all the good ideas and hints.
I took a look at the statements with the profiler, but I can see no difference between the statements. I also took a look at the system with Filemon and Procmon and I can see, that the query in the management studio only causes minor drive access and the query in the reporting services causes much more drive access. To me it still looks like doing an index scan vs. a full table access, but I am not sure about it. I will know try to switch of all indexes and look if the queries take the same time...

Regards
Oliver
Post #529993
Posted Tuesday, July 8, 2008 7:54 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: Today @ 12:11 PM
Points: 42,470, Visits: 35,541
And you see the one from reporting services with duration = 60 sec?

Can you perhaps post the query?



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 #530045
Posted Tuesday, July 8, 2008 8: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
Hi,

here are the results. First picture (ManagementStudio.jpg) shows statement and result for direct execution in the management studio. The second picture shows the result for the reporting services. You can clearly see the difference 3,2s vs. 49,5s.
I also took a look at the index usage statistics and I can see that the index is used by the reporting services... Where are the missing 46 seconds...

Regards
Oliver


  Post Attachments 
ManagementStudio.jpg (163 views, 155.53 KB)
ReportService.jpg (162 views, 187.12 KB)
Post #530091
Posted Tuesday, July 8, 2008 9:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 29, 2010 9:37 PM
Points: 114, Visits: 57
Hi,
As you can see from both images the reporting service is using the dynamic SQL. I hope if you create stored procedure will increase the performance.
Post #530131
Posted Tuesday, July 8, 2008 10:01 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,

Yes, you've got it! I am impressed, the report is now finished in 7 seconds! Collecting the data now only takes 0,3s in reporting services, 10 times faster than it is in management studio. I am glad to have this result, but I must confess, that I do not really understand it. Do you have more information about this topic or some good links?
I think I will use stored procedures more often in the future...

Regards
Oliver
Post #530169
Posted Tuesday, July 8, 2008 10:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 29, 2010 9:37 PM
Points: 114, Visits: 57
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
Post #530186
Posted Wednesday, July 9, 2008 10:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 1:47 PM
Points: 215, Visits: 415
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.


Post #531056
Posted Wednesday, July 9, 2008 11:44 PM
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,

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
Post #531365
Posted Friday, October 17, 2008 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 28, 2009 4:06 PM
Points: 2, Visits: 6
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/
Post #587810
Posted Friday, October 17, 2008 10:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 28, 2009 4:06 PM
Points: 2, Visits: 6
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/
Post #587811
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse