This is easy when you sit back and think about it. Remember the steps that a batch goes through before it is executed:
1 - syntax check
2 - name resolution
3 - optimization
4 - execution
The first query contains literals which are recognized by the query optimizer. It can now use statistics to determine whether any index on the zip column, for instance, is useful. The same can be said for the other columns. OTOH, the second query does not provide any literals. There are value for the variables but their values are not known until execution. In this case, the query optimizer has to make some guesses about the selectivity of the search arguments. These
magic numbers are documented:
"=" ---> 10%
"<" or ">" ---> 30%
BETWEEN ---> 10%
(These values are taken from Inside SQL Server 2000)
In the absence of any literals to compare against, SQL Server must use the above estimates when deciding on how to build the query plan.
If you run the two queries through SHOWPLAN, I'd expect to see different query plans.
-Mike
>This returns instantly in the QA:
>
>SELECT ContactName,CompanyName,AccountId,zip,Address1,City,cpk FROM account
> WHERE zip LIKE '%55405'
> AND CompanyName LIKE '%'
> AND ContactName LIKE '%'
> AND AccountId LIKE '%'
> ORDER BY companyName
>
>
>
>But this (using declared variables) takes 18 seconds:
>
>DECLARE @tcAccountId VarChar(10)
>DECLARE @tcContactName VarChar(30)
>DECLARE @tcCompanyName VarChar(30)
>DECLARE @tcZip VarChar(10)
>
>SET @tcAccountId = '%'
>SET @tcContactName = '%'
>SET @tcCompanyName = '%'
>SET @tcZip = '55405%'
>
>SELECT ContactName,CompanyName,AccountId,zip,Address1,City,cpk FROM account
> WHERE zip LIKE @tcZip
> AND CompanyName LIKE @tcCompanyName
> AND ContactName LIKE @tcContactName
> AND AccountId LIKE @tcAccountId
> ORDER BY companyName
>