Table open times and rowsize discontinuity

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 Received on Wed Aug 08 2007 - 13:01:37 CDT