Re: Informix slower than MS Access ?
From: "S. Radtke" <rt@pascal.de>
>
>is there any help for me and my informix server ???
Yep.
>I have a performance problem with Informix IDS 7.30 UC5 on a SUN Solaris
>5.5.1 (one processor with 512 MByte RAM)
>
>There is a table with 14 columns and 1.600.000 Rows.
>
>My Sql-Select is :
>
>SELECT lager, art_nr, SUM(menge)
>FROM lag_bew
>WHERE art_nr > 99000000
>GROUP BY lager, art_nr
>HAVING SUM(menge) > 10000
>ORDER BY art_nr>
>The result contains 27 rows and takes 45 seconds ;-(.
>
>The same select on an NT-Server (smaller than the SUN) with Mr. Gates's
>Access takes only 25
>seconds (Thats true !).
>
>Informix does a sequential scan on the table an needs a temporary file for
>group and order by.
>The table has only 2 extents.
>
>If i create a index such as (art_nr, lager) or (lager, art_nr) the select
>takes over one minute (Thats true also !).
>The explain shows that Informix use this index and need no temporary file.
>
>The database has no transactions, the lock mode of the table is page and if
>i lock the table in exclusive mode the select is only 2 seconds faster.
>
>Now to the server:
>
>I'm using two cooked files, one for the rootdbs and one for the datadbs on
>the same disc-device.
>Does the performance increase so much if I'm using raw devices ?
>
>Here is my $ONCONFIG-File:
>
[SNIP]
>
>WHAT CAN I DO ?????
UPDATE STATISTICS.
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
Obnoxio The Clown schrieb in Nachricht <7i13p2$r2g$1@news.xmission.com>...
>UPDATE STATISTICS.
I've already done that...
Thanx again...
Sven
S. Radtke wrote in message <7i148r$5j2$1@news.ppp.net>...
>
>Obnoxio The Clown schrieb in Nachricht <7i13p2$r2g$1@news.xmission.com>...
>
>>UPDATE STATISTICS.>
>
>I've already done that...
>
>
>Thanx again...
>
>
>Sven
>
>
You don't mention a temporary dataspace nor what your buffers are set at.
I would consider a temporary dataspace, otherwise Informix (as far as I
understand it) will use temporary files, thereby adding to the overhead,
since it has to create files, etc. etc., whereas a temporary dataspace would
avoid those headaches.
If buffers are too small, your hit rates will be too low (do onstat -p
before and after transaction). Memory's cheap, use it.....
As one IMS/DB instructor once told me, "the only good I/O is NO I/O".