Search code examples
sqldatabasehibernatedata-access-layerdao

Getting records for comparing: one by one or in one go?

i hava a list of products and i must compare each product in the list with original ones which exist in database. which would be right way?

  • get all records from db(keep in arraylist), compare each one, update

or

  • get one from db, compare it, update, get next

there are many joins in db schema, thus second way seems unsuitable. on the other hand products table contains over 5000 records and i have doubts about keeping all of them(can be reduced to ~500 by some queries) in memory.

Solution

  • The best way is somewhere between the two options:

    Get all the rows returned from the database in one query, but process each row as you read it, so that only one product is in memory at a time.

    Such an approach is completely scalable.