Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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
![]() |
![]() |