Searching/Filtering Database Results in App?

[MyMindsAvatar]

Hi,

Apologies if this is not the right place for this question.

I'm just wondering what's best, any help would be appreciated. Say you have obtained a list of results from a database (doesn't matter which one at this point, but a good one at least), and you now want to provide a search feature (and/or a filtering method). Is it better to perform the search/filter yourself in the app's code or use the database instead? What if there's the potential for a lag in database communication (e.g. remote server)?

Many thanks,

Richard ~

[kbw]

Let the database handle the data.

[MyMindsAvatar]

Thanks. I thought that would probably be the case, but I'm still a little worried about the lag. If the data is already there (returned from a previous query) and the search/filter simply covered what's already there, couldn't you code the search yourself and return the answer faster, especially if there was a lag in the database? Or would all the database optimisations (even just out of the box) surpass all of that?

Thanks again. My actual real knowledge of databases and programming is still in development.

Many thanks,

Richard ~

[kbw]

If you're on a large system, then the database might be on a set of dedicated boxes, performance tuned to your query and storing millions of rows of data. In that context, it's a no brainer.

On small systems, such as an IOS device using sqlite3 it becomes more contentious. But the principle remains the same. Let specialised components do their job and only bypass layers when performance tuning.

[MyMindsAvatar]

Wonderful, thanks for the advice. I would guess this advice applies to incremental searches as well (find-as-you-type searches), yes?

[kbw]

Yes.

SQL databases are notoriously bad at this sort of real-time filtering, but some databases are extremely good at that sort of thing.