| Subject | Re: Improve IBO Performance |
|---|---|
| Author | |
| Post date | 2015-06-27T13:57:22Z |
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.