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.

asked Feb 5, 2016 at 9:38
3
  • 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 Commented Feb 5, 2016 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. Commented Feb 5, 2016 at 15:57
  • Did you see: programmingzen.com/2011/07/19/… Commented Mar 6, 2016 at 23:04

1 Answer 1

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.

answered Feb 5, 2016 at 15:41
3
  • Unfortunately database is db2 v6 Commented Feb 5, 2016 at 22:05
  • @TOM-12: DB2 has full-text search available. See if your installation has it. Commented Feb 5, 2016 at 22:28
  • unfortunately from db2 v9 which is no way we can use Commented Feb 6, 2016 at 13:00

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.