Path: text.usenetserver.com!out02b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!o61g2000hsh.googlegroups.com!not-for-mail
From:  dean <deanbrown3d@yahoo.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Table open times and rowsize discontinuity
Date: Wed, 08 Aug 2007 14:34:38 -0700
Organization: http://groups.google.com
Lines: 84
Message-ID: <1186608878.102712.235450@o61g2000hsh.googlegroups.com>
References: <1186596097.751275.85520@k79g2000hse.googlegroups.com>
   <1186597655.62959@bubbleator.drizzle.com>
NNTP-Posting-Host: 151.204.192.226
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1186608878 13205 127.0.0.1 (8 Aug 2007 21:34:38 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 8 Aug 2007 21:34:38 +0000 (UTC)
In-Reply-To: <1186597655.62959@bubbleator.drizzle.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: o61g2000hsh.googlegroups.com; posting-host=151.204.192.226;
   posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
Xref: usenetserver.com comp.databases.oracle.misc:249169
X-Received-Date: Wed, 08 Aug 2007 17:34:38 EDT (text.usenetserver.com)

On Aug 8, 2:27 pm, DA Morgan <damor...@psoug.org> wrote:
> dean wrote:
> > Hello all,
>
> > We have a table T with the following structure:
>
> > Name                          Null?    Type
> >  ----------------------------- -------- --------------------
> >  USER_I                        NOT NULL VARCHAR2(30)
> >  FORM_I                        NOT NULL VARCHAR2(100)
> >  CATEGORY_I                    NOT NULL VARCHAR2(100)
> >  KEY_I                         NOT NULL VARCHAR2(100)
> >  VALUE_X                                VARCHAR2(2000)
>
> > By varying the VALUE_X field (varchar2(N)) from N=100 to N=2000,
> > however, we can alter the time it takes to open this table either onto
> > a grid or to scroll through all records. We are using Borland's
> > TADOQuery here. Oracle is 9.2i or 10g, windows thick client over a
> > LAN.
>
> > All data was the same, however, since the longest actual VALUE_X field
> > was onyl 27 characters.
>
> > Times to open a TdbGrid on user_preference table:
>
> > VALUE_X field:   Time to open:
>
> > varchar2(100):  10.44s
> > varchar2(200):  10.75s
> > varchar2(300):  10.91s
> > varchar2(400):  10.93s
> > varchar2(500):  11.21s
> > varchar2(600):  11.15s
> > varchar2(700):  28.44s <- steps up here
> > varchar2(800):  26.84s
> > varchar2(900):  26.59s
> > varchar2(1000): 26.36s
> > varchar2(1100): 26.78s
> > varchar2(1200): 26.42s
> > varchar2(1300): 26.78s
> > varchar2(1400): 28.27s
> > varchar2(1500): 28.96s
> > varchar2(1600): 28.34s
> > varchar2(1700): 27.64s
> > varchar2(1800): 28.5s
> > varchar2(1900): 28.13s
> > varchar2(2000): 28.36s
>
> > Why does this time step up at N = 700 characters? I have narrowed it
> > down to the ADO component, since an alternative component (ODAC) does
> > not report ANY differences, irrespective of the size of the field. SQL
> > + also reports the same number of bytes transfered and times to open,
> > irrespective of N.
>
> > Similar effect was noted in an alternative table (one with around 30
> > fields) at N = 600, so I am assuming the total rowsize is the property
> > to consider.
>
> > Thanks for any hint. We are considering removing such fields and using
> > a lookup table in such cases, since the data is sparsely populated.
>
> > Dean
>
> Look at array size and cache size settings. My guess is that it is in
> some manner related to ADO or the tool you are using (Delphi ?). It
> isn't something experienced in the database. Something you can confirm
> by running in SQL*Plus.
>
> Anything you ever see in a tool, that you don't see in SQL*Plus, IS
> the tool.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

This is using a serverside cursor, so caching won't have any effect.
For the array fetching, the only parameter I can find in the ADO
documentation is to append FetchSize=N to the connection string that
gets passed to Oracle, but I am not sure if ADO is over-riding this,
since it has no effect.