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