This is IBM's response : ( Unacceptable by the way )

Subject: IBM Informix case#439472 -"Performance is slow. Select on blob,
inserts on blob"

Dear John,
I am the new case owner of IBM Informix case#439472 -"Performance is
slow.
Select on blob, inserts on blob"

We currently have a feature #171522 "IBM INFORMIX NEEDS TO HAVE A FULLY
MANAGED .NET PROVIDER" open which matches your problem. Please see the
details below:

>>
One of the areas where the .Net application experience higher
performance
concerns is while it makes interoperation of managed and unmanaged code.

The problem with mixing managed and unmanaged code in an application is
that the unmanaged or native code doesn't recognized in the .NET
environment. Managed code components not only depend on the classes
available in the .NET Framework, they also expect the other components
with
which they interact to depend on that framework as well.

Even the most trivial calls into native code must undergo a mode
transition. The mode transition, which is the physical process of moving
data between the managed and unmanaged modes of operation. Typically it
requires about two dozens of instructions. The other cost involved in
the
process is marshalling the data to move across the boundary.

Marshalling is necessary because the internal representation of data
structures is different between managed and unmanaged code. To pass data
across the boundary, it has to change the data from the .NET
representation
to the native one, and then back again. Marshalling is process
expensive
and the more data it move back and forth, the more expensive it becomes.
Marshalling data structures one way can add as much as 3,000
instructions
to the processing time for complex data.

Since we are dealing with a database driver and it does massive data
movement, it must be undergoing enormous overhead that could have
avoided
having a fully managed .NET provider.

A .NET application that mixing managed and unmanaged code could degrade
its
performance almost 5 to 10 times. Especially when it is dealing with
lots
of data exchange between the boundary,
the performance degradation figure will be at the higher end.
<<

The Informix R&D group is in the initial phases of developing a plan to
implement a fully managed .NET provider. Currently, the plan is to
provide a fully managed provider on the .NET 2.0 Framework which is
supposed to be released by Microsoft later part of this year. R&D hopes
that they will have a release with the fully managed provider about 3
months later, give or take. This would therefore be at around Feb/March
2006

At this point, we know that "some things" can be done faster with the
ODBC
bridge than our .net provider, but until we develop a new provider,
there
is little we can do about it.


-----Original Message-----
From: owner-informix-list@iiug.org [mailtowner-informix-list@iiug.org]
On Behalf Of Art S. Kagel
Sent: Thursday, September 22, 2005 5:43 PM
To: informix-list@iiug.org
Subject: Re: IBM .net Driver for Informix vs MSSQL .net driver

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
sending to informix-list

as I indicated in an realier post, the fix for this will be non trivial
and will take some time.

If the timescale provided by support us unacceptable, please discuss
with your account team.