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 ««12

SSRS 2012 Report Issue On Server Expand / Collapse
Author
Message
Posted Friday, September 6, 2013 10:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:23 AM
Points: 42, Visits: 280
robert.gerald.taylor (9/6/2013)
nick947 (8/21/2013)

Do you know any other tricks to get past parameter sniffing?

Nick, here's a link explaining parameter sniffing and how to work around it:
http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices-150-Parameter-Sniffing.htm

It is probably worth trying to change your query so that you assign the parameters to internally defined variables and then use those internal variables in the rest of your query (as opposed to using the parameters directly).

HTH,
Rob


Hi Nick,

I have had to do what Rob suggested with the internal parameters. That worked for us.

So you have parameters passed to the stored procedure, then you declare variables at the top of the stored procedure and assign the parameter values to them, and use the local variable in the code...

example:

Alter Procedure dbo.My_Stored_Proc (@Var1 int, @Var2 varchar(50))

AS

Declare @Local_Var1 INT, @Local_Var2 varchar(50)

Set @Local_Var1 = @Var1
Set @Local_Var2 = @Var2

/****
code block
*****/
WHERE database_field1 = @Local_Var1
and database_field2 = @Local_Var2

Hope that helps,
Sarah
Post #1492316
Posted Friday, September 6, 2013 10:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 5:53 AM
Points: 20, Visits: 417
mysorian (9/5/2013)
This is just an experiment I want you to try.

Save your report to the file system. Pick up the report in Report Builder. Now deploy it to the server. Do you see the same thing? I am not suggesting this will solve your problem, but just take a look.

I have run into similar problem recently. I am suspecting the database drivers. Have a look here,

http://jayaramkrishnaswamy.sys-con.com/node/2777798

Make sure you look up the last link on the above article.


I have no idea why this worked, but it did. My execution time went from 15 minutes + to 15 seconds. I am not sure why but I am fine with the outcome. Thanks for this off the wall idea.

Nick
Post #1492343
Posted Friday, September 6, 2013 12:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:23 AM
Points: 42, Visits: 280
What does it mean to "pick up the report in Report Builder"?

Does that mean open the .rdl file in Report Builder?

Thanks for the clarification,
Sarah
Post #1492367
Posted Friday, September 6, 2013 12:41 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 1,184, Visits: 1,219
nick947 (9/6/2013)

I have no idea why this worked, but it did. My execution time went from 15 minutes + to 15 seconds. I am not sure why but I am fine with the outcome. Thanks for this off the wall idea.

I'm glad that did the trick for you; I've had a similar experience recently.

Rob
Post #1492371
Posted Friday, September 6, 2013 1:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 5:53 AM
Points: 20, Visits: 417
sarah.sickinger (9/6/2013)
What does it mean to "pick up the report in Report Builder"?

Does that mean open the .rdl file in Report Builder?

Thanks for the clarification,
Sarah


I pulled the .rdl out of my solution and save it to my desktop. Then I opened it in RB 3.0 (which was a very slow experience). I tried to publish through RB but that was not what I wanted, so I made sure I could run it in RB (I had to change the shared dataset). Finally, I used the upload feature in the folder I wanted and ran the report.


Nick
Post #1492398
Posted Friday, September 6, 2013 2:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 5:39 PM
Points: 40, Visits: 79
After running in RB 3.0 did you save it to the Report Server and then run the report on the Report Server or Report Manager?
Post #1492409
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse