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)

This is a known problem with the current Informix .net driver I understand any fix for this would be is non trivial. My suggestion would be to contact your support provider. Jose da Fonseca wrote: > 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

You may already have discussed this with your support provider and they have informed you of the timescales to resolve this. If this is unacceptable you will need to discuss with your account team.

Jose da Fonseca wrote: Have you opened a case with tech support? Likely they have seen this problem with .net performance and may have a work around or recommendation for a better .net provider! Art S. Kagel > 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

scottishpoet wrote: > This is a known problem with the current Informix .net driver > > I understand any fix for this would be is non trivial. > > My suggestion would be to contact your support provider. > > > Jose da Fonseca wrote: > >>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 > > Does it have a bug number? Regards.

yes it does