I have a DB that stores some complex objects of my app. When my app starts I load the objects into list and store them on the RAM.
But sometimes I would like to search for an objects that fulfills conditions (filtering), even the conditions are complexes.
I was always wrote a for loop (sometimes a very complex one) for this thing, that runs on these objects. But I heard recently that DB and SQL queries have a lot optimization mechanisms (dependent the DB company), and its works faster then regular search.
If I understand it correctly, it's better to write a SQL query that search these objects and returns objects IDs. And then by them IDs, get the relevant objects.
Its look like the ultimate solution for searching objects.
But I'm a little bit doubtful about this idea, because all the time we learned that DB is slow, and it's better to load the objects to RAM and work on them…
So I have two questions:
1) Are the queries actually more faster then regular search (for complex searches)?
2) Maybe its true for many many objects (thousands), but is it true for few objects (tens)?
Best Answer
In general, the database search will always be faster - especially if you're searching on indexed elements. That said, communicating with the database is loads slower.
Just how fast/slow depends on many variables particular to your situation. Measure, then optimize. In general, if you have few items or need to do many simple lookups, look to RAM. If latency is a concern, look to RAM or some non-traditional database. Otherwise, databases are specialized for querying and will often be absurdly faster than naive searches.