Re: db cache with IIS application

Your DBA is right.

If you use

strSQL = "SELECT * FROM tblCustomers WHERE ID=" & id

then Oracle will have to reparse this query every single time. (assuming id has a different value in each case).

If you use bind variables , which the second syntax is doing* you will only parse once.

--
Niall Litchfield
Oracle DBA
Audit Commission UK

* although I don't see any population of the bind variable with its actual
data.

"Jean" <ken_jean_at_hotmail.com> wrote in message
news:9f2f8a$5cu$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

>

>

>

>

>

>