Re: question about ADO and Oracle

Ed,

I will do a bit more digging on your assertions. Can you provide me any test cases that you have? I can work independently and produce my own but it would make a whole lot more sense if we were using the same tests so that the results were more easily compared.

Looking for ADO sample code...
Looking for results...
Looking for trace output...

I have a way to trace ADO, OCI, UPI independent of each other and would then be looking at correlating the output/results to answer your questions.

Hth,

~Cliff

"Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message news:3c3b61fe.116900914_at_ausnews.austin.ibm.com...
> 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/htm
l
> >/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.)
Received on Tue Jan 08 2002 - 16:33:28 CST