0

How would you approach when implementing search functionality for a data that is stored in local database?

  1. Prefetch whole list from database and then perform filtering on that cached list (have to created a backup variable), when user types

  2. Make database query every time user types

There are 200-300 rows in the table. Each row contains 10-15 columns.

PS: I always used to perform filtering on a cached list, mostly because I was required to show full list anyway, so the whole data set was already prefetched.

Let's assume you don't have to show the whole list in the first place and only show when user start typing, is it better overall to perform database query in this case?

I'm just not sure. I saw a colleague doing query.

And yeah I'm too lazy to test performance of db querying. Visually there is almost no lag though.

Every opinion is welcome!

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rinat Diushenov
  • 1,215
  • 7
  • 15

1 Answers1

0

"2. Make db query everytime users types." is the best option. Because for the number one you have to worry about both space and time complexity. With efficient query in sqlite you can fetch data quickly and this approach is cleaner too.

WS Ayan
  • 451
  • 4
  • 9
  • So you are suggesting that db querries not expensive at all? – Rinat Diushenov Oct 12 '21 at 12:30
  • For first approach: 1. run query to select all the data 2. return them in a data list 3. search the list with some logic. for second approach 1. write an efficient search query. 2. give the result back Which one easier to manage? – WS Ayan Oct 12 '21 at 12:46
  • Both of them are fairly easy. – Rinat Diushenov Oct 12 '21 at 15:13
  • It's not a matter of difficulties. You need two separate operations for one functionality in first approach, which defies single responsibility principle. That was my point. When table column changes, or data is needed from multiple tables.you change your fetch query and then again change your search logic. It just makes your code harder to understand and manage by other devs. – WS Ayan Oct 13 '21 at 06:57