Re: question about ADO and Oracle

Cliff,

Thanks for your reply. The two articles you cite from MS, we have already seen. I have passed them on to the developers.

Two things I find particularly confusing.

First is the quote from the ADO docs -- "CacheSize is based on the MaximumOpenRows provider-specific property (in the Properties collection of the Recordset object)" in that we can find NOTHING on MaximumOpenRows.

I assumed that Plus took its ArraySize and translated that to some other call at the API level -- a call that would also be available to other clients (like ADO). I thougth perhaps this obscure reference to MaximumOpenRows might be pointing to the same thing.

As I try to clarify my thinking (not to mention clarifying my question!) I see the following picture. When the Oracle server process gets a query from a client (the client could be SQL*Plus, or it could be ADO acting on behalf of an application pgm) it develops a result set, then feeds the rows back to the client in SQLNet packets that are sized according to a specification provided by the client. If the client is ADO, it (ADO) then feeds the result set back to *his* requestor (the application pgm, in VB, C, Cobol, etc.) in buffers of "n" number of rows at a time. If this picture is accurate, it would appear that CachSize specifies the size of the buffer between ADO and the application, but has no impact on the "buffer" (TNS packets) between ADO and the database server, whereas SQL*Plus uses its ArraySize to set the size of the TNS packets. In fact, my test results are consistent with this picture, but not necessarily proof. If my picture *is* accurate, then what I'm looking for is the key to specifying the size of the TNS packets between the Oracle server and ADO.

I would be happy to have my entire understanding shot out of the water if it would help solve my problem.
- Ed

On Tue, 8 Jan 2002 12:00:51 -0500, "Cliff Dabrowski" <cliffd.ng at bigfoot.com> wrote:

>Hello Ed,
>
>Your tests/findings with SQL*Plus are not directly applicable to what you
>need to do. SQL*Plus is developed on top of OCI (Oracle Call Interface) and
>an additional Oracle proprietary API called UPI. The "SET ARRAY" command
>simply sets some internal variables that are then used to determine rowset
>retrieves during the fetch using the appropriate OCI/UPI API call.
>
>Your assertion about the CacheSize parameter in ADO is correct and I have
>found a few references for you in case you have not already seen them. I
>would be interested to hear about your findings and final resolution on this
>issue.
>
>http://www.microsoft.com/data/impperf.htm
>An excerpt:
>2.8 Tune your Recordset CacheSizeproperty
>ADO uses the Recordset CacheSize property to determine the number of rows to
>fetch and cache. While you are within the range of cached rows, ADO just
>returns data from the cache. When you scroll out of the range of cached
>rows, ADO releases the cache and fetches the next CacheSize rows. So what
>CacheSize should you use in your application? Unfortunately, there isn't a
>single optimal CacheSize that is appropriate for all applications. You
>should tune your application with different CacheSize values and use the one
>that offers you the best performance. Using a small CacheSize value
>significantly improves performance for fetching data from an Oracle data
>store
>
>A little more up to date:
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html
>/improvperf.asp
>
>And...
>http://www.devx.com/premier/mgznarch/vbpj/2000/04apr00/df0400/df0400.asp
>Finally, you can use the CacheSize and Batch Size properties of the
>Recordset object. Use CacheSize to determine how many rows ADO stores on the
>client and how many rows it fetches with each trip to the data source. As
>you scroll through the recordset, the cache is refreshed with rows from the
>OLE DB provider. You'll certainly gain better performance by tuning this
>property appropriately as the default CacheSize is 1. Unfortunately, no rule
>of thumb exists for setting the CacheSize property, so you'll need to
>experiment with your application. It's especially important to tune this
>property for applications using server-side cursors, because it affects the
>number of round-trips to the data source.
>You use the Batch Size property to determine how many rows ADO includes in
>each round-trip to the server when performing a batch update using the
>UpdateBatch method on a disconnected Recordset object. To set dynamic
>properties, use the Properties collection of the Recordset object with its
>name as a literal string:
>
>Just one more...
>http://www.4guysfromrolla.com/webtech/053100-1.shtml
>
>Hope this helps,
>
>~Cliff
>
>
>"Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message
>news:3c39d1ab.14432322_at_ausnews.austin.ibm.com...
>> Our tuning efforts have lead us to looking at SQL*net issues. I ran a
>series of
>> tests from SQL*Plus, varyinig both the SDU/TDU values and the array size.
>>
>> My method of testing was to run a fixed set of queries (taken from a
>running
>> production instance) with client trace set to 16, then counting the number
>of
>> packets received by the client. I would consider a reduction in the
>number of
>> packets received to indicate less network traffic, less re-assembling of
>> packets, and thus indicative of some degree of performance improvement.
>>
>> The results of these tests showed that playing with SDU/TDU resulted in a
>small
>> decrease in packets received, but increasing array size made a significant
>> reduction in packets received.
>>
>> Now, the real problem. For these tests, setting array size was a function
>of
>> the SQL*Plus session and is set by a Plus SET command. So, how to
>translate
>> that to something the application can use? The app is written with MS
>tools and
>> uses ADO as the microsoft layer accessing the Oracle DB. We have looked
>all
>> through the ADO API reference and the closest we come is a discussion of a
>> CacheSize property. Within that discussion was this statement:
>>
>> "CacheSize is based on the MaximumOpenRows provider-specific property (in
>the
>> Properties collection of the Recordset object).
>>
>> This looks like MaximumOpenRows is what I'm after, but I can find no other
>> reference anywhere in the reference manual or anywhere on Technet. Does
>anyone
>> here have any experience with this, to point me in the right direction>
>> --
>> Ed Stevens
>> (Opinions expressed do not necessarily represent those of my employer.)
>> --
>> Ed Stevens
>> (Opinions expressed do not necessarily represent those of my employer.)
>
>

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)