Path: text.usenetserver.com!out02b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!k79g2000hse.googlegroups.com!not-for-mail From: dean Newsgroups: comp.databases.oracle.misc Subject: Table open times and rowsize discontinuity Date: Wed, 08 Aug 2007 11:01:37 -0700 Organization: http://groups.google.com Lines: 63 Message-ID: <1186596097.751275.85520@k79g2000hse.googlegroups.com> NNTP-Posting-Host: 151.204.192.226 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1186596097 8192 127.0.0.1 (8 Aug 2007 18:01:37 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 8 Aug 2007 18:01:37 +0000 (UTC) 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: k79g2000hse.googlegroups.com; posting-host=151.204.192.226; posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0 Xref: usenetserver.com comp.databases.oracle.misc:249161 X-Received-Date: Wed, 08 Aug 2007 14:01:38 EDT (text.usenetserver.com) 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