Can TFDQuery (SQLite) work as fast as TFDmemtable ?

[Stéphane Wierzbicki 45]

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 May 11, 2019 by Stéphane Wierzbicki

[Dmitry Arefiev 109]

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.

[Stéphane Wierzbicki 45]

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.

[Dmitry Arefiev 109]

TFDTable produces many additional queries to mimic TTable and to maintain Live Data Window:

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Browsing_Tables_(FireDAC)#Live_Data_Window_Mode

[stijnsanders 38]

My own clean SQLite 3.dll wrapper is here: https://github.com/stijnsanders/TSQLite

[Edwin Yip 154]

Not sure if it suites your needs, but SQLite supports in-memory database with ':memory:' as the special database file name. It's independent of which SQLite wrapper you use.

Edited May 12, 2019 by edwinyzh

[Stéphane Wierzbicki 45]

@edwinyzh the culprit was the use of TFDTable instead of TFDQuery objects.

 

Speed is now almost the same. 

 

[Dmitry Arefiev 109]

Using Array DML, or as I wrote above - TFDBatchMove, you can get even better speed.

[Stéphane Wierzbicki 45]

@Dmitry Arefiev yes I know 🙂

I'm so far pleased with the current speed. I'll check if I can use the Array DML within TMS scripter. 

 

You wrote a very efficient library... Kudos!