Devart Forums

Hi

We are experiencing a problem in some of our customers that is related to the performance of opening a query using the Execute method from TIBCQuery.

In most cases the databse file is big (Like over 10GB), and we are using Firebird 2.5.

When we call the Execute method it could take over 1 minute to open, during that time, since the Execute is synchronous (It will just return to me when it finishes opening the query) the calling thread will be hang, although we use a separated thread to open the query the problem is if we want to finish this thred before execute is finished, so we can´t, we have to wait until the query is finished.

Is there some way to call this Execute method assynchronously and cancel it whenever we want?

Is there a way to improve this opening performance? We don´t know if this is related to firebird itself or IBDAC... How does firebird scale to big database files?

Hello,

You can use the BreakExec method to break execution of a SQL statement on the server. You should call BreakExec only from another thread. When the BreakExec method is called, IBDAC calls fb_cancel_operation with the fb_cancel_raise option. Please read about specifications of this operation in the README.fb_cancel_operation.txt file of the \doc directory (in the Firebird install directory).
Also you can cancel statement execution by deleting a record from the MON$STATEMENTS table: The performance of executing a query depends on Firebird. The speed of data fetching to a client greatly depends on your server capacity and network speed. To improve the performance of data fetching in IBDAC, we suggest you to set the following options:
1. TIBCQuery.Options.DeferredBlobRead and TIBCQuery.Options.DeferredArrayRead to True. The DeferredBlobRead and DeferredArrayRead properties are used for fetching all BLOB and array values correspondingly when they are explicitly requested. This will increase performance in case if your tables have BLOB and array fields.
2. TIBCQuery.ReadOnly to True (in case if you do not need to modify data).
3. TIBCQuery.FetchAll to True. In this case all data will be fetched to the client at once.
4. TIBCQuery.FetchRows to a value that gives the most data fetching speed on your network (it is individual for each network). FetchRows affects only data fetching speed.

Hi AndreyZ,

I'll try this. Thank you.

By the way, there is a way to call the Execute method assynchronously and receive an event to fetch the data? In these cases I can give a response to the user immediately.

If you call the Execute method not in a main thread, your application will not wait for it, and therefore will not hang. In this case, your user will have an immediate response from your application.