Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Table open times and rowsize discontinuity

Re: Table open times and rowsize discontinuity

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 08 Aug 2007 11:27:37 -0700
Message-ID: <1186597655.62959@bubbleator.drizzle.com>


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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Aug 08 2007 - 13:27:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US