infinite loops in separate threads

Question

I have some data base optimization routines that need to execute periodically. I am currently using a TTimer but the main VCL freezes and is very hacky ... I would like to know what the best method is to have a low cpu consumption and execute these routines. I think putting the routines in separate threads with low thread priority is the best way.

Any ideas ?

Answer 1

One way of doing it is creating your "db optimization thread" something like:

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's 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 issues...

So, let's start a db optimization thread

...
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;

You can of course make it a low priority, but if your queries are not going to run for more than a few seconds at each time, I don't see a reason for that, but feel free to contradict.

Answer 2

If possible, it is much better to just code all your threads to do the most important thing that needs to get done at that particular time. Messing with thread priorities can cause serious performance problems if you don't know exactly what you're doing. Instead, just code your threads like this:

1. Is there anything important to do? If so do it.

2. Is there anything not too important to do? If so, do a little of it.

3. Go to step 1.

Say you do use thread priorities. Imagine this:

1. A low priority task, A, grabs a lock on the database.

2. A normal priority task, B, requires lots of CPU time, it steals the CPU from the low priority task.

3. A normal priority task, C, requires access to the database. But it can't run because the low priority task holds the lock on the database and task B gets the CPU over task A.

4. Now, task C has to wait until task B is complete to get access to the database. But it should be timeslicing with task B.

Answer 3

IMHO, a low priority thread is the way to go for this kind of task. But you do not have to create different threads for each optimization routine, handle all of them with only one thread. So it will be easier for you to execute them in some specific order or with different frequencies and you will be sure that they do not get into way of each other (from the point of DB).