Jump to content
Mark Williams

FireDac in memory dataset filter before iterate?

Recommended Posts

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?

Share this post


Link to post

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?

Share this post


Link to post

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.

  • Like 1

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×