|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Best Practices - using params passed to stored procedure
practices Question came to my mind because of parameter sniffing but a) use passed in stored procs parameters directly in your WHERE clause CREATE PROCEDURE uspGetDATA @param1 INT ,@param2 INT ,@param3 varchar(10) AS BEGIN SELECT col1, col2, col3 FROM table1 t1 INNER JOIN table2 t2 ON t1.ID = t2.ID WHERE t1.col1 = @param1 AND t1.col2 = @param2 AND t2.col3 LIKE ‘%@param3’ END b) use local variables and use them in your where clause, may be it will help alleviate parameter sniffing but this may not be a good idea sometimes you will not get better execution plan CREATE PROCEDURE uspGetDATA @param1 INT ,@param2 INT ,@param3 varchar(10) AS BEGIN DECLARE @Var1 INT, @var2 INT DECLARE @var3 varchar(10) SET @var1 = @param1 SET @Var2 = @Param2 SET @var3 = @Param3 SELECT col1, col2, col3 FROM table1 t1 INNER JOIN table2 t2 ON t1.ID = t2.ID WHERE t1.col1 = @var1 AND t1.col2 = @Var2 AND t2.col3 LIKE ‘%@var3’ END I just realized this should be posted in "SQL Sever programming" instead of
here.... > but this may not be a good idea sometimes By that, I think you have answered your own question. That is, there is no > you will not get better execution plan one-size-fit-all best practice recommendation here. In a given case, if one way works fine, stick to it. Linchi Show quoteHide quote "css" wrote: > Which method is preferred, I am asking this question in terms of best > practices > > Question came to my mind because of parameter sniffing but > > a) use passed in stored procs parameters directly in your WHERE clause > > CREATE PROCEDURE uspGetDATA > @param1 INT > ,@param2 INT > ,@param3 varchar(10) > AS > BEGIN > > SELECT col1, col2, col3 > FROM table1 t1 INNER JOIN table2 t2 ON t1.ID = t2.ID > WHERE t1.col1 = @param1 AND t1.col2 = @param2 AND t2.col3 LIKE ‘%@param3’ > > END > > > b) use local variables and use them in your where clause, may be it will > help alleviate parameter sniffing but this may not be a good idea sometimes > you will not get better execution plan > > > CREATE PROCEDURE uspGetDATA > @param1 INT > ,@param2 INT > ,@param3 varchar(10) > AS > BEGIN > > DECLARE @Var1 INT, @var2 INT > DECLARE @var3 varchar(10) > > SET @var1 = @param1 > SET @Var2 = @Param2 > SET @var3 = @Param3 > > SELECT col1, col2, col3 > FROM table1 t1 INNER JOIN table2 t2 ON t1.ID = t2.ID > WHERE t1.col1 = @var1 AND t1.col2 = @Var2 AND t2.col3 LIKE ‘%@var3’ > > END > > Most of the stored procedures I have programmed uses approach a)
---to use passed params directly in the where clause. I have understanding of parameter sniffing *in theory*, but I came to know about it fairly recently and besides any other circumstances in which I should use appraoch b) instead of directly using passed params. Thanks in advance. Parameter sniffing in general is a good thing, so you should not try to avoid it. Plus when you use local variables you
effectively disable parameter sniffing and the generated plan may or may not be efficient. If you detect parameter sniffing problem then you can think of how to solve it. Depending on the SQL Server version you have there could be more effective methods (like using OPTION RECOMPILE on SQL Server 2005/2008). >>Parameter sniffing in general is a good thing, so you should not try to OK Thanks for clarifying that.avoid it Well said, well said. For whatever reason, there seems to be an impression
out there that parameter sniffing is associated with bad query plans. Linchi Show quoteHide quote "Plamen Ratchev" wrote: > Parameter sniffing in general is a good thing, so you should not try to avoid it. Plus when you use local variables you > effectively disable parameter sniffing and the generated plan may or may not be efficient. If you detect parameter > sniffing problem then you can think of how to solve it. Depending on the SQL Server version you have there could be more > effective methods (like using OPTION RECOMPILE on SQL Server 2005/2008). > > -- > Plamen Ratchev > http://www.SQLStudio.com > Parameter Sniffing is only a problem with odd distributions.
Think of a table with at least 500K records that allows duplicate non-primary keys. Most duplicates have 2-10 peers. Then there is one set with 50,000 keys. There really needs to be two query plans, but SQL only supports one per procedure (ignoring the parallel and non-parallel versions of the plan). Parameter sniffing only exacerbates the problem, it is the bad distribution that causes it. -- Show quoteHide quoteGeoff N. Hiten Principal SQL Infrastructure Consultant Microsoft SQL Server MVP "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message news:23366DAA-6954-42F0-8F4B-22D93BB434BB@microsoft.com... > Well said, well said. For whatever reason, there seems to be an impression > out there that parameter sniffing is associated with bad query plans. > > Linchi > > "Plamen Ratchev" wrote: > >> Parameter sniffing in general is a good thing, so you should not try to >> avoid it. Plus when you use local variables you >> effectively disable parameter sniffing and the generated plan may or may >> not be efficient. If you detect parameter >> sniffing problem then you can think of how to solve it. Depending on the >> SQL Server version you have there could be more >> effective methods (like using OPTION RECOMPILE on SQL Server 2005/2008). >> >> -- >> Plamen Ratchev >> http://www.SQLStudio.com >> Geoff N. Hiten (SQLCrafts***@gmail.com) writes:
> Parameter Sniffing is only a problem with odd distributions. There are other situations as well where parameter sniffing can bite you.> > Think of a table with at least 500K records that allows duplicate > non-primary keys. Most duplicates have 2-10 peers. Then there is one > set with 50,000 keys. There really needs to be two query plans, but SQL > only supports one per procedure (ignoring the parallel and non-parallel > versions of the plan). > > Parameter sniffing only exacerbates the problem, it is the bad > distribution that causes it. Assume that during the night there is a lot of reindexing that largely flushes the plan cache. There is some process that keeps in-memory version of table(s). In the morning it makes a total refresh. During the day, it periodically reads deltas. The procedure has a where clause that goes: WHERE id > @startid where @startid is the parameter. The first read in the morning will set the plan, and then the procedure will scan all day... -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx We've only had trouble with "sniffing" when there were mutually exclusive
optional parameters. So, if @param1 is passed then @param2 would be null, and vice versa. Depending in which paramter was not null, a certain query block would be executed; basically one of two quesies is executed based on the pramter passed in. So, at compile time, one of the two queries gets optimized for null, when null is really NEVER an actual value that will be used in the where clause. I don't condone this type of design in a procedure, but it's what we were faced with, and we had horrible issues with bad plans, subsequent table scans, blocking, and pissed-off users. To fix the problem, in certain procedures index hints were used, and in another procedure OPTION RECOMPILE. I just share this since sniffing usually isn't a problem, but scenarios like I describe should raise a red flag and be watched. James 1) OR type queries like this are a disaster for the optimizer in general
2) I find that a much more common place where plan caching will cause trouble is where data value distribution is non-uniform, leading to situations where one parameter will hit 2 rows and another will hit 10% of the 5M row table. If you know your data is skewed like this you would do well to plan on parameter-sniffing workarounds from the start to avoid the pain that is sure to follow if you don't. -- Show quoteHide quoteKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "James Hunter Ross" <james.r***@oneilsoft.com> wrote in message news:%23NNOuKz$JHA.2120@TK2MSFTNGP02.phx.gbl... > > We've only had trouble with "sniffing" when there were mutually exclusive > optional parameters. So, if @param1 is passed then @param2 would be null, > and vice versa. Depending in which paramter was not null, a certain query > block would be executed; basically one of two quesies is executed based on > the pramter passed in. So, at compile time, one of the two queries gets > optimized for null, when null is really NEVER an actual value that will be > used in the where clause. > > I don't condone this type of design in a procedure, but it's what we were > faced with, and we had horrible issues with bad plans, subsequent table > scans, blocking, and pissed-off users. To fix the problem, in certain > procedures index hints were used, and in another procedure OPTION > RECOMPILE. > > I just share this since sniffing usually isn't a problem, but scenarios > like I describe should raise a red flag and be watched. > > James >  
Other interesting topics
Locks and SQLAgent - Generic refresher, Alert Engine
Connection server utilizing trusted connection to other SQL Server RESTORING drop table not showing up in the transaction log deleting DB strange BLOB beahaviour AWE on SQL Server2005 'Generate scripts' functionality in SQL 2005 not being consistent Problem with SUM - Help please Backups & Transaction Files |
|||||||||||||||||||||||
