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

Table 9-4. The motd table

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.

SQL Fundamentals

Joins, Unions, and Views

Text Handling

Date Handling

Number Crunching

Online Applications

Organizing Data

Storing Small Amounts of Data

Locking and Performance

Reporting

Users and Administration

Wider Access

Index



SQL Hacks
SQL Hacks
ISBN: 0596527993
EAN: 2147483647
Year: 2004
Pages: 147

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net