A performance question
Posted in 1997
I am evaluating Informix on NT (to possibly replace MS SQL Server), and I notice a significant difference in performance. I did a little tweaking, not too much - I set BUFFERS to 6000 (x4096K page)(the machine has 128 Meg) SHMVIRTSIZE to 16Meg, MULTIPROCESSOR 1 (the machine is a dual Pentium). Anyhow, I have duplicated a table that I have on SQL server. About 4 Mil rows. I noticed that a SQL statement that I'm trying returns almost immediately on SQL Server, especially the second time (because of caching, I guess), but takes significantly longer on Informix. And output of the same query running the second time isn't any faster. The SQL statement is such that it has to create a temporary table (there is GROUP BY) and has to do some index scanning (there is an "IN (x, x, x, x, x, x)" clause). Both SQL Serv. and Inf. have exactly the same clustered index built, looking at query plans shows (EXPLAIN ON and SHOWPLAN ON)that they both use the index in the query - yet Informix is about 15 times slower. I am positive I don't have somethign configured correctly - if anyone can understand any of the above and has some advice, I'd appreciate it. Thanks.