FireDac in memory dataset filter before iterate?

[Mark Williams 14]

I need to work with a potentially large dataset in memory. It has an integer field called "document_category_id". On first loading the table it is sometimes necessary to update records in the table from one document_category_id to another. This might need to be done for several, but not all of the various document_category_ids.

 

Trying to work out what is the most efficient way of iterating.

 

Would it be more efficient to filter on the requored document_category_ids first and then iterate or to iterate on an unfiltered table?

[Mark Williams 14]

Ran some tests on an in memory dataset of 25K records.

 

Without filter 31ms to iterate the whole dataset (whilst also querying each record to see if it matched the required "document_category_id)." .

 

Filtering first took twice as long to filter and iterate  even though the resulting dataset was only 30 records. That sort of answers my question. But would this still hold true with say a million plus records?

[Jim McKeeth 104]

Ultimately testing and looking at the code is your best way to know the correct answer, but here is some advice:

 

Without the use of an existing index, when you filter, it will need to iterate through the entire dataset to apply the filter. If you think about it that makes sense. The only time filtering (or indexing) provide value is if you are going to use the results more than once. With an in-memory dataset that is usually not the case.

 

It will really depend on your use case. If you will only ever do a single iteration pass then filtering or indexing don't make sense. You might consider where you are pulling the data from and what that process looks like. Maybe you can update it during that process.

[Brian Evans 105]

Usually start with the bottom question/answer from: TFDMemTable Questions - RAD Studio (embarcadero.com) especially the use of BeginBatch / EndBatch. 

Edited May 21 by Brian Evans