Section 12.4. Thinking in Business Tasks


12.4. Thinking in Business Tasks

Before focusing on one particular query, don't forget its context. Queries executed in loops are a very bad indicator of the quality of code, as are program variables with no other purpose than storing information returned from the database before passing it to another query. Database accesses are costly, and should be kept to a minimum. When you consider the way some programs are written, you are left with the impression that when their authors go shopping, they jump into their car, drive to a supermarket, park their car, walk up and down the aisles, pick a few bottles of milk, head for the checkout, get in line, pay, put the milk in the car, drive home, store the milk into the fridge, then check the next item on the shopping list before returning to the supermarket. And when a spouse complains about the time spent on shopping, the excuses given are usually the dense traffic on the road, the poor signposting of the food department, and the insufficient number of cashiers. All are valid reasons in their own right that may indeed contribute to some extent to shopping time, but possibly they are not the first issues to fix.

I have met developers who were genuinely persuaded that from a performance standpoint, multiplying simple queries was the proper thing to do; showing them that the opposite is true was extremely easy. I have also heard that very simple SQL statements that avoid joins make maintenance easier. The truth is that simplistic SQL makes it easier to use totally inexperienced (read cheaper) developers for maintenance, but that's the only thing that can be said in defense of very elementary SQL statements. By making the most basic usage of SQL, you end up with programs full of statements that, taken one by one, look efficient, except perhaps for a handful of particularly poor performers, hastily pointed to as "the SQL statements that require tuning." Very often, some of the statements identified as "slow" (and which may indeed be slow) are responsible for only a fraction of performance issues.

Brilliantly tuned statements in a bad program operating against a badly designed database are no more effective than brilliant tactics at the service of a feeble strategy; all they can do is postpone the day of reckoning.

You cannot design efficient programs if you don't understand that the SQL language applies to a whole subsystem of data management, and isn't simply a set of primitives to move data between long-term and short-term memory. Database accesses are often the most performance-critical components of a program, and must be incorporated to the overall design.

In trying to make programs simpler by multiplying SQL statements, you succumb to a dangerous illusion. Complexity doesn't originate in languages, but in business requirements. With the exclusive use of simple SQL statements, complexity doesn't vanish, it just migrates from the SQL side to the application side, with a much increased risk of data inconsistency when the logic that should belong to the DBMS side is imbedded into the application. Moreover, it puts a significant part of processing out of reach of the DBMS optimizer.

I am not advocating the indiscriminate use of long, complex SQL statements, or a "single statement" policy. For example, the following is a case where there should have been several distinct statements, and not a single one:

 insert into custdet (custcode, custcodedet, usr, seq, inddet) select case ?          when 'GRP' then b.codgrp          when 'GSR' then b.codgsr          when 'NIT' then b.codnit          when 'GLB' then 'GLOBAL'          else b.codetb        end,        b.custcode,        ?,        ?,        'O' from edic00 a,      clidet bT where ((b.codgrp = a.custcode         and ? = 'GRP')        or (b.codgsr = a.custcode            and ? = 'GSR')        or (b.codnit = a.custcode            and ? = 'NIT')        or (a.custcode = 'GLOBAL'            and ? = 'GLB'))    and a.seq = ?    and b.custlvl = ?    and b.histdat = ? 

A statement where a run-time parameter is compared to a constant is usually a statement that should have been split into several simpler statements. In the preceding example, the value that intervenes in the case construct is the same one that is successively compared to GRP, GSR, NIT, and GLB in the where clause. It makes no sense to force the SQL engine into making numerous mutually exclusive tests and sort out a situation that could have been cleared on the application side. In such a case, an if ... elsif ... elsif structure (preferably in order of decreasing probability of occurrence) and four distinct insert ... select statements would have been much better.

When a complex SQL statement allows you to obtain more quickly the data you ultimately need, with a small number of accesses, the situation is completely different from the preceding case. Long, complex queries are not necessarily slow; it all depends on how they are written. A developer should obviously not exceed their personal SQL skill level, and not necessarily write 300-line statements head on; but packing as much action as possible into each SQL statement should be a prerequisite to improving individual statements.

Tuning SQL statements before improving programs and minimizing database accesses means that you are ignoring some of the major means of tuning improvements.




The Art of SQL
The Art of SQL
ISBN: 0596008945
EAN: 2147483647
Year: N/A
Pages: 143

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