Glen,

what about running the SQL profiler, picking up the SQL statment and run it from the QA ?

Walter,


>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