Delphi 2010: No thread vs threads - TSQLConnection and TSQLDataSet

Question

Hi everyone,

My previous question

From the above answer, means if in my threads has create objects, i will face the memory allocations/deallocations bottleneck?

I've a case that I need to create TSQLConnection and TSQLDataSet to query data from 5 tables of the database, each table has more than 10000 records. So I will create 5 threads, each thread accept a tablename as parameter via constructor. Unfortunately, i cannot get more obvious difference of time taken. I've write the following codes:

TMyThread = class(TThread)
private
  FTableName: string;
protected
  procedure Execute; override;
public
  constructor Create(const CreateSuspended: Boolean; const aTableName: string); 
    reintroduce; overload;
end;

constructor TMyThread.Create(const CreateSuspended: Boolean; const aTableName);
begin
  inherited Create(CreateSuspended);
  FTableName := aTableName;
  FreeOnTerminate := True;
end;

procedure TMyThread.Execute;
var C: TSQLConnection;
    D: TDataSet;
begin
  C := NewSQLConnection;
  try
    D := NewSQLDataSet(C, FTableName);
    try
      D.Open;
      while not D.Eof do begin
        // Do something
        D.Next;
      end;
    finally
      D.Free;
    end;
  finally
    C.Free;
  end;
end;

function NewSQLConnection: TSQLConnection;
begin
  Result := TSQLConnection.Create(nil);

  // Setup TSQLConnection
end;

function NewSQLDataSet(const aConn: TSQLConnection; const aTableName: string):
    TSQLDataSet;
begin
  Result := TSQLDataSet.Create(aConn);
  Result.CommandText := Format('SELECT * FROM %s', [aTableName]);
  Result.SQLConnection := aConn;
end;

Is there any advice or recommendation for this case?

Answer 1

My first thing I should do: take a look at the CPU time. Where is the bottleneck: is your application using 100% CPU in single user mode, then multi-threaded won't work (only on dual/quad core). But if your app in multi-threaded mode is not using much CPU you could have an other bottleneck: for example, the DB server is maybe using 100% CPU? Or your server HD is slow? Or slow network? Or maybe even the DBExpress database driver is not multi-threaded, so it only uses 1 thread at a time (you will notice low CPU on client and server on multicore).

By the way: yes, you should always try to minimize memory allocations. FastMM is not the best MM for heavy multithreaded: does not scale very well on multicore (you will never see 100% CPU in memory heavy apps). But nothing to bother about in normal apps. Only if this is the case, you could try TopMM: somewhat slower(?) but scales much better: http://www.topsoftwaresite.nl/Downloads/TopMemory.pdf

Answer 2

Actually I would like to see a CPU GRAPH with Process Explorer, like step 4 of this page: http://www.brightrev.com/how-to/windows/53-five-uses-for-sysinternals-process-explorer.html

Because it shows CPU+mem+IO(!) in time. Please of both test app and firebird.

I think firebird is bound by IO (your HD), because in your first CPU screenshot, both have low CPU. Maybe you can increase firebird memory cache?

Btw: how many cores/cpu's do you have?

Answer 3

Aha! You said you use SuperServer?

Firebird 1.5 Classic Server vs. Superserver
SuperServer: No SMP support. On multi-processor Windows machines, performance can even drop dramatically as the OS switches the process between CPUs. To prevent this, set the CpuAffinityMask parameter in the configuration file firebird.conf.

So it uses only 1 core. You should use Classic Server (process per connection). Firebird has no true SMP support yet, will be in 3.0 version: http://tracker.firebirdsql.org/browse/CORE-775