Re: Binding vs. not binding in dynamic sql.
Date: 1995/04/11
Message-ID: <690195143wnr_at_lecky.demon.co.uk>#1/1
In article: <3ma2eu$7su_at_crl.crl.com> ward_at_crl.com (Ward Mullins) writes:
>
> I was wondering if anyone has done any actual benchmarking on the
> performance hit of not binding variables in a dynamic sql (i.e. using an
> sprintf function to fill in data explicitly) vs binding variables for the
> sql. I would imagine there is a parse overhead, and that oracle does
> some sort of caching of previously executed sql statements which would
> impact the performance, but I don't have a real feel for how much impact
> this is. I would appreciated hearing from anyone who has benchmarked
> this, or might have some insight for me.
>
> Thanks,
> ward
>
The performance hit is, as you suspect, in parsing. All SQL is cached after
parsing in the SGA, but can only be reused if the SQL text is *identical* in later
statements. Bind variables make the statement more likely to be reusable and
reduce the work of the RDBMS on subsequent parses, but literal values reduce
network traffic between client and server. If the statement is likely to be reused
extensively - go for bind variables, if not, then use literal values.
-- --------------------------------------------------------------------------- | Keith Bremer EMail kbremer_at_lecky.demon.co.uk | ---------------------------------------------------------------------------Received on Tue Apr 11 1995 - 00:00:00 CEST