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 |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2004-08-11 : 03:13:28
|
| Hi,I have a query that scans a 3 diff table of about 500,000 rows each. It scans a varchar(500) field for certain words and deletes those rows. I run this about once per night.I run it with the clause - " LIKE '%BADWORD%' "I run this about 20 times, for each word. Right now its taking about 9 minutes to run.My knowledge on execution plans are pretty beginner. Do you think it would be better to have 20 clauses and run just once? If its just 15 - 30 seconds I'm looking at shaving im not too worried, but if I could cut it in half or more then I'm interested.I realize its impossible to tell, but just asking for your educated guesses and recommendations.Thanks alot,mike123 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-08-11 : 05:05:05
|
| WHERE (xyzcolumn LIKE '%BADWORD1%) or (xyzcolumn LIKE '%BADWORD2%) or (xyzcolumn LIKE '%BADWORD3%)should allow you to do it all in one pass.....reducing your overall run down to 9mins/20.the major contributor to the poor performance is the leading '%'. this stops the query using any potential index on the xyzcolumn. IF ALL the badwords WERE ALWAYS to be the 1st word in the xyzcolumn then removing the 1st '%' should be an improvement...(provided the xyzcolumn is the 1st column in an index) |
 |
|
|
|
|
|
query speed question