[SOLVED] Informix Database is extremely slow when combining with Anydac

Informix Database is extremely slow when combining with Anydac

We are facing real performance issue with our application since we moved to AnyDac as an alternative to BDE.

The application supports more than one database, so other ones like SQLSrvr and Oracle runs very well, but Informix got real real slow since then. It was predictable that it would get a bit slower because of the ODBC-Driver, but it's so slow that we suspect it could be a configuration issue of AnyDac (ADConnectionDefs.ini).

Did anyone else have such an issue?

Adding more info up to da-soft request

We are using anydac version 4.0.7.1723
Our Informix ODBC Driver is v3.7
Database server is Informix-11
The Informix configuration alias is as follows:

Server=
Database=
User_name=informix
Password=
DriverID=ODBC
DataSource=ODBC_Informix_Alias
MetaDefSchema=informix
FetchOptions.RowSetSize=1000
MonitorBy=
FetchOptions.AssignedValues=[evMode, evItems, evCache, evAutoClose, evRecordCountMode, 
                             evCursorKind, evRowsetSize]
FetchOptions.Items=[fiBlobs]
FetchOptions.Cache=[fiBlobs, fiMeta]
FetchOptions.Mode=fmAll
FetchOptions.CursorKind=ckDefault
FetchOptions.AutoClose=False
FetchOptions.RecordCountMode=cmVisible
FormatOptions.AssignedValues=[fvMapRules, fvMaxBcdPrecision, fvMaxBcdScale, fvSE2Null]
FormatOptions.OwnMapRules=True
FormatOptions.MaxBcdPrecision=2147483647
FormatOptions.MaxBcdScale=2147483647
FormatOptions.StrsEmpty2Null=True
ResourceOptions.MacroExpand=False
ResourceOptions.EscapeExpand=False
ResourceOptions.MacroCreate=False
ResourceOptions.ParamCreate=True
ResourceOptions.ParamExpand=True
ResourceOptions.DirectExecute=True
ResourceOptions.AssignedValues=[rvMacroCreate, rvMacroExpand, rvParamCreate, rvParamExpand, rvEscapeExpand, rvDirectExecute]
FormatOptions.MapRules=< 
    item SourceDataType = dtBCD TargetDataType = dtDouble end 
    item SourceDataType = dtInt64 TargetDataType = dtDouble end 
    item SourceDataType = dtDateTimeStamp TargetDataType = dtDateTime end 
    item SourceDataType = dtByte TargetDataType = dtInt16 end item end >

The line FetchOptions.RowSetSize=1000 was added to try improve performance but it didn't help.

Solution

This issue was resolved many versions ago (in v 4.0.1, i think). So, if you are not with latest AnyDAC version, then consider to upgrade.
Consider to remove fiMeta from FetchOptions.Items and set different FetchOptions.CursorKind.

PS: In general, to help you with such issues, I will need AnyDAC, Informix server, Informix ODBC driver and Delphi versions. Also, the table DDL and simple test application will help.