Jump to content
Sign in to follow this  
Stéphane Wierzbicki

Can TFDQuery (SQLite) work as fast as TFDmemtable ?

Recommended Posts

Hi,

 

I need to import some Excel data, modify it and export it into another format.

Input fields defs, output fields defs and script can be changed ar run time (lets say by a smart end user.

Data is persisted into an SQLite database.

 

Basically here is what I did: 

  • Creating Input and Output TFDMemtable at run time (based on fields defs previously defined
  • Injecting XLS data into Input TFDmemtable objects
  • Passing Input and output TFDmemtable objects to scripter object
  • Running script (cannot use DML for various reasons)
input.beginbatch;
Output.beginbatch;
try
 input.fisrt;
 while not input.eof do 
 begin 
   output.append;
   output.fieldbynem('blabla').AsString := input.fieldbyname('llkll').AsString;
   Output.post;
   input.next;
 end;
finally
 input.endbatch;
 Output.endbatch;
end;
  • Persisting TFDMemtable data into SQLite database thanks to TBatchmove
  • Exporting data into another format

 

This works pretty well (few secs for 10000 rows)

I decided then to remove all those TFDmemtable objects and replace them with TFDTable objects. Now it take 15x the time to complete. This is true specially on the script side.

 

To speed up the things I tried to wrap my script on a transaction

 

    try
      FLocalConnection.StartTransaction;
      fScripter.Execute;
        if FLocalConnection.inTransaction then
          FLocalConnection.commit;
        Log('Script Executed')

    except
      on e: exception do
      begin
        if FLocalConnection.inTransaction then
          FLocalConnection.rollback;
        Log('An error occured while executing the script: ' + e.Message);
      end;
    end;

I  tried to set TFDTable with this options

 

    FetchOptions.RecsMax := 500000; // Sample value
    ResourceOptions.SilentMode := true;
    UpdateOptions.LockMode := lmNone;
    UpdateOptions.LockPoint := lpDeferred;
    UpdateOptions.FetchGeneratorsPoint := gpImmediate;

And at least I tried to work with an :memory: database and pooled connection

 

  // close fdmanager
  FDManager.Close;
  // check if connection definition exists
  condef := FDManager.ConnectionDefs.FindConnectionDef(ConDefName);
  if not Assigned(condef) then
    condef := FDManager.ConnectionDefs.AddConnectionDef;
  // add connection definition
  condef.Name := ConDefName;
  condef.Params.DriverID := 'SQLite';
  condef.Params.Database := ':memory:';
  condef.Params.Pooled := true;
  condef.Params.UserName := '';
  condef.Params.Password := '';
  condef.Params.Add('SharedCache=False');
  condef.Params.Add('LockingMode=Normal');
  condef.Params.Add('Synchronous=Off');
  condef.Params.Add('JournalMode=Off');

  condef.Params.Add
    ('SQLiteAdvanced=auto_vacuum = 1;page_size = 4096;temp_store = MEMORY');

  condef.Params.Add('LockingMode=Normal');
  condef.Params.Add('CacheSize=60000');
  condef.Params.Add('BusyTimeOut=30000');
  condef.Apply;
  FDManager.Active := true;

 

Nothing changed so far.

 

Do some of you have any recommendation ?

 

 

 

Edited by Stéphane Wierzbicki

Share this post


Link to post

1) If you need the speed, then dont use TFDTable. At least, use TFDQuery.

2) Consider to use TFDBatchMove with SQLReader from XLS / ODBC data source. And SQLWriter to SQLite DB.

Share this post


Link to post

Speed difference is so huge between TFDTable and TFDQuery. I naively thought that TFDTable was the right choice when dealing with tables.

 

Do you know why there is such speed differences ? 

 

Anyway thank you for your help.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×