Re: oracle database cache with IIS application

i'm not exactly sure what you are asking, but there is definitely a difference between the two queries... the first one uses a literal value, the second uses a bind variable in a prepared statement.

the use of bind variables is usually preferrable. this is because, as your dba explained, SQL reuse is a good thing. so without going into all of the gory details about the shared pool, the SQL area, hard parses, and so on... suffice it to say, SQL reuse can reduce (sometimes significantly) the database resources used by an application.

i have very rarely found situations where using literal values is preferrable to using bind variables. (e.g. highly skewed data)

i hesitate to mention that there is a new feature in 8i which reduces the negative impact on performance of not using bind variables, by (apparently) substituting a new SQL statement for the original SQL statement. the new SQL statement replaces the literal values with... you guessed it... bind variables.

"Jean" <ken_jean_at_hotmail.com> wrote in message news:9f2f71$51i$1_at_serv1.iunet.it...
> Hi to all!
> I'm developing a web application based on IIS 5.
> I have more asp pages that retrieve data from an Oracle 8i database.
>
> I developed my ADO code using some recordset and building dinamic queries.
> For example a query may be
> strSQL = "SELECT * FROM tblCustomers WHERE ID=" & id
> rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
>
> My db administrator told me that this method cause a cache block because
 it
> must run a new query for every call and can't execute query from cache.
> So he told me that I must use another method for access data.
>
> For example I must change my query so:
>
> strSQL = "SELECT * FROM tblCustomers WHERE ID=?"
> set cmd=Server.CreateObject("ADODB.Command")
> set cmd.ActiveConnection = cn
> cmd.CommandType = adCmdText
> cmd.CommandText = strSQL
> set rs = cmd.Execute
>
> Now, I say that this method can improve performance beacuse db server can
> execute query calling it from cache.
> But, is possible that first method can cause cache block?
> Normally, db cache queries for a faster call than to execute a new query
> that there isn't in cache.
> But if I call a query no cached, cache may delete last used query and
 insert
> new query in cache. If this process work properly cache may be always with
> same size and db server may haven't problems.
> Can anyone tell me what is wright solution for my problem?
> Thanks in advance.
> Bye
>
>
>
>
Received on Wed May 30 2001 - 20:01:00 CDT