Nick West wrote: >I think we can keep this very simple by just >looking at one extreme case: CALNONLINEARITY. The data >consists of 40 floating point numbers and there is ~186000 >rows. This is what I have done:- > >1) Design:- > I have made each row a separate row in the database, >well they cannot all be one row! > I have made each row an object. Perhaps I could do >better here say one object for the whole table with handles to >the rows? > >2) Implementation:- > It takes ~ 100 secs cpu and 175 sec elapse to build the table:- > cpu elap > 45 110 secs for the raw query to get data into memory > 25 30 secs to make the table row objects > 15 20 secs to get the data via TSQL > 15 15 to fill the table row objects > >Here are the two questions:- > > 1) Is my design right, and if not what should I have done? > 2) If the answer to 1) is that it was right, is the >performance badly wrong, and if so where am I wasting time? > >If we could speed up this worse case by a factor of 5 or so >then I would have to eat my words about the technology choice! (create CALNONLINEARITY table on fndapl mysql server) mysql> select * from CALNONLINEARITY where seqno=200000000; [..] 185856 rows in set (3 min 25.56 sec) mysql> select distinct seqno from CALNONLINEARITY; +-----------+ | seqno | +-----------+ | 200000000 | | 200000010 | | 400000000 | | 400000010 | +-----------+ 4 rows in set (9.43 sec) select CONST1 from CALNONLINEARITY where seqno=200000000; 185856 rows in set (11.30 sec) select CONST1,CONST2,CONST3,CONST4 from CALNONLINEARITY where seqno=200000000; 185856 rows in set (25.64 sec) select CONST1,CONST2,CONST3,CONST4, CONST5,CONST6,CONST7,CONST8, CONST9,CONST10,CONST11,CONST12, CONST13,CONST14,CONST15,CONST16 from CALNONLINEARITY where seqno=200000000; 185856 rows in set (1 min 25.73 sec) select CONST1,CONST2,CONST3,CONST4, CONST5,CONST6,CONST7,CONST8, CONST9,CONST10,CONST11,CONST12, CONST13,CONST14,CONST15,CONST16, CONST17,CONST18,CONST19,CONST20, CONST21,CONST22,CONST23,CONST24, CONST25,CONST26,CONST27,CONST28, CONST29,CONST30,CONST31,CONST32 from CALNONLINEARITY where seqno=200000000; 185856 rows in set (2 min 44.23 sec) select SEQNO, TASK, SEIDKEY, STRIPENDID, DATALEN, CONST1,CONST2,CONST3,CONST4, CONST5,CONST6,CONST7,CONST8, CONST9,CONST10,CONST11,CONST12, CONST13,CONST14,CONST15,CONST16, CONST17,CONST18,CONST19,CONST20, CONST21,CONST22,CONST23,CONST24, CONST25,CONST26,CONST27,CONST28, CONST29,CONST30,CONST31,CONST32,CONST33,CONST34,CONST35,CONST36,CONST37,CONST38,CONST39,CONST40 from CALNONLINEARITY where seqno=200000000; 185856 rows in set (3 min 25.45 sec) (CONCLUSION:: return times are a function of # of columns as well as number of rows) mysql> desc CALCOMPRESS; +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | SEQNO | int(10) unsigned | YES | | NULL | | | TASK | int(11) | YES | | NULL | | | SEIDKEY | int(10) unsigned | YES | | NULL | | | STRIPENDID | int(10) unsigned | YES | | NULL | | | DATALEN | int(11) | YES | | NULL | | | CONSTANTS | varchar(255) | YES | | NULL | | +------------+------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) select SEQNO, TASK, SEIDKEY, STRIPENDID, DATALEN, CONSTANTS from CALCOMPRESS where seqno=200000000; 185856 rows in set (1 min 3.74 sec) ********************************************************************** (SO:: retrieving 6 columns, one of which is 'fat' is ~4 times faster than retrieving 46 'skinny' columns w/ mysql) ********************************************************************* ./tstit.pl -c 'dbox/dbox@DBI:mysql:test;host=fndapl' -d mysql -t CALNONLINEARITY -q 'where seqno=200000000' CALNONLINEARITY -------------------------------- number of rows: 185856 time ./tstit.pl -c 'dbox/dbox@DBI:mysql:test;host=fndapl' -d mysql -t CALNONLINEARITY -q 'where seqno=200000000' CALNONLINEARITY -------------------------------- number of rows: 185856 digest bc2bb0197a676debbbb738d77697846b 60.697u 1.845s 4:22.31 23.8% 0+0k 0+0io 540pf+0w time ./tstit.pl -c 'dbox/dbox@DBI:mysql:test;host=fndapl' -d mysql -t CALCOMPRESS -q 'where seqno=200000000' CALCOMPRESS -------------------------------- number of rows: 185856 digest b2e65f5a3f88d15fd9d509e1679227c5 15.634u 1.804s 1:21.84 21.2% 0+0k 0+0io 540pf+0w ************************************************************* consistent w/ above conclusion ************************************************************* time ./tstit.pl -c 'dbox/dbox@minosdev' -d Oracle -t CALNONLINEARITY -q 'where seqno=200000000' CALNONLINEARITY -------------------------------- number of rows: 185856 digest bc2bb0197a676debbbb738d77697846b 72.070u 0.816s 2:04.64 58.4% 0+0k 0+0io 1179pf+0w time ./tstit.pl -c 'dbox/dbox@minosdev' -d Oracle -t CALCOMPRESS -q 'where seqno=200000000' CALCOMPRESS -------------------------------- number of rows: 185856 digest 1a53b2e8cea2035421f53ad8038b2bc0 20.755u 1.005s 2:26.55 14.8% 0+0k 0+0io 1179pf+0w Now see how much faster minos-db1 is than fndapl...... time ./tstit.pl -c 'reader/minos_db@DBI:mysql:offline;host=minos-db1' -d mysql -t CALNONLINEARITY -q 'where seqno=200000000' CALNONLINEARITY -------------------------------- number of rows: 185856 digest bc2bb0197a676debbbb738d77697846b 60.546u 1.619s 1:17.64 80.0% 0+0k 0+0io 540pf+0w