Before I get into the subject, I want to make it abundantly clear that there are many… many other ways of achieving the same thing and some of them might be even better than what I am going to explain here. However, the reason for writing this blog isn’t to get the best performance of the code (although you will definitely see high performance improvement), but it is rather to explain the concepts of pooling, multi-threading and making them work together to boost application response times.

I chose database access as the way of explaining the concept of pooling and threading because, well, that seems to be one of the most useful area where this functionality can be implemented.

Don’t throw away that TThread class yet!

When Parallel Programming Library (PPL) was introduced in the RTL, I almost saw it as the death knell for the TThread class. I was thinking that… here’s a simplified approach to creating thread with a focused method that’s automatically executed… all in a single line of code. No doubt, PPL (just as OmniThread library) is an immensely useful library and can be used for almost all cases where you require a standard threaded execution. Of course, this super simplified approach comes with some constraints and when it comes to creating more elaborate and complex functionality and they leave something to be desired. That’s when the good old TThread class can come to our rescue.

Inheriting from the TThread class can enable us to write complex structures within the thread which can be quite useful as you will see ahead.

The components

For the sake of simplicity, I have used one single unit which contains all the components needed to do what we have set out to do. The components are :

• The thread class : This will be the thread that will have the connection object, query and some custom properties and method that we will find useful.
• A simple Array : This will be a placeholder for storing multiple instances of our above custom thread class.
• Unit methods : Some basic methods to manage the the above array.

With that in mind, lets start building! 🙂

IMPORTANT

Our Objective is to create a series of threads that are database enabled. Each thread has the ability to execute a SQL statement independently on the database server. An important aspect of this mechanism is to do away with the load of connecting to the database again and again for every execution. Basically, we want to keep a fixed number of thread ready and connected to the database and simply keep re-using them for new SQL statements the application requires. In the end of the application life cycle, we want to free all these connections, queries and thread, there by providing us a clean (read memory leak less) exit.

So, the first thing that we will build is our custom thread class. I will simply paste the code for the class here and follow it up with an explanation.

Explanation of the code

Most of the above code for the class is pretty straight forward and I will explain that swiftly. The interesting area of the code is the Execute procedure which is of course where the thread code actually runs. That bit I will explain a bit more in detail.

Okay, so lets start with the structure. To make the objects thread safe, the objects have to be created within the thread so that every object has their own memory space and its guaranteed not to be shared with other thread. Therefore, we have the connection and the query objects as private fields of the thread.

Also, I have defined a field for the SQL statement that will be executed by the FDQuery object within the thread. Since the objects are in the thread, its guaranteed to be executed in parallel to the application main thread in a non blocking fashion. I have set it up as a property because I want thread to be notified when the SQL statement changes. Why? Details explanation will come up when I explaining the Execute method.

Additionally, I have a field for a call back function, in case the user of the thread want to be notified once the query execution is completed. This is optional.

Finally, I have created two methods, Initialise and Finalise, which will be called only once each in the entire life time of the thread. The code for that is pretty straight forward, simply setting up the database objects when created and free them when thread is terminated.

The main two things I want to explain here is the Execute method and the FResume / FShutdown flags. Lets start with the Execute method.

So here’s the thing, the Execute method can be called only once in the lifetime of the thread. Which basically means that once you execute a thread for a particular set of instructions, its impossible to call the same instance again. If you want to re execute, you have to create a new instance of the thread. I know, its unlike what you are used to. I mean, Execute is a method and it should be re-callable, right? Not so for thread. If you call Start on a thread that’s already executed (or executing), its going to throw an error saying, ‘Cannot call Start on a running or suspended thread.’. So, lets try and understand how we can circumvent this limitation.

Never let Execute finish!

Since the thread is anyway a non-blocking entity to the main application, it doesn’t matter if we keep it running in the background (as long as we are not doing something crazy with it which will hog up the resources). The idea is to trap the thread execution in an infinite loop like this

That’s the first step. Now, the next thing we do is to set our own resume flag using the boolean variable FResume to execute what that thread needs to execute. In our case, we simply want the thread to execute the SQL that’s in its FDQuery component like this

There’s another custom FSQLType variable that I have created only to determine if FDQuery is going to be opened (return data) or simply execute a query.

Since these states are in an infinite loop, we do not want to keep executing them over and over again, right? We just want to Execute them once until again the user of the thread gives another explicit instruction to re execute (another SQL statement maybe). That’s the reason we check the FResume to be True and set it to False immediately after.

The way we want to tell the thread to execute another SQL is by setting the FSQL property to a new statement and then setting the FResume to True again. The infinite loop will check the FResume and execute the SQL if its True and immediately make it False thereafter.

Just as an added feature, I thought it would be nice to notify the user that the execution of the SQL is done by calling the call back method that user has set immediately after the execution like this

In this case, the thread is designed to send the index of the pooled thread used (more explanation coming up) with the SQL it just executed.

Finally, we have to have a way of breaking out of this infinite loop, right? For that, I have created another custom boolean variable FShutdown which when set to True, will break the infinite loop and terminate that thread once and for all.

Note: I could have used the already existing Terminated variable but I just wanted to be explicit for the explanation.

But hey, hang on… what’s with the Sleep(5) in the infinite loop? Sleep gives a small break to the infinite loop so that other messages can be processed by the system else the thread will hog up the CPU.

Now that we have our database aware thread class ready, we can create the methods to manage multiple instances of it by creating a reusable ‘pool’ for it.

The above code creates 5 instances of our thread class, each ready to work on the database asynchronously. In the initialization section, we create the pool and in the finalization, we destroy it.

Finally, we have the two externally exposed (read callable by the user) method as follows:

The above methods will ensure that the pool is used in a round robin fashion (circular). Of course, you can write some mechanism to get non working threads and not go circular (like I have).

Here’s link to the entire source code with the test project. This was tested on a local MySQL database and I ran upto 4 instances… each instance inserting a record every few milliseconds. I got speeds of upto 2000 inserts in a few seconds. Most importantly, no errors… no leaks! 🙂

You will need to customise the FDConnection to your database as well as the SQL statements for your tables.

Hope that helped. Your comments are welcome!