in reply to Re: (OT) Why SQL Sucks (with a little Perl to fix it)
in thread (OT) Why SQL Sucks (with a little Perl to fix it)

It is that I am asked to do things with it that can't be expressed efficiently in a relational manner.

I think it would be interesting to have a better description of such tasks. At the very least some our SQL deficient readers will get an idea of things that dont work well in relational databases.

One such example that comes to mind is storing heirarchies. Relational semantics and directed acyclic graphs dont mix nicely. :-)

---
$world=~s/war/peace/g

  • Comment on Re^2: (OT) Why SQL Sucks (with a little Perl to fix it)

Replies are listed 'Best First'.
Re^3: (OT) Why SQL Sucks (with a little Perl to fix it)
by tilly (Archbishop) on Dec 14, 2005 at 00:34 UTC
    The example that I am most commonly interested in is maintaining running totals. For instance given a table with payments and receipts, determine what our lowest account balance was.

    It isn't all that hard to write that in standard SQL. But the solutions that you get won't execute very quickly...

[reply]

      Thanks, thats an interesting example. Just out of curiosity why do you calculate such totals from an accounts-receivable table? Wouldn't it be easier to have a seperate totals table and update it every time a new record was added? Either in a transction explicitly do two updates, or use a trigger on the accounts-receivable table that does it automatically....

      ---
      $world=~s/war/peace/g

[reply]
        I wasn't part of the design decision for that, so I can't give a good answer for why it wasn't done that way.

        However I can think of at least two reasons why it wasn't done that way.

        The first is because orders are generated in one database, and then accounts receivables are dealt with in another. You really want those two to have limited communication - that way if the front end database is compromised your financials information is still safe. However that design makes your suggested interface much more difficult. (Not impossible, I can think of workarounds, but more difficult.)

        The second is because the people building the system were building the transactional system first, and then the reporting needs came after. At the time that the transactional system was designed and built, the need wasn't obvious. At the time some reporting needed to be built it was easier to deal with what was rather than to insist on redesigning what was already out there and working. After all we do succeed in running the requested query...

[reply]
      The canonical answer is to use a view on the accounts_receivable table. The problem is that your accounts_receivable table is too normalized for adequate performance. You have to create a denormalized view in order to get what you're looking for.
      My criteria for good software:
      1. Does it work?
      2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]
        Yes, I know how to do it conceptually in pure SQL. I also know how to do it reasonably quickly using Oracle's analytic extensions. I prefer the latter solution since it runs at an acceptable speed without having a DBA get involved in changing the schema.
[reply]

In Section Meditations