Is it better to pull in an entire dataset if there's going to be a lot of ajax filtering done on the page?

Question

I basically have this web page where you can narrow your search results by adding and removing certain filters on the left hand side.

The content being pulled from the database is a list of properties, which can be filtered by:

• Type ( Apartment, Camp, Ranch, Fishing Camp, Hotel, Lodge )
• Activities ( Camping, Kayaking, Hunting )
• Location ( Idaho, Illinois, Indiana )

So everytime I click on one of the valid filters on the left side, it adds that to the filtering criteria which is used in an ajax request. This joins the minimum number of tables necessary and returns the results.

So if I play around and activate/de-activate I can easily do around 20-30 ajax requests in about a minute ( You are only allowed to filter one of each type at a time unless you de-activate though ).

Question: I'm wondering if I should have instead pulled all the possible properties and just filtered them instead of querying the database per each ajax request, which has at least 2-3 LEFT JOINs per transaction.

There are going to be less than 100 properties, I'm not dealing with hundreds or thousands.

Answer 1

Can you re-write your ajax request so that instead of returning a list of properties, it just returns their ids? Does this cut down on the number of LEFT JOINs?

Then you could loop through these ids, calling another ajax request to get the properties you don't already have cached from earlier filtering.

Answer 2

Absolutely: Pull the full dataset and filter on it. Let's say each of your Ajax requests takes 100ms. Already, you could be talking about introducing 2-3 seconds of latency into your application when, with your small dataset, you don't really need it.

If you're narrowing down a list of results, "object oriented CSS" can be your friend here, too. Think of expressing the various filter-able properties as classes, and adding the user-selected filters to their parent, then using CSS to show or hide relevant results.

For example, if you have all of your results as LIs under a parent UL, <ul class="idaho apartment"> could indicate the user filtered on all Idaho Apartment items while the following CSS would hide the others:

.apartment .camp, .apartment ranch ... { display : none; }
.idaho .illinois, .idaho indiana{ display: none; }

With this approach, you have a single server hit and rely on the browser + JavaScript to handle the rest (and since you're using Boolean comparisons - a result either has a property or doesn't - it could work well for you). Regardless, even selecting nodes manually when you filter will feel snappier than sending off a request-per-filter-operation. Plus, now you'd have the ability to cache that single database query, potentially saving you a bunch of overhead if you need to run complex queries.

Serve the full set!