filtering data from db

Question

I've taken over a web application that someone else built. It's a pretty simple application that queries ALL records from a db and displays them in a list. They've also provided a way to 'filter' the data. When the user enters in data into a "filter by" text box, via Ajax the system re-queries the database to only select records that begin with that data. it's basically doing a

    "select * from widgets where name like '" + input_from_user + "'"

Now we've gotten to the point where there are too many records being returned initially and I'm mullling over the follow design decisions.

1. Is the filter really working well? Meaning, if we leave everything as is and query all records, as the user is filtering the data, why do I need to requery the database? Should I just "somehow" use the data I already have and filter that? For example, couldn't I somehow use javascript / jquery to filter client side?

2. If we want to introduce some sort of paging mechanism where the SQL statement itself is a)sorting by a certain field, b)limiting the results to the next 100 sorted records, do I still need a filter? I don't think so. It's either paging, or filtering in my mind, especially if I limit the amount of data we show at a time to 5o -100 records (something small that will fit on one screen).

Here's what I was thinking of doing (pseudocode)

SELECT *
FROM MyTable
WHERE SomeColumn > LastValue
ORDER BY SomeColumn
LIMIT 100

I wanted to throw it out there to see if anyone had any comments / suggestions / wanted to tell me that I'm way off base here.

Thanks.

Answer 1

1. Filtering should be done in the database. Databases are really good at filtering results using the WHERE clause. One should always govern that amount of data being queried, paged and returned. You don't want millions or rows going across the network and you don't want the database to page a result set that has a million rows as it is a lot of needless work, so put a limit on the total amount of records SQL should query and page and how many records should be returned on a page.

2. Paging can also employed. Two types of paging, offset and keyset. Offset is typically easier to employ, but keyset performance wise is more consistent. One should research both before picking a solution as they work slightly differently. Either can be acceptable. Paging should be done in the query as well. One can write offset and/or keyset queries by passing in a few extra parameters like page size, number of records to page, etc.

In conclusion, page and filter at the database. Decide what the high water mark is for the number of records to filter and what the page size is. If the high water mark is hit, send the users the page back and tell them to narrow the query result by adding in more specific search criteria as the query is returning too many results for the current filter.

One should be able to pick a page size and filter high water mark that meets the needs of the application without crippling the network and database. Some examples are page size of 100 with max records of 10,000, or maybe 10 and 1,000.

If these values never change, consider caching the entire result set so the data is fetched just once on start up.

This will keep your DBA and network admin happy and will be a positive experience for your users.