Re: Doing a query with string vs bind variables.

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 17 Sep 2007 19:04:34 -0800
Message-ID: <46ef3232$1@news.victoria.tc.ca>

Luch (DarthLuch_at_gmail.com) wrote:
: We have a PowerBuilder application where we started noticing the
: performance of a query is worse if you do it with bind variables then
: if you do the same query as a string.

Bind variables minimize the number of times a query must be parsed. That is likely to speed up your application if the same queries are used multiple times.

Hard coded values allow the optimizer to select the best possible path to select the data. That is likely to speed up your application if a query is only run once, or if the time to parse the query is small compared to the time it takes to run the query, especially if the values are unusual compared to most of the data.

Apparently more recent versions of Oracle (version?) will examine the values of bind variables before running a query as a sanity check that the existing parsed query will still be sensible for those values. I know not the exact details.

$0.10 Received on Mon Sep 17 2007 - 22:04:34 CDT