Performance degradation question w/transactions

While attempting to diagnose some performance problems with our application, I decided to run some simple benchmarks just to get some idea of the raw performance of Online (5.03.UC1) by itself (without my application on top of it). I encountered some disconcerting results, so disconcerting that I'm in search of some help in figuring out what might be going on. Here are the tests I ran: For each test, I began with an empty database named "benchmark". Database logging was "ON" but directed to /dev/null. Before each test, I created a single table as follows: create table "test".rows ( f1 varchar(32,4), f2 char(10), f3 smallint, f4 date, f5 date, primary key (f1) constraint "test".p1rows ) lock mode row ; All tests were run on a very lightly loaded HP 730 running HP-UX 9.05. (My tests were the only user processes.) The machine has 64MB RAM. Test 1: ------- Insert 100,000 rows into the table. Each insert is an individual transaction. Here is the excerpted SQL file I executed: begin work; insert into rows values ('key_0', 'anything', '0', 'today', 'today'); commit work; begin work; insert into rows values ('key_1', 'anything', '0', 'today', 'today'); commit work; begin work; insert into rows values ('key_2', 'anything', '0', 'today', 'today'); commit work; . . . begin work; insert into rows values ('key_99997', 'anything', '0', 'today', 'today'); commit work; begin work; insert into rows values ('key_99998', 'anything', '0', 'today', 'today'); commit work; begin work; insert into rows values ('key_99999', 'anything', '0', 'today', 'today'); commit work; I got the following results: $ time dbaccess benchmark benchmark1 > bench.log 2>&1 real 8h45m16.43s user 8h31m37.77s sys 10m2.26s Eight hours!!!! What's going on? Test 2: ------- Insert 100,000 rows into the table, this time without transactions. Here is the excerpted SQL file I executed: insert into rows values ('key_0', 'anything', '0', 'today', 'today'); insert into rows values ('key_1', 'anything', '0', 'today', 'today'); insert into rows values ('key_2', 'anything', '0', 'today', 'today'); . . . insert into rows values ('key_99997', 'anything', '0', 'today', 'today'); insert into rows values ('key_99998', 'anything', '0', 'today', 'today'); insert into rows values ('key_99999', 'anything', '0', 'today', 'today'); I got the following results: $ time dbaccess benchmark benchmark2 > bench.log 2>&1 real 11m28.07s user 10m05.66s sys 1m53.38s Test 3: ------- Rerun Test 1 except use Page Locking instead of Row Locking. The results were just as bad as in Test 1: $ time dbaccess benchmark benchmark1 > bench.log 2>&1 real 8h45m16.43s user 8h31m37.77s sys 10m2.26s Test 4: ------- Insert 100,000 rows into the table using 10 transactions of 10,000 inserts each. The results were: $ time dbaccess benchmark benchmark4 > build.log 2>&1 real 7m43.23s user 5m40.99s sys 1m56.41s Test 5: ------- Insert 100,000 rows into the table using 4000 transactions of 25 inserts each. The results were: $ time dbaccess benchmark benchmark5 > build.log 2>&1 real 11m33.85s user 9m9.21s sys 2m11.30s Can anyone suggest what might be degrading the performance of my system so horribly in tests 1 and 3? Are there parameters that I can or should tune in my Informix configuration to avoid getting bogged down? Any insight would be much appreciated! Thanks in advance, John -- ------------------------------------------------------------ John Cowles Hewlett-Packard Company Product Generation Information Systems 300 Apollo Drive, M/S CHR-01-SS Chelmsford, MA 01824 (508) 436-4577 FAX (508) 436-5152 http://www.pgis.hp.com/~cowles mailto:cowles@apollo.hp.com ------------------------------------------------------------