DB-connection in separate thread - what's the best way?

Question

I am creating an app that accesses a database. On every database access, the app waits for the job to be finished. To keep the UI responsive, I want to put all the database stuff in a separate thread.
Here is my idea:

• The db-thread creates all database components it needs when it is created
• Now the thread just sits there and waits for a command
• If it receives a command, it performs the action and goes back to idle. During that time the main thread waits.
• the db-thread lives as long as the app is running

Does this sound ok?
What's the best way to get the database results from the db-thread into the main thread?
I haven't done much with threads so far, therefore I'm wondering if the db-thread can create a query component out of which the main thread reads the results. Main thread and db thread will never access the query at the same time. Will this still cause problems?

Answer 1

I have implemented both strategies: Thread pool and adhoc thread creation.

I suggest to begin with the adhoc thread creation, it is simpler to implement and simpler to scale.

Only move to a thread pool if (with careful evaluation) (1) there is a lot of resources (and time) invested in the creation of the thread and (2) you have a lot of creation requests.

In both cases you must deal with passing parameters and collect results. I suggest to extend the thread class with properties that allow this data passing.

Refer to the documentation of the classes, components and functions that the thread use to make sure they are thread safe, that is, they can be use simultaneously from different threads. If not, you will need to synchronize the access. In some cases you may find slight differences regarding thread safety. As an example, see DateTimeToStr.

Answer 2

First of all - if you haven't much experience with multi-threading, don't start with the VCL classes. Use the OmniThreadLibrary, for (among others) those reasons:

• Your level of abstraction is the task, not the thread, a much better way of dealing with concurrency.
• You can easily switch between executing tasks in their own thread and scheduling them with a thread pool.
• All the low-level details like thread shutdown, bidirectional communication and much more are taken care of for you. You can concentrate on the database stuff.

The db-thread creates all database components it needs when it is created

This may not be the best way. I have generally created components only when needed, but not destroyed immediately. You should definitely keep the connection open in a thread pool thread, and close it only once the thread has been inactive for some time and the pool disposes of it. But it is also often a good idea to keep a cache of transaction and statement objects.

If it receives a command, it performs the action and goes back to idle. During that time the main thread waits.

The first part is being handled fine when OTL is used. However - don't have the main thread wait, this will bring little advantage over performing the database access directly in the VCL thread in the first place. You need an asynchronous design to make best use of multiple threads. Consider a standard database browser form that has controls for filtering records. I handle this by (re-)starting a timer every time one of the controls changes. Once the user finishes editing the timer event fires (say after 500 ms), and a task is started that executes the statement that fetches data according to the filter criteria. The grid contents are cleared, and it is repopulated only when the task has finished. This may take some time though, so the VCL thread doesn't wait for the task to complete. Instead the user could even change the filter criteria again, in which case the current task is cancelled and a new one started. OTL gives you an event for task completion, so the asynchronous design is easy to achieve.

What's the best way to get the database results from the db-thread into the main thread?

I generally don't use data aware components for multi-threaded db apps, but use standard controls that are views for business objects. In the database tasks I create these objects, put them in lists, and the task completion event transfers the list to the VCL thread.

Main thread and db thread will never access the query at the same time.

With all components that load data on-demand you can't be sure of that. Often only the first records are fetched from the db, and fetching continues after they have been consumed. Such components obviously must not be shared by threads.

Answer 3

What you are looking for is the standard data access technique, called asynchronous query execution. Some data access components implement this feature in an easy-to-use manner. At least dbGo (ADO) and AnyDAC implement that. Lets consider the dbGo.

The idea is simple - you call the convenient dataset methods, like a Open. The method launches required task in a background thread and immediately returns. When the task is completed, an appropriate event will be fired, notifying the application, that the task is finished.

The standard approach with the DB GUI applications and the Open method is the following (draft):

• include eoAsyncExecute, eoAsyncFetch, eoAsyncFetchNonBlock into dataset ExecuteOptions;
• disconnect TDataSource.DataSet from dataset;
• set dataset OnFetchComplete to a proc P;
• show "Hello ! We do the hard work to process your requests. Please wait ..." dialog;
• call the dataset Open method;
• when the query execution will be finished, the OnFetchComplete will be called, so the P. And the P hides the "Wait" dialog and connects TDataSource.DataSet back to the dataset.

Also your "Wait" dialog may have a Cancel button, which an user may use to cancel a too long running query.

Answer 4

If you create your thread at start and reuse it later whenever you need it, you have to make sure that you disconnect the db components (grid..) from the underlying datasource (disableControls) each time you're "processing" data.

For the sake of simplicity, I would inherit TThread and implement all the business logic in my own class. The result dataset would be a member of this class and I would connect it the db aware compos in with synchronize.

Anyway, it is also very important to delegate as much work as possible to the db server and keep the UI as lightweight as possible. Firebird is my favourite db server: triggers, for select, custom UDF dlls developed in Delphi, many thread safe db components with lots of examples and good support (forum) : jvUIB...

Good Luck