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 |
|
M.E.
Aged Yak Warrior
539 Posts |
 Posted - 2002-10-22 : 14:50:18
|
| Select blah blah blah from table inner join 3 other tablesWHERE col1 = 5 and col3 = 'pic'concentrating on the where statement here. Above is what I want however col3 contains the following entreespicpic and coverpic/loglogmappingI need all the ones that contain pics. therefore pic, pic and cover, and pic/log. How should this where statement be wordedwhere col1 = 5 and (col3 = 'pic' or col3 = 'pic and cover' or col3 = 'log/pic')Or choice Bwhere col1 = 5 and col3 like '%pic%'this is a huge table and optimization is key.... Any ideas on what would be faster and why?-----------------------SQL isn't just a hobby, It's an addiction |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-22 : 14:55:20
|
| If there are only 3 possible "pic" values, like the ones you listed here, then use the OR logic, it can utilize an index. The LIKE condition you have can't because of the leading wildcard. However, if the "pic" always starts at the very beginning, then LIKE could use an index with a "pic%" pattern. |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-22 : 15:12:49
|
| Unfortunately theres one at the start and one at the end. I only have 3 so the or clause did seem best. Curiousity... When does the or clause become worse than the like... when thes 5 or's? 10? 1000?-----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-22 : 15:56:15
|
| There's no hard-and-fast rule about where the trade-off occurs. If you have a very wide table, and the column you're searching is indexed and fairly selective, then it will probably always be better to use the OR syntax, or better, insert the values you want into a table and JOIN that to the table you're searching. The index would most likely be used in that scenario.If it's a narrow table (only a few columns) then the index may not be as efficient as a table scan. And depending on the hardware, disk setup, RAM, etc...you get the picture. The only way you can know for sure is to test, test, test. |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-10-22 : 16:58:30
|
| Well, most I was able was to test up to 15 or statements... It was still faster (about 33% faster) than the like statement (preformed on a table of 1.9 million records). I think the join idea is more clear and easier to alter in the future (had another 'this pic/log' or something like that came up in the future and needed to be added)note to self... like sucks-----------------------SQL isn't just a hobby, It's an addiction |
|
|
|
|
|
|
|
|
Where clause optimization