Stored procedure slow, same code in QA fast???  
Author Message
WEBBADGER





PostPosted: Thu Apr 07 11:40:47 CDT 2005 Top

SQL Server Developer >> Stored procedure slow, same code in QA fast???

I've created a stored procedure that executes horribly slow when I run it
(either from our application or from Query Analyzer)

However, when I paste the code from the stored proc into Query Analyzer, I
get the result sets back in a matter of seconds.

I've recreated the stored proc with a different name, and have tried
recompiling it, but no go.

Contents of the code are propietary and I can't paste them into here - I was
hoping someone might have general answers/clues why a stored proc would take
longer than 5 minutes to run, yet the exact same code running in QA would
consistenly run in seconds. (If necessary, I can provide dummy code that
illustrates what's going on).

Thanks,
Kevin


I

SQL Server236  
 
 
Thomas





PostPosted: Thu Apr 07 11:40:47 CDT 2005 Top

SQL Server Developer >> Stored procedure slow, same code in QA fast??? I'm assuming that when you run this through code vs. QA, you
are running against the exact same db. Have you run a SQL
Trace to determine what else is running against the db at
the time your stored proc call is made? What duration of
execution does SQL Trace give to the stored proc call? It is
possible that something other than SQL is holding up the
works.


Thomas







> I've created a stored procedure that executes horribly
> slow when I run it
> (either from our application or from Query Analyzer)
>
> However, when I paste the code from the stored proc into
> Query Analyzer, I
> get the result sets back in a matter of seconds.
>
> I've recreated the stored proc with a different name, and
> have tried
> recompiling it, but no go.
>
> Contents of the code are propietary and I can't paste them
> into here - I was
> hoping someone might have general answers/clues why a
> stored proc would take
> longer than 5 minutes to run, yet the exact same code
> running in QA would
> consistenly run in seconds. (If necessary, I can
> provide dummy code that
> illustrates what's going on).
>
> Thanks,
> Kevin
>
>
> I


 
 
AlejandroMesa





PostPosted: Thu Apr 07 11:47:11 CDT 2005 Top

SQL Server Developer >> Stored procedure slow, same code in QA fast??? See if this helps:

http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/0a6ae4020b1e3621#0ff9e6e72122e1fb


AMB



> I've created a stored procedure that executes horribly slow when I run it
> (either from our application or from Query Analyzer)
>
> However, when I paste the code from the stored proc into Query Analyzer, I
> get the result sets back in a matter of seconds.
>
> I've recreated the stored proc with a different name, and have tried
> recompiling it, but no go.
>
> Contents of the code are propietary and I can't paste them into here - I was
> hoping someone might have general answers/clues why a stored proc would take
> longer than 5 minutes to run, yet the exact same code running in QA would
> consistenly run in seconds. (If necessary, I can provide dummy code that
> illustrates what's going on).
>
> Thanks,
> Kevin
>
>
> I
>