|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Like vs =Hi
Is there a major speed difference if WHERE col like 'abc%' is used against WHERE col = 'abc' in a select statement in stored procedure? Thanks Regards Ideally, if you are looking for exact matches, then avoid LIKE, and go with
=. The optimizer knows that it doesn't have to go for anything but 'abc'. The speed difference should be noticeable with larger tables. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com .. "John" <John@nospam.infovis.co.uk> wrote in message Is there a major speed difference if WHERE col like 'abc%' is used againstnews:%23FoxyhRpFHA.2976@TK2MSFTNGP12.phx.gbl... Hi WHERE col = 'abc' in a select statement in stored procedure? Thanks Regards Yes or No. It really depends. Like 'abc%' returns the same data in just
about the same time if you are talking about an unindexed set. But it gets complicated from there. Does col have a unique index? Does 10% of the table match abc%, or none? = always at least equal, but more often faster. The real question is, what do you need. All rows where col is equal to abc, or all rows where the value of col starts with abc. And don't cross post, especially to the microsoft newsgroups (since the same people are generally here and there) Just try one at a time, and give it a little time, then post to other newsgroup. Especially since this is not a server thing, it is a programming one :) -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "John" <John@nospam.infovis.co.uk> wrote in message news:%23FoxyhRpFHA.2976@TK2MSFTNGP12.phx.gbl... > Hi > > Is there a major speed difference if WHERE col like 'abc%' is used against > WHERE col = 'abc' in a select statement in stored procedure? > > Thanks > > Regards > > hi
the major difference here is, if u use = then the column is matched exactly with the string if u use like, depending on the % used, the result is returned. i.e if the string exists then the result would be true eg in your case: if u use = then only rows with column value abc is returned if u use like the rows returned would be abc abc<any text> please let me know if u have any questions best Regards, Chandra http://www.SQLResource.com/ http://chanduas.blogspot.com/ --------------------------------------- *** Sent via Developersdex http://www.developersdex.com *** John (John@nospam.infovis.co.uk) writes:
> Is there a major speed difference if WHERE col like 'abc%' is used against It can be virtually difference at all, or it can like night and day.> WHERE col = 'abc' in a select statement in stored procedure? If col is non-indexed, the only overhead is a somewhat more complex comparison operation. If col is indexed with a non-clustered index, the optimizer will have to make a decision on whether to use the index. Assume for simplicity that the index is unique. Then = will use that index, and access will be fast. But if there is LIKE there is another story. In this case, there are two possible strategies: use the index or scan the table. Scanning the table is better a large proportion of the rows start with 'abc%'. The optimizer does not know about this, but from statistics compiled about the table, it can make an estimate. If the estimate is incorrect, the plan may not be the best - something the users will very painfully notice. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp Hi
My problem is that I have three parameters and all of them optional. I can't think of avoiding writing a sub-sp for every combination of provided and not provided parameters except to use like. Is there a better to handle three parameters where any combination of them could be optional? I am using the following select in the sp; CREATE PROCEDURE [dbo].[MyProc] @Param1 varchar(50) ='', @Param2 varchar(50) ='', @Param3 varchar(50) ='' AS SET NOCOUNT ON; SELECT <fieldlist> FROM MyTable WHERE (Col1 like @Param1+ '%') AND (Col2 like @Param2+ '%') AND (Col3 like @Param3+ '%') Thanks Regards Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns96B86CFBDFA99Yazorman@127.0.0.1... > John (John@nospam.infovis.co.uk) writes: >> Is there a major speed difference if WHERE col like 'abc%' is used >> against >> WHERE col = 'abc' in a select statement in stored procedure? > > It can be virtually difference at all, or it can like night and day. > > If col is non-indexed, the only overhead is a somewhat more complex > comparison operation. > > If col is indexed with a non-clustered index, the optimizer will have > to make a decision on whether to use the index. Assume for simplicity > that the index is unique. Then = will use that index, and access will be > fast. But if there is LIKE there is another story. In this case, there > are two possible strategies: use the index or scan the table. Scanning > the table is better a large proportion of the rows start with 'abc%'. > The optimizer does not know about this, but from statistics compiled > about the table, it can make an estimate. If the estimate is incorrect, > the plan may not be the best - something the users will very painfully > notice. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp > John (John@nospam.infovis.co.uk) writes:
Show quote > My problem is that I have three parameters and all of them optional. I Aha! Again it is proven that if you don't supply the full information, > can't think of avoiding writing a sub-sp for every combination of > provided and not provided parameters except to use like. Is there a > better to handle three parameters where any combination of them could be > optional? I am using the following select in the sp; > > CREATE PROCEDURE [dbo].[MyProc] > @Param1 varchar(50) ='', > @Param2 varchar(50) ='', > @Param3 varchar(50) ='' > AS > SET NOCOUNT ON; > SELECT <fieldlist> > FROM MyTable > WHERE (Col1 like @Param1+ '%') AND (Col2 like @Param2+ '%') AND (Col3 like > @Param3+ '%') you may not get a very appropriate answer. I figured that may be the user could enter part of a name, you were wondering whether if was good to use LIKE as a matter of routine. (For that case, I would suggest to search for equality first, and for LIKE searches require at least two- three characters of input.) This is a different problem. And a more complex one, that has several solutions, and which one to pick depends on the situation. Now, I am not going to tell all this here. Instead I will refer you to an longer article on the topic: http://www.sommarskog.se/dyn-search.html. From other posts I have understood that you are looking at SQL 2005. Therefore I should add that the article is written with SQL 2000 in mind. I know of a things that I need to update. Nevertheless the article should be useful to you. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp >> Is there a better to handle three parameters where any combination of them could be optional? << Let the inputs be NULL when missing and coalesce them to empty strings:WHERE col1 LIKE COALESCE (@param1, '') + '%' AND col2 LIKE COALESCE (@param2, '') + '%' AND col3 LIKE COALESCE (@param3, '') + '%' --CELKO-- (jcelko***@earthlink.net) writes:
>>> Is there a better to handle three parameters where any combination of them could be optional? <<> I agree that NULL is better than empty strings from a conceptual point of> Let the inputs be NULL when missing and coalesce them to empty strings: > > WHERE col1 LIKE COALESCE (@param1, '') + '%' > AND col2 LIKE COALESCE (@param2, '') + '%' > AND col3 LIKE COALESCE (@param3, '') + '%' view, but performance-wise it will not make things any better. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp  |
|||||||||||||||||||||||
Other interesting topics