I have created a Stored Procedure as follows:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

ALTER  PROCEDURE findtitle
@tString varchar(40) 
with recompile
 AS
set @tString = rtrim(@tString)
select * from inv where squeezedesc like @tString

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Then I execute the SP as follows:

EXECUTE findtitle 'thehook%' with recompile

Yet it takes 38 seconds to complete. YIKES! When I run the following from the QA:

Select * From inv Where SqueezeDesc like 'thehook%'

it takes less than a blink of the eye to complete.

I am suprised, dissapointed and worried. An application feature depends on this function. I appreciate any and all help.

Thanks

Glenn



>Glen,
>
>I've seen this many times. This probably due to the optimizer reusing plans. The QA by default recompiles the execution plan while the query passed by the QD reuses a previous queryplan
>
>Look at the books online at the WITH RECOMPILE option. Create a SQL proc with recompile option as see whether this speed up things.
>
>Walter,
>
>
>
>>I have an application written in VFP which executes a query against a SQL 2000 table with approx. 3,500,000 records. this table has an index on a computed field called Squeezedesc (replace(description,' ','') ).
>>
>>When I run this query in SQL Query Analyser it is done in negligible time whereas when executed from the app it takes 38 seconds???? Here is my query:
>>

>>Select inv.uid as idinv,inv.isbn13 as sku1 From inv Where SqueezeDesc like 'thehook%'
>>

>>
>>Is this an expected behavior?
>>
>>Glenn