July 9, 2011, 1:11 p.m.
posted by blackhat
We can start off with some general tips.
SQL is a procedural language. Despite the confusion and outright lies about this point, it is a fact that an SQL statement's clauses are processed in a fixed order. And despite the set orientation of SQL, the DBMS must often operate on an SQL result set one row at a time. That is, if you're executing this statement:
UPDATE Table1 SET column1 = 5 WHERE column2 > 400
SQL's set orientation means the DBMS will determine that, for example, six rows meet the condition that requires them to be updated. After that, though, the DBMS must actually change all six of the rows—one row at a time.
The relational model is inherently efficient. Dr. Codd's rules and the subsequent work on normalization are based on the proven truism that mathematical foundations lead to solid structures. (Normalization is the process of designing a database so that its tables follow the rules specified by relational theory.)
Always assume you will do a query at least 100 times. That will make you ask yourself whether you want to use a procedure, a view, a trigger, or some other object and might also make you ask—If this is so common, has someone already written something like it?
And now, let's get started.