RE: IBM .net Driver for Informix vs MSSQL .net driver

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 [mailto:owner-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.