Web Development – Filtering Data from Database with jQuery

databasejquerypaginationsortingweb-development

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.

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?
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.

Best Answer

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.
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.

Related Solutions

Web Development – Client-Side vs Server-Side Filtering in Web Apps

Some client side filtering issues:

Cache invalidation. Server side filtering will always operate on up-to-date data, while client caches will have to be updated when data changes. This may be very complex depending on which correctness guarantees you want to have. The future developer will curse this solution. If it doesn't work properly the customers will curse this solution.
Query language. Your server already supports SQL out of the box (assumed because of the "sql" tag), and it is very different from JSON + JavaScript, the most obvious candidate for a client side querying solution. There might be a sufficiently generic client side data structure + querying solution out there that you can slap it onto the web site with barely any effort, but the alternative is invariably going to result in more (and more complex) code than using server side querying. The future developer will curse this solution.
Code change propagation. Many web sites have fallen into a trap with client side code where a refresh of the page does not download the latest code. Users then will likely not be able to query until they clear the browser cache. The customers will curse this solution.
Network transfer size. You may have a small database right now, but transferring all of it to every client on a regular basis is going to be quite slow and costly in the long run. The sysadmin will curse this solution. Any customers on slow connections will curse this solution.
CPU/battery use. JavaScript uses more CPU (and therefore battery) than plain text. And no, you will not have the resources to optimise the code. Customers with laptops and slow machines will curse this solution.

If your database really is small, and you create a server side filtering solution which is anywhere near sane, the filter round-trip time should be in the low milliseconds. This is fast enough that nobody will notice, and you avoid a whole lot of extra complexity and possible grief.

C# – Is it possible to cache data on a REST service that returns paginated data

Isn't it possible to simply cache the thousands of records somewhere to avoid calling the database each time

Yes, but it is the exact opposite of being stateless, which REST is. You're trying to go against the main ideology of RESTful services.

Can it be done? On a technical level, if you really want, sure. But this is a case of the XY problem. Your proposed solution (Y - caching the data) is a workaround for the actual problem (X - paginating the query to lower the amount of data returned).

Storing the full dataset only solves half of the problem. If the user only looks at two pages on average, that still means that you retrieved 20 useful rows, and 9980 useless rows (assuming 10,000 rows total). That's means that 99.8% of the retrieved data is never used. That's still a big waste of space.

Furthermore, you'd have to cache a separate dataset for every unique combination of filters. Which means that you're likely going to have a ton of duplicate data in memory, because a given record may appear in several differently filtered datasets.
Assuming you have a significant amount of concurrent users and are caching all their requests, I'm starting to think that it'd be more efficient for you to simply store the entire table in-memory once, just to get rid of the duplicates (I'm not saying that you should do this, I'm just pointing out that caching everything is going to cause more problems than it's likely to solve).

There are many reasons why you shouldn't be trying to cache the unpaginated data as a solution to a pagination problem.

I know I could modify the query that Dapper uses so the pagination filter goes down to the query level, but I find that's too much burden to do

Well if you refuse to paginate the dataset, then you obviously can't expect a paginated dataset. But then you're excluding the correct solution in favor of an easier unRESTful solution; which is likely to create technical debt for the future.

Have a look at this pagination with Dapper example. You'll have to change the subquery to your own query.

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY InsertDate) AS RowNum, *
          FROM      Posts
          WHERE     InsertDate >= '1900-01-01'
        ) AS result
WHERE   RowNum >= 1 // *your pagination parameters
    AND RowNum =< 10  //*
ORDER BY RowNum

All you would need to do to implement this for yourself is figure out the row limits (1 and 10 in the example). These can be found by using simple calculations.

Note: I'm assuming that the pageNumber is 1-indexed, because that's generally how the UI portrays it. pageSize is 10 in your case.

var row_limit_lower = ((pageNumber - 1) * pageSize) + 1;
var row_limit_upper = (pageNumber * pageSize) - 1;

Figure out which page number is being requested. Based on that (and a page size, which I infer is always 10 for your application), you can calculate and implement the needed pagination in the query itself, thus preventing the retrieval of many unnecessary rows.

Best Answer

Related Solutions

Web Development – Client-Side vs Server-Side Filtering in Web Apps

C# – Is it possible to cache data on a REST service that returns paginated data

Related Topic