0

Scenario:
application will search in database almost all columns across about 8 tables/views for defined string and return all rows containing that string (will return collection of objects or JSON). At beginning database(DB2 v6) will contain about 100k records

Which approach is better:

  • Loading these tables and mapping them to objects in application memory, searching using these objects.
  • Mapping tables from database to embed database in application(JAVA DB in this case), searching using embed database.

First one is fast but downside is that application will need memory(first prototype went up to .5 GB), the second one is slower and also is heavy on memory.

Has anyone tried something similar or can point to better solution please share it.

  • 2
    Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. Also see How to Ask – gnat Feb 5 '16 at 9:40
  • Can you not just query the database? Any way that pulls ALL the data from the database will be slow since you have to read the data from the DB, transmit the data, then parse the data. – RubberChickenLeader Feb 5 '16 at 15:57
  • Did you see: programmingzen.com/2011/07/19/… – NoChance Mar 6 '16 at 23:04
1

SQLite has a full text search extension. I suggest you take a look at the documentation. It could optimize the searches because it would create an inverted index that maps from each unique term or word that appears in the dataset to the locations in which it appears within the table contents. Such searches should be quicker than just issuing SQL comparisons using the LIKE operator inside every column of every table. This approach is compatible with the second option you are considering.

| improve this answer | |
  • Unfortunately database is db2 v6 – TOM-12 Feb 5 '16 at 22:05
  • @TOM-12: DB2 has full-text search available. See if your installation has it. – Blrfl Feb 5 '16 at 22:28
  • unfortunately from db2 v9 which is no way we can use – TOM-12 Feb 6 '16 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.