Re: Executing a Stored Procedure via Query Analysier is faster then a

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Simon Shearn (simon_at_antispam.aardvarks)
Date: 12/10/04 

Date: Fri, 10 Dec 2004 04:50:31 -0000

"David Hekimian" <David Hekimian@discussions.microsoft.com> wrote in message
news:F71CDFA4-1822-410B-9F4A-696C6BE4D7F3@microsoft.com...
> I have a complicated Stored Procedure that when run as a Scheduled Job
takes
> 9 hours to complete. When I run the same Stored Procedure in Query
Analysier,
> it takes 3 hours.
>
> The box is a Quad Xeon 3.0 Ghz w/E64MT and 8 GB of ram attached to a IBM
> FiberChannel SAN. It is part of a Windows 2003 / SQL 2000 Cluster.
>
> I run the job every night at the same time, thus the server load should be
> comparable.
>
> Is there a fundamental difference in how Query Analyser executes a Stored
> Procedure and how a SQL Job is executed?
>
> This Stored Proc uses Temp Tables and Indexes heavily.
>
> We've reindexed the database, recompiled the stored procs and truncated
log
> tables. The times are still wildly different between the two execution
types.
>
> I just don't know where to start / continue troubleshooting.
>
> Please Advise.
>
> - David

The SP may be running as different users in Query Analyser and as a Job, but
I've never seen this affect performance.

Can you reproduce the time differential by running the SP against a reduced
data set? - it's a bit difficult to debug issues like this when each test
takes 9 hours.

Regards,

Simon

Relevant Pages

  • Re: Executing a Stored Procedure via Query Analysier is faster then a
    ... Does the stored procedure have SET NOCOUNT ON at the beginning? ... "David Hekimian" wrote in message ... > Is there a fundamental difference in how Query Analyser executes a Stored ...
    (microsoft.public.sqlserver.server)
  • SqlDataAdapter.Fill returns results of previous command
    ... This code executes successfully at certain points in the application, ... I don't know what the cause of this exception is yet, ... stored procedure was indeed executed on the database. ... The trace shows that the last action on the db was the stored ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Help Isolating Data Access
    ... > I've set up a stored procedure that first queries the table for a record ... > The process that executes this stored procedure runs on multiple threads ... Is there a design pattern for ...
    (microsoft.public.dotnet.general)
  • Re: Tracing user & permissions (triggers and stored p)
    ... To solve my problem I chosed to configure the SQL ... > from xp_cmdshell executes under the security context of the SQL Server ... >> sql Trigger executes a Stored Procedure passing it some variables. ...
    (microsoft.public.sqlserver.security)
  • Re: Status Column in sysobjects table...SQL2000
    ... I mean when the same piece of code i.e. the stored procedure is compiled ... in the query analyser compiled manner. ... "Dan Guzman" wrote: ... >> I am not sure if there is any way to get this undocumented information. ...
    (microsoft.public.sqlserver.programming)