Re: Dynamic SQL Performance Question

From: <michael_bialik_at_my-deja.com>
Date: 2000/04/14
Message-ID: <8d7ivc$nra$1@nnrp1.deja.com>#1/1

Don't be so sure about it.
 Stored procs are usually better when dealing with very limited number  of search criteria, but suppose you have following situation ( very  simplified one ).

 You have table Tab0001 with fields FLD001, FLD002, ... FLD020 and  you would like to enable search by ANY combination of these field.  Your SQL probably is going to look something like that:

 SELECT * FROM tab0001
 WHERE

  ( FLD0001 = :parm1 OR :parm1 IS NULL ) AND
  ( FLD0002 = :parm2 OR :parm2 IS NULL ) AND
  ( FLD0003 = :parm3 OR :parm3 IS NULL ) AND
  ( FLD0004 = :parm4 OR :parm4 IS NULL ) AND ...

 ( you got the idea ).

 The OR's are completely confuse optimizer, so FULL TABLE scan  access will be used.

 HTH. Michael.

In article <8d7eok$iq1$1_at_nnrp1.deja.com>,   billmil_at_my-deja.com wrote:
>
> > the elapsed/cpu time for executing dynamic sql is taken up in the
> parsing phases.
>
> Thanks for the input, both of you. We're going ahead and replacing all
> dynamic sql with stored procedures. It seems that the performance
> benefit from stored procedures outweighs the (one time) cost of
> developing them.
>
> bill milbratz
> chicago
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 14 2000 - 00:00:00 CDT