[GLLUG] Learning SQL

[Jason Green]

On 3/27/06, Andy Lee <ALEE at courts.mi.gov> wrote:
> I have to disagree with the comment that you shouldn't worry about SQL and just hide it behind an abstraction layer. My experience has been that those generic abstraction layers are harder to use than writing good optimized custom code. I do agree that you don't want data access code floating around with your presentation code, but, you can see substantial performance improvements if you really learn SQL. I've seen some nasty code from people who only know how to do simple selects, and have to code loops with multiple hits to the DB where a join would have worked. I've also been able to take queries from requiring 5+ minutes execute, to under 5 seconds just by properly ordering the clauses, and knowing what indexes to add. If you are going to be doing database programming, it is worth your time to learn how to do it right, especially when the language in question is so dang simple!

I've read something along the lines of this phrase before: "If you
have to loop your SQL queries, you're not thinking hard enough".
Point being, put the complicated pieces of your code inside the SQL
statement or on the server as a trigger or view - the server will be a
lot more efficient at updating the data than your code will be.

Quick and stupid example:  I want to update a record with information
from a web form about a customer.  Here's the stupid code:

UPDATE tbl_customer SET first_name = '$fName';
UPDATE tbl_customer SET last_name = '$lName';
UPDATE tbl_customer SET phone_number = '$phoneNum';

That makes calls to your server 3 times.  Smarter code:

UPDATE tbl_customer SET first_name = '$fName', last_name = '$lName',
phone_number = '$phoneNum';

Now, that was very quick and dirty, but it should get the point
across.  Just about anything you want to do with the data in your SQL
server is accessible via SQL commands.  Sometimes it takes a little
digging to figure out which command and what syntax, but it's
definitely do-able.

As long as you're not designing seriously big applications which
require heavy use of server-side scripts/triggers/views, etc., then
try to make your application database-agnostic.  Meaning, don't put a
bunch of MySQL-specific functionality in it if there's a chance that
10 years down the road, you might want to switch to
SuperdyDuperMasterSQLServerFighter2ChampionshipEdition server.  SQL
wrapper frameworks like ODBC, ADODB (the php version, not the MS
stuff), etc., can come in very handy.