April 13, 2012 at 1:28 pm
I have a Dataset that is driven from a stored proc that passes in three parameters (a single value, a multi-select list of values, and a 'ALL'/micro filter that is optional). If I run the stored proc from SSMS with a complex set of parameters, for instance ('97408','{42 values comma separated}','Sugar'), it runs in about 10 seconds which is acceptable. If I choose the exact same list from the IE dropdowns in the deployed report then it runs for over 10 minutes and still doesn't finish.
What gives? What is different about running it from SSRS?
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
April 13, 2012 at 1:36 pm
parameter sniffing? cached plans? stale statistics? Could be any of those. From your brief description it sounds like a "catch all" type of query. Take a look at Gail's blog post here. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/%5B/url%5D
Otherwise take a look at this article[/url] about how to post performance problems.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply