December 14, 2010 at 9:20 am
As I said, My stored procedure runs in about a second in SSMS. There's nothing overly complicated going on - quite a lot of left joins to derived tables but nothing that i'd expect to give me a problem.
I have put it in a report through Visual Studio, and it is taking ages to run - in fact its been going over half an hour now and although it says its generating a report, its clearly just hanging. I have deployed it to the report server and it is the same - very very slow.
Why would this be?
December 14, 2010 at 10:14 am
Any complex conditional formatting or nesting in the report?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2010 at 12:28 pm
I have run into an issue called Parameter Sniffing that can cause a report to run painfully slow. This can/does happen when you are feeding parameters from the report to the SP. Fortunately there is a very simple way around it.
In your SP you have your input variables
@input1 Varchar(5),
@input2 Varchar(5)
Then you use those variables as part of the where, or anywhere, in your SP. The key to the solution, is converting the input variables to local variables within the SP.
DECLARE @LocalInput1 Varchar(5),
DECLARE @LocalInput2 Varchar(5)
SET @LocalInput1 = @input1
SET @LocalInput2 = @input2
and then using the local variables everywhere in your SP.
By making this simple change to the SP I have had a report that took over 10 minutes to render, change to about 3 seconds.
Hope this helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply