Is it faster to requery from SQL or loop in an ArrayList and filter data in Java?

Question

I hava an ArrayList that got filled from an SQL query and contains all the rows of a table.

If I want a subset of this ArrayList (say WHERE column1 = x)
Is it faster to remake an SQL connection and fetch a query with this condition, or loop through the arraylist and filter the data ? (considering an arraylist of 1000 rows)

If the loop through the arraylist is faster, until what size is it still appliable ?

Thank you

Answer 1

It should be always faster to do this in Java. Java filtering of a memory array is as fast as, or faster, than SQL. And you also have to consider the overhead of connecting to the database, sending the command, and decoding the answer.

For really complex queries it could be simpler to have this done by SQL, and it probably scales better regarding to database size (think one million rows: are you going to keep them all in memory?). Also, the SQL query optimizer might have more information on column cardinality and be able to perform more efficiently, and therefore faster, overall, than your merely-faster-on-a-check-by-check-basis Java code.

On the other hand, also consider maintainability of the code. Some things are better left to SQL even if they're a mite slower that way.

Answer 2

If you have everything in memory already, my guess is that filtering the list will be faster than making an inter-process call to a database, especially for such a small amount of rows.

But it seems strange to have the full list in memory in the first place. Databases are fast, and are there to return the data you want when you want it. Caching the contents of such small tables in memory probably makes your application more complex for no significant speed gain. I would try keeping the application as stateless as possible unless you have a performance problem and you prove that caching the table contents solves the problem.