Hack 8. Filter Rows and Columns

Don't just download the whole table. Use filters on row and column information and minimize database traffic.

Programmers of a certain disposition try to avoid the database as much as possible. They learn a single, simple SQL statement and use it in all circumstances. The one statement they need is SELECT * FROM t. Grab the whole table and treat it as a giant array. No need to learn much SQL, right? The problem is that this approach is inefficient.

Let's say you have a web site that keeps all of its pages in the database. It's great for content management and version control, but each page request means getting data from the database. The table itself has two fields: pagename and content. How can you do this efficiently in, say, Perl? The name of the page you want to display is stored in $p:

my $sql = "SELECT pagename,content FROM page";
my $sth = $dbh->prepare($sql);
my $rsh = $sth->execute( );
while (my $row = $sth->fetchrow_hashref( ) ) {
 print $row->{content} if ($row->{pagename} eq $p);
}

The preceding code suffers from linear performance degradation. As more pages are added, more information is sent between the database server and the program. The code has to filter out all of this.

You should really be filtering in SQL so that you get just the results you need. The following example is much better, but it still has some problems:

my $sql = "SELECT pagename,content FROM page where pagename = '".$p."'";
my $sth = $dbh->prepare($sql);
my $rsh = $sth->execute( );
my $row = $sth->fetchrow_hashref( );
print $row->{content} if $row;

It is possible that $p could be set to something unexpected. For instance, rather than index.html it could be index'html. This would cause the query to fail with a syntax error.

If you don't fix this issue, not only do you have a potential syntax error, but also you may be leaving yourself open to an SQL injection attack [Hack #48].

A placeholder is a way to put a program variable into an SQL statement so that SQL injection cannot work. These are also known as bind variables and query parameters. The common way to use a placeholder is to simply put a ? where you want your variable contents to appear, and then pass the variable as a parameter of the execute API call. So, the preceding code becomes:

my $sql = "SELECT pagename,content FROM page where pagename = ? ";
my $sth = $dbh->prepare($sql);
my $rsh = $sth->execute($p);
my $row = $sth->fetchrow_hashref( );
print $row->{content} if $row;

You can have more than one parameter; just supply them in the order in which the ? shows up in the $sql string. You can gain additional improvement by query caching. Each time your code runs, the same SQL statement is run on the database, no matter which page is requested. The page being requested is passed separately as a placeholder variable.

Data filtering in the database server will give you a faster response time and will lower data bandwidth requirements between the server and your program. It is faster for other reasons as well. One is indexing [Hack #9].

Many database servers cache recent queries in their parsers and may even cache the query plan. If the query is the same every time, the SQL engine can avoid preparing the query each time it is run.

Placeholders are not restricted to just Perl. The languages that are shown connecting to the database in "Connect to SQL from a Program" [Hack #2] have similar concepts. Here are some examples of inserting the program variable myParam into a placeholder.

2.2.1. Perl

my $sql = "SELECT cname FROM atable WHERE cname = ?";
my $sth = $dbh->prepare($sql,$myParam);

 

2.2.2. Java

PreparedStatement sql = 
 con.prepareStatement("SELECT cname FROM atable WHERE cname = ?");
sql.setString(1, myParam);
ResultSet cursor = sql.executeQuery( );

 

2.2.3. Ruby

sql = db.prepare("SELECT cname FROM atable WHERE cname = ?");
sql.bind_param(1,myParam);

 

2.2.4. C#

You don't use ? for the placeholder in C#. Instead, you name the placeholder and put an @ character in front of it:

SqlCommand cmd = new SqlCommand(
 "SELECT cname FROM atable WHERE cname = @myCname");
cmd.Parameters.Add("@myCname", myParam);

 

2.2.5. PHP

In PHP, placeholders depend on the database libraries you are using to connect to your database. Wrapper libraries are available, including ADOdb (http://adodb.sourceforge.net), which can make your life much simpler. Here's how you'd execute a statement with ADOdb:

$DB->Execute("SELECT cname FROM atable WHERE cname = ?",array(myParam));

Without ADOdb, you should look at specialized functions such as mysql_stmt_bind_param or oci_bind_by_name.

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

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