RE: Is there a good discussion of optimizations?

From: Guyren Howe <guyren@gmail.com>

>Most folks, in my experience, who use relational databases don’t really understand the basic theory or even more important the why - the philosophy - of what a relational database is and how to get the most out of them. I see a lot of folks trying to use SQL in an imperative manner - make this temp table, then update it some, then make this other temp table, etc...

>Anyway, I’d like to put together something that explains this. I would go into Codd’s original insight and how simple yet powerful it is. I’d like to discuss how if you stick to SQL, rather than forcing it into this imperative straight-jacket, the database can work out all the details so you don’t have to do the bad things.

Be sure you point out where SQL either fails or comes up short to give a

balanced view. To be fair, sometimes the reason people perceive SQL as failing

them (and why they go with the imperative process) is because they don't know

everything SQL does, or even perhaps their tool doesn't implement the whole

standard.

An example of this is that we have a report we're trying to write that I'd

like to think can be done in SQL, but I can't think of a way to do it. Yet,

if I do the base query and pull the data back into my application, I can do

the last bit with 3 lines of Perl very easily. The problem here revolves

around comparing a row of data to the previous row to know whether the data

changed "significantly enough" to keep the new row.

Another example is doing running totals. A couple of years ago I would have

said SQL can't do that. Now I know about the OVER clause, something that I

would categorize as somewhat obscure, I can do it as needed.

As Michael Lewis pointed out, large dataset can also cause you to choose not

to use SQL in 1 big statemet for everything (i.e. advocating the use to temp

tables). In some ways, using a CTE is a type of temp table, or at least I

view it as such. That allows a person to solve a problem in bite-sized chunks.

I will agree that optimization can do it better at times, but the code also has

to be maintained as well – a balancing act.

To continue the above, the idea of using a sub-select in a FROM clause is

generally not thought of by new learners. So experience is also a factor.

I think your idea is a good one, but I do hope you present that SQL can't

solve everything ... else why do we have plpgsql. :) You’re correct though,

SQL isn’t used as much as it should be in many places.

Kevin