Eli M In case you add records in a SELECT-TFDQuery with Append/Post then, yes, these records are cached in memory. If you can make sure that the query returns only few or no records then close and open the dataset after a certain amount of records added. This will clear the cached data. Adding multiple records is better done with an INSERT-TFDQuery using Params. Perhaps you can make use of the TFDBatchMove component?
I had seen the INSERT-TFDQuery here https://stackoverflow.com/questions/45975298/out-of-memory-when-inserting-records-in-sqlite-firedac-delphi but it seemed counter intuitive. I'll give it another shot. I'm using TFDBatchMove in a third project where I'm processing lots of rows as well and my problem with it over there is the static typing keeps killing the inserts. Maybe the data is dirty (which it seems like it is) but even setting up all the fields specifically and not letting it auto detect them didn't help.
On the IOS side I solved the problem by switching to a CSV file (from an XML WSDL file) which dropped the file size 3X. Then I parse the CSV file manually with TStringList and use the Array DML from the StackOverflow suggestion to insert the data into SQLite. I tried TFDBatchMove but it was taking 30-60-90 seconds to process the CSV file and TStringList does it in a few seconds.
Eli M In case you add records in a SELECT-TFDQuery with Append/Post then, yes, these records are cached in memory. If you can make sure that the query returns only few or no records then close and open the dataset after a certain amount of records added. This will clear the cached data. Adding multiple records is better done with an INSERT-TFDQuery using Params. Perhaps you can make use of the TFDBatchMove component?
ReplyDeleteI had seen the INSERT-TFDQuery here https://stackoverflow.com/questions/45975298/out-of-memory-when-inserting-records-in-sqlite-firedac-delphi but it seemed counter intuitive. I'll give it another shot. I'm using TFDBatchMove in a third project where I'm processing lots of rows as well and my problem with it over there is the static typing keeps killing the inserts. Maybe the data is dirty (which it seems like it is) but even setting up all the fields specifically and not letting it auto detect them didn't help.
ReplyDeleteOn the IOS side I solved the problem by switching to a CSV file (from an XML WSDL file) which dropped the file size 3X. Then I parse the CSV file manually with TStringList and use the Array DML from the StackOverflow suggestion to insert the data into SQLite. I tried TFDBatchMove but it was taking 30-60-90 seconds to process the CSV file and TStringList does it in a few seconds.
ReplyDelete