February 3, 2010 at 1:56 pm
I have a relatively complex stored procedure that runs great. I can execute the sp via Management Studio New Query window and it takes less than 5 seconds to bring back the results.
I created an SSRS report that simply does the same thing (in my feeble mind, anyway). I mean, this SSRS report does the same execute on the same sp. I deploy the report and go into the web browser and run the report and it takes up to 6 minutes! Way too long for any of my users to wait on this particular report.
I feed it the same variables, etc. from both places SSMS and SSRS.
I am doing these things from the same workstation, same user, same sever, etc.
What are some things I need to be aware of, things I need to look for, to try and troubleshoot why kicking off the sp via SSRS takes so terribly much longer than running the same sp via SSMS query window?
Thank you.
Joel
February 3, 2010 at 2:26 pm
If report doesn't have to show real time data - create snapshot or cache it.
February 3, 2010 at 4:29 pm
Could be down to something like different ANSI settings. Is there something silly with different date formats occuring, If you are positive that if you pass the EXACT same parameters in , sp_recompile, the proc first though, in both cases , then the isssue has to be something environmental, ie a connection setting.
February 4, 2010 at 6:17 am
I'd suggest running a trace when you run the sp from SSMS and then from the SSRS Report. Verify that the issue is SQL Server. If you get the same statistics CPU, Reads, Duration from Profiler then the issue is the rendering of the report.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 4, 2010 at 5:59 pm
I recall some time ago we had the same issue where we were passing in the parameters within SSRS to a SQL Dataset and it would slow it all down compared to doing it in SSMS (minutes compared to seconds like your issue). It appeared that when SSRS was passing in the parameter it was possibly recalculating the value and not storing it once and that was it.
What I did was declare a new TSQL parameter first within the dataset and set it to equal the SSRS parameter and then use the new parameter like I would in SSMS.
eg:
DECLARE @X as int
SET @X = @SSRSParameter
March 11, 2010 at 9:01 am
Thanks AshMc, this one worked for me. However my issue now is that it will only work with a single parameter and the query won’t run if I want to pass multiple parameter values.
March 11, 2010 at 9:09 am
We are having exactly the same problem on some of our reports (nice to see we aren't the only ones!) and had come up with the fix of setting local variables and setting them equal to the parameter - works like a charm unless we have to pass multiple values through (which is why we took it out of the stored proc in the first place). Is there a known system fix, or a way to pass multiple values from a prompt into a local variable to bypass the issue? We are just researching the issue now and I will come back if we get a solution before someone else does on the site...
March 11, 2010 at 3:00 pm
I was able to find how I did this previously. I created a Temp table placed the values that we wanted to filter on in it then did an inner join on the main query to it. We only use the SSRS Parameters as a filter on what to put in the temp table.
This saved a lot of report run time doing it this way
DECLARE @ParameterList TABLE (ValueA Varchar(20))
INSERT INTO @ParameterList
select ValueA
from TableA
where ValueA = @ValueB
INNER JOIN @ParameterList
ON ValueC = ValueA
April 4, 2010 at 12:11 am
Many thanks for that - which has probably saved me hours if not days of frustration!
Clive, London UK
June 24, 2010 at 1:23 pm
So did you find any other solution?Could you please post it?
The SQL withing stored procedure I am using passes 11 parameters from SSRS. It takes only 2 seconds after implementing covering index for this query to run in SSMS but in SSRS more than 40 min.
I tried assiging SSRSparameters to local parameters within the stored procedure. It did not help in improving performance in this case.
June 24, 2010 at 1:31 pm
No because no need.
I did find, however, that I had to remove all uses of the called parameters inside the SP, and ensure that they were ALL changed to the internally declared parameters.
June 24, 2010 at 2:12 pm
mehtayogita (6/24/2010)
So did you find any other solution?
No, I gave up and simplified the sp, which is not what I wanted to do but had to move on. I still don't get why it runs so quickly in ssms but then so slow in ssrs. One of those unknown mysteries to me in life!! LOL Sorry.
June 24, 2010 at 4:02 pm
Hi Guys,
I did find out in the end what the actual reason was. Its Parameter Sniffing. I think basically SSRS sends through NULL values for the parameters for the query plan, the query plan wont work as it should particularly if a parameter is on a join I have found. Which makes sense as nothing should be returned if its A = NULL compared with A = A.
Anyway a good article below explaining it. The fix I suggested earlier declaring the parameters in the SQL actually turns the Parameter Sniffing off. I think if you do this in the SP it may have a similar effect.
http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html
June 25, 2010 at 12:59 am
Well i would of said that way back but you stated ...
I feed it the same variables, etc. from both places SSMS and SSRS.
Which would rule out parameter sniffing.
June 25, 2010 at 6:05 am
Dave Ballantyne (6/25/2010)
Well i would of said that way back but you stated ...I feed it the same variables, etc. from both places SSMS and SSRS.
Which would rule out parameter sniffing.
Hi Dave, you would have said what? You may have me confused with AshMc that replied later. I said your quote, and that is true. Same variables both places.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply