Informix Database is extremely slow when combining with Anydac

1
votes

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.

1
delphioptimizationdelphi-2010informixanydac
I think that AnyDAC forum would be right place for a question - da-soft.com/forums/anydac-general-english.html – Branko
My experience with AnyDAC is that it has excellent performance, so I was looking for issues with Informix. I found da-soft.com/blogs/anydac-2.0-informix.html . Not much but maybe this can help you. – Branko
@Branko: Thank you for answering (commenting), we will be asking in the anydac forum, i thought maby anyone had the same issue, if ill get an answer from anydac ill post it here too. – CloudyMarble
General comment: don't have users connect to your Informix database as user informix; it is a monstrous security liability. I assume you omitted the password for security reasons; there is a password in the real system, isn't there? Connecting as informix is like logging in as root (or System Administrator) at all times. All else apart, it means that the slightest slip can be deadly because the user is all-powerful and can do all sorts of damage without restraint. – Jonathan Leffler

1 Answers

1
votes
  1. 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.
  2. 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.