BorlandTalk.com

Rob Poyntz · Guest

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

[email]rpoyntz (AT) vaneyk (DOT) com.au[/email]

Moacir Alves Schmidt · Guest

Hi, Rob.

This is a very very strange behavior and unfortunately completely
TRUE! We have no idea about what execution play will be used by the
stored procedure when it is called from delphi! Several times I
executed a stored procedure from query analyzer and it works very well
but when i try from delphi....bleargh! terrible slowly...

The solution(?) I am using is simply put some optimizer hints into
sp's queries to force a known good execution plan (collected the
indexes from query analyzer). I know this is annoying but...

If someday someone find a solution to put some intelligente into
delphi behavior please publish here! I would really appreciate that!

Regards!

<Moacir>

Barak zabari · Guest

You probably did this but did you set
Prepered :=true; ?

Barak zabari

"Rob Poyntz" <rpoyntz (AT) vaneyk (DOT) com> wrote