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 |
|
mmkrishna1919
Yak Posting Veteran
95 Posts |
 Posted - 2012-08-21 : 06:54:42
|
| Hi All,in my query i have a crieteria like select all records with description column having "basic" word.i have nearly 1000 distinct descriptions are there in my table.Out of 1000 distinct descriptions i had 12 different descriptions with word "basic" like "basic1","basic2"..."basci12"i had written my where clause as below:where description in('basic1','basic2',.....,'basic12') i mentioned All distinct 12 "basic[1-12]" values in my IN clause.And we have another alternative with like clauseWhere description like 'basic%'Total number of records in my table are nearly 70000plz advise which method is good in performance wise?M.MURALI kRISHNA |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-08-21 : 07:02:21
|
| You can run both queries with the execution plan turned on (Query -> Included Actual Execution plan). When you do that, in the results area there will be another pane that shows the execution plan. The execution plan will have two sections, and each section will show the the relative cost. The one with the lower relative cost is more efficient.If you have an index on the description column, the LIKE clause is probably faster. If you don't they would likely perform the same. |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-08-21 : 07:02:59
|
| I would go for the like as it is easier to code and understand.You will probably want to change it later anyway.Rather than the in clause I would probably populate a table variable.It sounds like you are using part of a column to define a category - i.e. this is really a compound value basic + suffix which is a bad idea and will cause problems later.As to performance - depends on the distribution and indexing - test it and see. I would go for the like statement whatever given this data but would prefer to restructure.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-21 : 10:17:53
|
quote:
Originally posted by mmkrishna1919
Hi All,in my query i have a crieteria like select all records with description column having "basic" word.i have nearly 1000 distinct descriptions are there in my table.Out of 1000 distinct descriptions i had 12 different descriptions with word "basic" like "basic1","basic2"..."basci12"i had written my where clause as below:where description in('basic1','basic2',.....,'basic12') i mentioned All distinct 12 "basic[1-12]" values in my IN clause.And we have another alternative with like clauseWhere description like 'basic%'Total number of records in my table are nearly 70000plz advise which method is good in performance wise?M.MURALI kRISHNA
one problem in using condition below is it will make search more broader. so tomorrow if you've item like 'basic necessity...' even that will be returned which I'm not sure is what you desire. so if you're stricting after basic followed by numeric you may be better of using basic[1-12] etc. for performance as suggested you need to evaluate various options and see which one works great for youWhere description like 'basic%'------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
LIKE vs IN clause which is best Performance wise