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 |
|
koosmare
Starting Member
1 Post |
 Posted - 2004-09-01 : 05:12:12
|
| Hi, I have a table with 30 000 000 records in it. The table was backed up from one server to another. The query 'select * from address where line1 like '%water%' executed on the fist server in 20 seconds, but on the second server it takes 7 minutes. when I create a store procedure containing the same quesry it executes in 20 seconds. I also manage to establish with the index tunig wizard that the store procedure uses a index, but the query does not and it does not recomend any new indexes. Thanks |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-09-01 : 07:31:31
|
| Any code with LIKE '%water%' will not use any indices....there's no point...the leading % means that there could be an infinite number of characters before 'water'....a bit like searching a phone-book with the word 'john'....you have to read every entry....On the 1st server ....you may be benefiting from CACHE from previous runs....search here for information on same.especially the use of DBCC FREEPROCCACHE...in order to get run-independent results when performance-testing. |
 |
|
|
|
|
|
Performance with like in query