Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
darkdusky
Aged Yak Warrior
591 Posts |
 Posted - 2008-10-17 : 11:05:24
|
| Hi,I have to add a filter inside stored procedure. This filter is an upper and lower integer range which will be input in Reporting Services.My problem is this has slowed procedure down.Section of procedure to be filtered:select * from [dbo].[fnFunctionName] ('0708')where Capacity >98 and Capacity <=120 I need to use variables instead of above.I first tested as stand-alond query with declared variables and I also placed amended query in its own stored procedure as I read Optimiser can handle variables quicker when passed rather than declared in query.CREATE PROCEDURE [dbo].[my_procedure] (@MinCapacity INT, @MaxCapacity INT) ASBeginselect * from [dbo].[fnFunctionName] ('0708')where Capacity >@MinCapacity and Capacity <=@MaxCapacityENDRunning Times:numbers hard-coded - < 1secvariables declared in stand alone query - 5 secsvariables passed through procedure - 6 secs. The numbers returned are quite small (approx 400) but the Function is querying a large number of tables and performing calculations etc. With-out filter or with hard-coded filter results are returned instantly.Any alternatives? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-17 : 11:09:20
|
| Seems like it might be due to Parameter sniffing. Can you just try using some varaibles inside procedure which just takes values from parameters and use it instead of parameters in filter condition and see if its of any good? |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-10-17 : 11:48:34
|
| How common is this?There seems to be a bunch of such advice lately that seems to fix the posters issues.Has it always been this way or is the sniffing a 2005 problem?-------------Charlie |
|
|
|
|
|
|
[Solved] Variable makes query run slowly