Endless loops in separate threads

Question

I have some database optimization procedures that need to be done periodically. I am currently using TTimer, but the main VCL is frozen and very hacked ... I would like to know what the best method is to have low CPU consumption and execute these routines. I think the best way is to execute routines on separate threads with low thread priority.

Any ideas?

Answer 1

If possible, it is much better to simply copy all your threads to do the most important thing that needs to be done at this particular time. A thread-priority messiah can cause serious performance problems if you don’t know exactly what you are doing. Instead, simply encode your streams as follows:

• Is there anything important? If so, do it.

• Is there something not too important? If so, do a little.

• Go to step 1.

Suppose you use thread priorities. Imagine the following:

• A low priority task, A, locks a lock in the database.

• A task with the usual priority, B, requires a lot of processor time; it steals the processor from a task with a low priority.

• The usual priority task, C, requires access to the database. But it cannot work, because a low priority task contains a database lock, and task B receives a CPU for task A.

• Now task C must wait for task B to complete in order to access the database. But this should be a temporary assignment with task B.

Answer 2

One way to do this is to create a "db optimization stream" something like this:

type // a record defining database connection TConnectionSettings = record DatabaseName: string; Server: string; Port: Word; UserName: string; Password: string; end; type TDBOptimizationThread = class(TThread) private FConnection: TDatabaseConnection; // your database connection... I don't know what libraries you are using FQuery: TQuery; // your specific db query protected procedure Execute; override; public constructor Create(AConnectionSettings: TConnectionSettings; destructor Destroy; override; end; implementation constructor TDBOptimizationThread.Create(AConnectionSettings: TConnectionSettings; begin inherited Create(True); // create suspended //FreeOnTerminate := True; // if you want it to be freed when you terminate it // create FConnection and FQuery objects // setup FConnection parameters based on AConnectionSettings end; destructor TDBOptimizationThread.Destroy; begin // destroy objects inherited Destroy; end; procedure TDBOptimizationThread.Execute; begin while NOT Terminated do try // check if it time to run query // you can use a private variable of TDateTime type that will hold // last timestamp of when the query ran, etc. if ItsTimeToRunQuery then begin // check if we still have db connectivity if NOT FConnection.Connected then // ouch, try to connect... FConnection.Connect; FQuery.SQL.Text := 'Your optimization query'; FQuery.Execute; // or ExecSQL or whatever the method is based on your db library end; except on E: Exception do begin // log exception, something went wrong!! end; end; end; 

It is very important that your db connection is created and destroyed in this thread, otherwise you will have problems ...

So let the db optimization thread begin

 ... var LConnSettings: TConnectionSettings; // you may want a private TDBOptimizationThread variable rather than // a variable in a method, but I leave that to you LDBOptimizationThread: TDBOptimizationThread; begin LConnSettings.Database := 'MyDatabase'; LConnSettings.Port := 1234; LConnSettings.Server := 'localhost'; // continue with connection settings... LDBOptimizationThread := TDBOptimizationThread.Create(LConnSettings); LDBOptimizationThread.Start; // start it end; 

Of course, you can make it a low priority, but if your requests are not executed for more than a few seconds at any given time, I see no reason for this, but do not hesitate to contradict.

Answer 3

IMHO, a low priority thread is a way to go about this task. But you do not need to create different threads for each optimization routine, process all of them with only one thread. Thus, it will be easier for you to execute them in a certain order or with different frequencies, and you will be sure that they do not enter into each other (from the database point).