Subject RE: [IBO] Re: Improve IBO Performance
Author IBO Support List
Post date 2015-06-27T17:40:26Z
Yes, this is excellent advice. When you said your performance was especially degraded with queries that had many fields I should have asked how your ColumnAttributes was configured. If there is a lot of these settings it does make the time to prepare the query take longer.
 
Thank you for sharing your results!
 
Jason
 

From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com]
Sent: Saturday, June 27, 2015 6:57 AM
To: IBObjects@yahoogroups.com
Subject: [IBO] Re: Improve IBO Performance

Hi, 

Thanks for answer.

We use only TIBO* components. Migrate to TIB_* would not be viable for us. 

Making new tests we found some interesting things that I would like to share.

I added, in the procedure of the previous post, a TClientDataset and TDataSetProvider linked to TIBOQuery and the result was a considerable deterioration in the benchmark:

IBO Before Open Query: 5507 ms
IBO After Open Query : 11653 ms

Investigating, we realized that the IBO scans the Schema Database that consumes considerable time on the connection and the first query open. So we add the local cache dir in the TIBODatabase:

IBO_DB.SchemaCacheDir := 'C:\CacheIBO';

Response time has improved considerably, and add or not TClientDataset and TDataSetProvider linked to TIBOQuery resulted in no significant difference in this case:

IBO Before Open Query: 453 ms
IBO After Open Query : 749 ms

However, in our project, many queries were still considerable time to run SQL's, that´s running in a few milliseconds by IBExpert, for example.  

Then, after several attempts, we found that many queries were with ColumnAttributes property with lots of columns configured without the need (inherited setting from Delphi 6 time), and that made some queries delay 3.5 - 5.5 seconds and now, with ColumnsAttributes property clean, perform at < 400 milliseconds.

In conclusion, we got a significant improvement in performance by setting a cache directory for the IBO, and cleaning the ColumnsAttributes property that were set unnecessarily.