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

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