Re: Informix WAN performance compared to SQL and Oracle

David We use Informix from choice in our 64Kbs WAN environment (on NT, HP-UX and Linux) because it is FAST. IMHO what you DON'T want to do is fish about in the dark. Suggestions: 1) Use a packet sniffer to see exactly what is being put out (packet size, SQL content, address resolution etc) and returned, on the network and you will have your answer. 2) Compare ODBC traces for the three databases. If the SQL and returned data is not largely the same for each database (e.g. you see lots of repeated schema fetches with Informix) call into question the ODBC/SQL competence of the language environment used to produce the application. It may not be truly database independent - it may request ODBC functionality that exists in the ORA or SQL drivers and use a lengthy workaround with the INF driver. The traces will tell you. 3) Install Informix DBAccess on the client - run the SQL select from there (that's guaranteed efficient). If that performs its not a network or database engine issue its ODBC and/or application. Otherwise suspect some address resolution issue - or network mis-configured on the client or server or a router. Use onstat on the server to check network buffer overflow - you should see none. If you see any then you do have a transport problem of some sort. We run heavyweight client-server apps, until very recently over only 64kbs WAN links, with no problems. I have no axe to grind - we do use SQL Server and Oracle and others but we find Informix performance, correctly set up, awesome. Hope this helps some. Good luck. Bill Allison BSW Timber plc In article <Pine.LNX.4.20.0011162159450.4748-100000@newt.pandora.be>, David Stes <stes@pandora.be> wrote: > > On Thu, 16 Nov 2000 kbadinger@my-deja.com wrote: > > > We have a software product which backend 3 different databases; > > Informix, SQL Server and Oracle. Our customers run these products over > > both WAN and LAN environments. We use odbc to connect to these > > databases. > > One obvious question is whether these customers use the same type of WAN. > > For example there may be customers who have a T1 or multiple T1's or even > gigabit ethernet links ... (they may simply use different wan > technologies). The Informix users may have just a 128 Kilobit line while > the SQL Server users may have a faster link. > =20 > > We are experiencing severe performance degradation of the application > > when back ended by Informix. A few of our Informix customers have > > escalated these issues and are demanding we solve them. We setup a > > controlled lab environment to test this problem. We have loaded the > > same customer dataset into all 3 platforms. Specifically these are; > > Informix 7.31.TC4, SQL Server 7.0, and Oracle 8.0. We are using the > > following ODBC drivers; Informix 2.80 driver, SQL Server 3.70, and > > Oracle 8.00. Both the client and the database server are running > > Windows NT 4.0 sp4. > >=20 > > We are fetching a total of 18,000 rows from the database, ranging in > > fetch from 1 row to 5000 rows at a time. Running this process over a > > LAN, the following results were found: > > Informix=09Oracle SQL Server > > 34097 ms=0933456 ms=0942509 ms > >=20 > >=20 > > Running this process over the WAN, the following results were found: > > Informix=09Oracle=09 SQL Server > > 339093 ms=0978042 ms=0966164 ms > >=20 > > Consistently, Informix performance over the WAN has been 4-5 times > > slower than Oracle, and 5-6 times slower than SQL Server. > >=20 > > There are 4 hops to the server over the WAN. > >=20 > > I have attempted to tune the fet_buf_size environment variable, and I > > have added the =96b 4096 variable to the server startup. Neither of > > these has made a significant positive effect. > >=20 > > My questions are: > > 1.=09Is Informix performance in a WAN/ODBC environment simply > > constrained to being slower than Oracle and SQL in that > > environment. > > 2.=09If not, what variables need to be tuned to improve performance > > for Informix in that environment. > >=20 > > Please let me know if there is any additional information that I can > > provide to help resolve this problem. > >=20 > > Any help would be greatly appreciated. > >=20 > > Thanks, > > Kevin Badinger > >=20 > >=20 > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > >=20 > > Sent via Deja.com http://www.deja.com/ Before you buy.