Misuse Of Sql

There are cases where StructuredQueryLanguage is possibly overused, meaning that another tool could do some or all of the job simpler. Note that this does not necessarily imply that relational theory is limited, but rather is a focus on SqlFlaws. Maybe another RelationalLanguage could handle it better, for example.



Signs that you are overusing SQL:


One of my bigger complaints about large SQL statements is that it is hard to inspect the intermediate results, something I miss from my ExBase days. There are few or no debuggers that let you see the results of intermediate queries, for example, at least not with a lot of copy and paste. SQL as implemented lacks human-interactive DivideAndConquer. Perhaps this is a drawback of declarative techniques. But it would be fairly easy to add intermediate inspection to something like SMEQL/TQL (SmeQl) because one can optionally use named references instead of nesting to combine queries. (Inspection may slow down processing because the optimizer may have to create an actual intermediate table when inspection is requested instead of apply shortcuts to possibly combine steps.)

TutorialDee supports this via the WITH construct (see page 45 in TheThirdManifesto 3rd edition for an example), which introduces names to reference subexpressions. Internally, it can be implemented as syntactic sugar, and does not inhibit optimisation.
All of these MisuseOfSql cases seem to be issues of code clarity and refactoring. Massive duplication? Parametrize into procs. Redundant subqueries? Pre-select into a temp-table.

Peculiar that there's no "Misuse Of" Cee, Perl, Smalltalk, Erlang, Ruby, Lisp or Scala pages, no?

-MichaelWilson

"Parametrize [sic] into procs" often has a significant negative impact on automated optimisation and therefore usable performance; it rarely does in the general-purpose programming languages you've listed. Pre-selecting into a temp-table is unpleasantly complex, bypasses certain automated optimisations, may introduce concurrency issues, and may significantly degrade performance.

There are no "Misuse Of" pages for C, Perl, Erlang etc., because bad programming in general-purpose languages is, for the most part, obvious and well-understood amongst professional programmers. We don't need a page to know that, for example, monolithic programs should be avoided in favour of use of various code modularisation techniques. MisuseOfSql (plus SqlFlaws), though, is somewhat less understood.
See also: ReportsSmell, PerniciousIngrownSql
CategorySqlProgramming

EditText of this page (last edited October 13, 2010) or FindPage with title or text search