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 |
|
bmassey
Starting Member
22 Posts |
 Posted - 2008-05-21 : 16:13:57
|
We have an account number search that is timing out in our application. I've done some digging into the query and this is what I have found:DECLARE @AccountNumber VARCHAR(20), VARCHAR(1000)SET @AccountNumber = '7777777777%' -- Example 1: Runs in less than one secondSELECT * FROM customer WHERE accountnumber LIKE '7777777777%'-- Example 2: Runs in less than one secondSET @sql = 'SELECT * FROM customer WHERE accountnumber LIKE ''' + @AccountNumber + ''''EXEC(@sql)-- Example 3: Takes almost two minutes to runSELECT * FROM customer WHERE accountnumber LIKE @AccountNumber Example 3 is a scaled down version of our stored proc search. The account number is passed through an input parameter and than a '%' is appended to the end of the account number to make it a wild card search. However, I don't understand why the first two examples run fine but the last example takes forever to run. Any help would be much appreciated. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-21 : 16:18:28
|
Seems like you been hit by a Parameter Sniffing.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
|
|
|
Need help with LIKE query using a variable