move data retrieved by ObjectManager to Firedac memory tables

[Kounalakis_Dimitris]

HI,
I am having a number of tables (10-15Mb size) that I want to move to memory tables because they are in use most of the time and by different threads.
I was thinking of two possiblities:

a) Moving them to FireDac TFDMemTables and copy cursor to different threads using the same data. What is the most efficient way to copy data from a Tlist retrieved by Aurelius to a TFDmemTable? I know that the following line does the work but does it exists something more efficient?

FDMemTable1.CopyDataSet(AureliusDataset1, [coStructure, coRestart, coAppend]);

b) Using a long-living TAureliusConnection with a ':memory' native SQLite as follows:

IDBConnection1:=TSQLiteConnection.Create(':memory');

and copy data from an other temporary AureliusConnection/Objectmanager that connects to the mysql/postgres database to get the tables.

Here, the questions are:

1. Can this native IDBConnection1 be used by different threads simultaneously by different TobjectManagers?
2. How can I use this SQLite memory database from IDBConnection1 to run sql queries through firedac (optionally by different threads)?

The reason for not using just arrays, it is the need for using indexes
Thank you in advance

[wlandgraf (Wagner Landgraf)]

I never benchmarked code that transfer data from AureliusDataset to FDMemTable. You would have to measure and profile to know which method is the most efficient.

Regarding your question 1: It's safer to protect the connection using critical section. You can also do that for a global manager, or global objects.

Regarding your question 2: I didn't understand it. Can you please elaborate?

[Kounalakis_Dimitris]

I have benchmarked different cases yesterday.
I can tell that in small number of records returned Aurelius does not have real difference from using directly firedac components. For many returned records(15000 in my case), aurelius needs near double the time (2450ms) than the time needed by an TFDquery (1340ms)
In all cases the fetch mode of the TFDquery was set to fmAll.

Just because in my case, I am loading a number of table in memory that they are read-only, I found that the fastest way is to save a TFDmemtable with binary format in Savetostream and same this stream to a record in a table with blob field. I can read this record with Aurelius as fast as with TFDquery and load the structure and data with loadfromstream in TFDmemTable. I found that by setting the sfbinary instead of sfauto in loadfromstream, it needs half of the time.
This is the fastest procedure of all I tested

Concerning the second question, checking the code and documentation, I found that SQLite is not threadsafe and sqlite connection's handle is in private sections, so this complicated case is abandoned

My problem now is the more memory used by firedac in ftstring fields. But I can tune that with TFDFormatOptions.InlineDataSize

I am thinking if Tdictionary could play the role of the indices in aurelius's Tlist return without FDMemTables but I am not sure it worths to spend so much time
Thank you

[wlandgraf (Wagner Landgraf)]

Thank you for the feedback, @Kounalakis_Dimitris. It's not clear to me, though, if this ticket is solved, and if not, what are the pending issues?