I am using Firedac to create SQL Lite connection on a local .db file. I am using:
What I want is copy all data or tables from my SQLite database file on memory and then execute SQL queries on it.
I've read that I can use mMemTable or other datasets but all information regarding this is more confusing than helping me out.
I want to achieve this because I have to run "heavy" queries (thousands of queries inside a for-loop) but without sacrificing so much speed and resources. Currently I have 5 query.open("SELECT * ....") inside a very big loop, making my code very slow. I suppose this derives from many query.open() stuff.
If I have a copy of my SQLite data on memory and then run queries on that dataset, it would increase my code speed drastically.
I have implemented this on another part of my code, where I used limited amount of data taken from SQLite file inside sorted lists, and then I iterated over my lists. Performance increased over 300% after replacing query.open() inside that big loop.
I cannot use the same method as mentioned above, because I want to run SQL queries from my "SQL in Memory" to fetch specific data with complex criteria.
So anyone can help me out with the above issue? Maybe a small and "clear" tutorial regarding this?
It seems that my question is still not answered. I will try to be more specific with another example of code in order to understand the situation. I am still trying to achieve what I was telling above. I want to copy data from my SQLite database on memory, DISCONNECT and then run SQLite queries on in memory database. It is supposed to work with TFDQuery, TFDLocalSQL, TFDConnection.
I am creating and fetching data from SQL file using "conn" connection and "query". Then I activate
try
// connection with local SQL file to fetch data from
query.Connection := conn;
query.Open('SELECT * FROM CAMPAIGNS');
query.FetchAll();
// I've put SQL text because FDQuery1 must me active and SQL text must be
// included
FDQuery1.SQL.Text:='SELECT 1';
FDQuery1.Active:= true;
// the following line of code, is supposed to copy data and structure
FDQuery1.CloneCursor(query,true,true);
finally
conn.Close();
query.Free();
conn.Close();
end;
// it seems from showmessage that records exist inside TFDQuery
showmessage(FDQuery1.RecordCount.ToString);
// when I execute the following query, it tells that such table does not
// exist
FDQuery1.Open('SELECT * FROM CAMPAIGNS');
A case of the blind leading the blind, I'm still trying to figure this out myself, but I think you need to use a TFDLocalSQL as conduit between your TFDMemTable and TFDQuery. For more info, try http://docwiki.embarcadero.com/RADStudio/Rio/en/Local_SQL_(FireDAC)