SQL is SWELL »

I'm building out a few status-like reports for various projects right now (you might even call them TPS reports). There's nothing too special about these type of pages. Really they are mostly just a collection of record counts from various tables.

When I do this type of work, I notice that I often end up doing a lot of re-factoring as the page explodes to include/display more and more data. The problem is often due to my lack of designing what I really want the page to display before I start coding it (I'm especially bad at 'design a plan first' for my own projects and usually dive right into code).

So I often start out building one or two SQL queries to grab the initial data I want. Then I think of something new I want to add, and so I build a new query for that. I repeat that process over an over until suddenly my page is one huge mess of code with tons of little queries and I think to myself 'why am I doing it this way? My page is getting too slow and unmanageable!'

That's when I usually sit down and re-factor my code. Often times I find that I can take many of my little queries and wrap them all up into one or two larger queries. This gives me the benefit of less code and a lot less round trips to the database server (a VERY good thing).

This is especially true when I have a page of a lot of SQL count type statements. But don't take my word for it, use the examples I outline below to test out why re-factoring like I do really is better!

And just because I've been spending a lot of time in both Ruby and PHP as of late, I'm going to give you examples for both languages. (Two for the price of none! Awesome.)

To be complete and thorough let's start our example Ruby code with some the usual database set up stuff. Here I'm referencing the Active Record gem, defining the table classes I'll be working with, and making a connection to my SQL Server database. Both of our Ruby examples below reference this set up code (ie. make sure you include it at the top of your ruby file if you really are trying to run these Ruby examples).

require 'rubygems'
require_gem 'activerecord'

class Tablea < ActiveRecord::Base
  set_table_name "tablea"
end

class Tableb < ActiveRecord::Base
  set_table_name "tableb"
end

ActiveRecord::Base.establish_connection(:adapter => "sqlserver", :mode => "odbc", :dsn => "myDSN", :database => "myDatabase", :username => "username", :password => "password", :port => "1433")

t1 = Time.now
temp = Tablea.count
temp2 = Tableb.count
t2 = Time.now
puts "#{temp} records in tablea"
puts "#{temp2} records in tableb"
puts "Took: #{t2 - t1}"

t1 = Time.now
temp = Tablea.find_by_sql("select count(0) mycount, 'tablea' as type from tablea UNION select count(0) mycount, 'tableb' as type from tableb")
t2 = Time.now
puts "#{temp[0].mycount} records in tablea"
puts "#{temp[1].mycount} records in tableb"
puts "Took: #{t2 - t1}"

<?
$conn = odbc_connect('myDSN','username','password');
$t1 = microtime();
$sql = "select count(0) mycount from tablea";
$rs = odbc_exec($conn, $sql);
$tableacount = 0;
while(odbc_fetch_row($rs)) {
  $tableacount = odbc_result($rs, "mycount");
}
$sql = "select count(0) mycount from tableb";
$rs = odbc_exec($conn, $sql);
$tablebcount = 0;
while(odbc_fetch_row($rs)) {
  $tablebcount = odbc_result($rs, "mycount");
}
$t2 = microtime();
echo $tableacount . " records in tablean";
echo $tablebcount . " records in tablebn";
echo "Took: " . $t2 - $t1 . "n";
?>

<?
$conn = odbc_connect('myDSN','username','password');
$t1 = microtime();
$sql = "select count(0) mycount, 'tableacount' as type from tablea UNION select count(0) mycount, 'tablebcount' as type from tableb";
$rs = odbc_exec($conn, $sql);
$tableacount = 0;
$tablebcount = 0;
while(odbc_fetch_row($rs)) {
  $val = odbc_result($rs, "mycount");
  $type = odbc_result($rs, "type");
  switch(true) {
    case($type == "tableacount"):
      $tableacount = $val;
      break;
    case($type == "tablebcount"):
      $tablebcount = $val;
      break;
  }
}
$t2 = microtime();
echo $tableacount . " records in tablean";
echo $tablebcount . " records in tablebn";
echo "Took: " . $t2 - $t1 . "n";
?>