Re: A performance question

Gregory Trubetskoy wrote: > > 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. Hi Gregory ! I assume that you have checked all the performance stuff like update statistics on the informix side. Allthough you have a dual pentium machine try your query again with MULTIPROCESSOR 0 (and NUMCPUVPS 1, SINGLE_CPU_VP 1). Informix Tech Support here in Germany told us to set MULTIPROCESSOR to 1 only on machines with more than 2 processors. Have you activated PDQ ? If so, how is your table data fragmented. If your table is not fragmented you should not activate PDQ (PDQPRIORITY 0). Your query uses a temporary table. Have you tried to create a seperate temporary dbspace ? What optimizer hint (OPTCOMPIND) do you currently use ? Try your query again with a different setting. Hope some of my suggestions can help. Tschuess, Dirk -- --------------------------------------------------- Dirk Schmidtpott S I E M E N S EMail: schmidtpott.pad@sni.de ============= Phone: 0049-5251-8-14922 N I X D O R F Fax : 0049-5251-8-27905 The above statements and opinions are my own and do not necessarily represent those of my employer. ---------------------------------------------------