IBDAC bad performance compared to FIBPlus in remote connections over a local network.

[lam]

Hello,
We recently migrated our codebase from FIBPlus to IBDAC and we are experiencing very significant downgrades in query execution performance. Especially using an IBCQuery.Open with the same query repeatedly.
Here is the case:

• Server: Firebird 2.5.5
  Connection: Remote over local network.
  Components compared: IBCQuery configured with its defaults vs TpFIBDataset under the same conditions.
  Query: Simple select of a table with 5 records.
  Execution: Count= 100. Connection is ACTIVE throughout the loop. The Query is not reset before each execution.

We executed this test 15 times, this graph shows the results, the Y axis is the execution time in Miliseconds:



IDBAC AVG Time: 668 MS
FIBPlus AVG Time: 212 MS

I read in another Post that turning off the option QueryRowsAffected helped, and it did but we aren't quite there yet:



What else can we do to optimize this? We really need to get back to our FIBPlus execution times as soon as possible.

Thanks in advance.

[ViktorV]

Perhaps, this behavior is due to the BLOB-fields presence in your table. In the FIBPlus components BLOB fields deferred loading is used by default, whereas IBDAC components by default load BLOB fields data immediately. The DeferredBlobRead property is responsible for downloading data to BLOB fields, it is set to False by default. Therefore, to solve the problem try to set the DeferredBlobRead property to True :

Code: Select all

  IBCQuery.Options.DeferredBlobRead := True;

You can find additional information about increasing performance when using our components to work with Firebird on our website: https://www.devart.com/ibdac/docs/index ... rmance.htm

[lam]

Fixed it! It wasn't related to BLOBs as we don't use this kind of fields a lot. Setting the option AutoPrepare to TRUE will make the execution time the same or even better than FIBplus. An important thing to consider is that AutoPrepare option wont have any effect if the AutoClose Option is set to TRUE.

NOTE: The test was made with the Option QueryRowsAffected set to FALSE

[ViktorV]

We are glad to know you have found a solution for the issue.
Feel free to contact us if you have any further questions about our products.