Recompiling Stored Procs

From: Rob Poyntz (rpoyntz_at_vaneyk.com)
Date: 02/28/05 

Date: Mon, 28 Feb 2005 15:10:41 +1100

Hi all,

Have coded a stored proc in SQLServer2K which runs fine in Query Analyser,
taking approx 0.5 seconds to run. However, when calling the stored proc from
my Delphi app, it takes approx three seconds. Further investigation using
Performance Monitor (looking at SQL:General statistics / SQL Compilations
per second), it seems that the running from Delphi causes the stored proc to
be recompiled each time, whilst from Query Analyser it compiles once (if
needed), then uses the stored execution plan from the cache, as would be
expected.

Does anyone know why recompilation is caused by calling the stored proc from
Delphi each time, and how I can prevent it?

Many thanks in advance,
Rob

rpoyntz@vaneyk.com.au

Relevant Pages

  • Re: SP vs. SELECT Statement
    ... Is the Stored proc only being used by the form you are currently calling it ... Alex White MCDBA MCSE ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Adapter.DeleteCommand.ExecuteNonQuery returns -1 but it works
    ... No warnings/errors when executed from the studio. ... If it is -1 then you need to post the stored proc code. ... you might be calling the proc with the wrong type of execution call from ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Very Simple Stored Proc with parameter problem when called from simple .net app
    ... > WineNCheese writes: ... >> calling the stored proc from SQL Query Analyzer works fine. ... > Also, with SqlClient, you should call the parameter @testparam. ...
    (microsoft.public.sqlserver.programming)
  • Re: Error Message...
    ... This is one of those error messages that means what it ... from does not have rights to execute the stored proc. ... >> I am calling the below SQL statement as one of the SQL ...
    (microsoft.public.cert.exam.mcad)
  • Re: Stored procedure tuning
    ... I think that calling another stored proc could be considered as a last ... Is there any other possible way of problem resolution? ... >> Within that IF clause there is an UPDATE statement. ...
    (microsoft.public.sqlserver.programming)