Re: question about ADO and Oracle

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.)
Received on Tue Jan 08 2002 - 11:00:51 CST