Hack 70. Combine Your Queries

Not only can you combine related queries, but you also can merge unrelated queries. If these queries are always used together anyway, combining them can result in a measurable performance improvement.

The time it takes for your program to send a query to the database, and for the database to send the results back to your program, is a significant factor in making your database-driven web sites seem quick and responsive. When you have optimized your queries to filter in the database system [Hack #8], you may discover that both the database and the program seem to spend significant periods of time waiting on each other to respond. This is particularly true if your programs and database server run on different machines.

One of the best ways to minimize round-trip time for multiple queries is to stop using multiple queries and to use only a single query. You will save yourself the round-trip network time for all the separate queries you didn't use, plus a good deal of CPU time by not executing the code to build, send, run, receive, and decode the query information for the queries that were avoided.

The tricky part about combining queries is that your program will eventually have to split the single result set back into separate rows from each combined query involved. The queries involved may have different numbers of columns with different types.

Consider a typical web-driven site, with page content in one table (shown in Table 9-3) and a message of the day in another table (see Table 9-4). A CGI program makes queries to load the page needed (in this case, index.html) and to load the message of the day (motd), and then combines them to form a pretty page layout. It still needs two queries, which add up to twice the round-trip time required for one query.

Table 9-3. The page table

Content	Page name
hello	index.html
Hia	index.html
page2	p2.html
Index	contents.html

Message

The site will be down on Tuesday.

A typical approach might be:

SELECT pagename,content
FROM page
WHERE pagename = 'index.html'

or:

SELECT message FROM motd

You could instead combine them, with sets of columns for each different query, and UNION it all together by using NULLs where necessary:

SELECT pagename,content,NULL,'page'
FROM page
WHERE pagename = 'index.html'
UNION
SELECT NULL,NULL,message,'motd'
FROM motd

Your client program can split them up again by using column 4 to indicate the source table (motd or page). Of course, the query is also really difficult to read, but it may be worth it if you need the speed or if you run these queries often.