SiteCrafting, Inc.

I always enjoy trying to do my work creating web applications using the fewest number of database queries possible. It's kind of a pride thing, I guess. That and I suppose it makes to improve an app's performance and reduce the load on a server. If you're into that kind of thing. ;)

A technique I use subtitutes the temptation of using queries whose results call queries and instead uses only two.

Something that commonly comes up involves a needing to learn something specific about every item of a query result. While looping through the first set of results it's easy to just run a query each time to get what I need. Problem comes when your apps scale up from result sets in the double digits to those in the thousands or more. Running thousands of queries just to get what you want is a little selfish, don't you think?

When I create dynamic reports for clients that show row upon row and column upon column of totals pulled from the four corners of a vast datascape the number of queries needed can skyrocket. One technique I'm rather fond of employing does everything needed in two queries. Let's play pretend here and say we're working with a grocer who wants to see a report of how many of different kinds of fruit have been sold everyday for the last month.

The first query might look like this:

...And might return results looking like this:

For each of the items in that result set, you could run a query to get the total sold for each day of a month. For the shortest month of the year with 28 days, this would mean 112 queries! You can see how this could easily get out of hand.

Instead, try a second query that gathers up the dates for every fruit sale during the month, en masse. You can then dump these results into an array who's indexes match the name for each type of fruit. For each fruit you can put another array with a slot for each day of the given month that stores the total number each was sold on a given day. It may end up looking like this:

From here it's pretty easy to loop through the first set of data and match on the second set using those spiffy associative indexes.

This is a pretty rudimentary example but it's important for me to be mindful of such techniques as I build bigger and bigger apps. Like I mentioned, when creating reports sometimes the data is being mined from many different areas of the database all with different calculations that would make a single query nearly impossible. By finding a common bit of data like an ID and leveraging some arrays you can keep the number of queries down.