Thank you for that. We are using 2.90 xC3.
We have tested the same thing with 2.81 and older and have the same
problem.
It may be that Microsoft have "super-tuned" there provider and Informix
have not ?


-----Original Message-----
From: owner-informix-list@iiug.org [mailtowner-informix-list@iiug.org]
On Behalf Of Chafik, Basim
Sent: Thursday, September 22, 2005 3:52 PM
To: informix-list@iiug.org
Subject: RE: IBM .net Driver for Informix vs MSSQL .net driver

Jose,

What version of CSDK you are using on the client. .NET support was
introduced in CSDK 2.81.TC3 and had some problems (according to Tech
Support). Even that you are using .NET, but behind the seen, the .NET
provider is using ODBC (so all problem/bugs for ODBC drivers are
inherited).
A lot of enhancement and fixes were added to CSDK 2.90.TC3.

Basim Chafik
Senior Systems Analyst
IBM Certified Advanced Technical Expert (CATE)
1.800.688.4895
basim.chafik@plx.com
plexus (Division of BancTec)



-----Original Message-----
From: Jose da Fonseca [mailto:jose@lads.co.za]
Sent: Thursday, September 22, 2005 7:28 AM
To: informix-list@iiug.org
Subject: IBM .net Driver for Informix vs MSSQL .net driver


HI All,

Has anybody had any experience with porting from MSSQL to Informix? We
are have successfully ported the data from MSSQL to Informix ids 10 (two
attempts - data types were a bummer).

We are now finding performance issues when reading the same data from
the Informix database. I am not sure what Microsoft have done in there
driver but the getting of data from Informix whether ODBC or .net driver
is credibly slow.

This is what I have seen.

Doing a select statement through dbaccess matches speed of doing same
query on through enterprise manager on MSSQL.

The moment you do the same query through vb.net we see the following:

We have set the fetchbuffersize to max +- 32767 .

On single record return selects , no visible difference.

(select returns 1 column of varchar (200,0) average bytes per row 20)
(select suburbname from area )



On select that returns +- 11768 rows : vb from same machine to mssql db
on in intel 4 processor server 2003 machine < 1second

Vb from same
machine to IDS 10 on pa risc 8 way hp-ux server 8 seconds

On select that returns +- 60000 rows : vb from same machine to mssql db
on in intel 4 processor server 2003 machine < 1second

Vb from same
machine to IDS 10 on pa risc 8 way hp-ux server 8 seconds



On a more complex select statement : Multiple table joins with nested
loops.



MSSQL from vb.net - 60000 rows returned - 1 minute 13 seconds

Informix from vb.net - 60000 rows returned (after optimizing the sql
statement) 14 minutes



I really need to come up with some answers quickly or I will be forced
to migrate the Informix to MSSQL . ( business requirements require us
to merge the to platforms urgently)



From what I have experienced in all the testing, it is not the SQL
execution itself where the problem lies but the communication between
the database server and the application set to fill the dataset or loop
through the datareader. I have looked at 3rd party database products
like connx, datadirect and openlink but they do not have a driver of
there own for .net, they simple add a wrapper to the odbc or cli
components. Can anyone point me in the right direction?



Thank you

Jose
sending to informix-list
sending to informix-list
sending to informix-list