RE: Is there a good discussion of optimizations?

From Guyren Howe
>Actually, I’m mostly going to talk about the relational model, rather than SQL. Our industry seems to always settle for third-best, and SQL is the worst of all the examples of this. The world desperately needs a good relational database based on a better query language — datalog, for example.

>I put up with SQL so I can use the relational model, and I think that understanding SQL has to start with that.

I can understand that that. :) Yet SQL is the interface/lens we most use to interact with an RDBMS.

>>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.

>Actually, Window functions might be "advanced", but are certainly not obscure. Your example sounds like it’s trivially solved with LAG().

Fair enough. Perhaps it would be better to say that many don't go to the depths to learn
window functions until forced to, and a lot of problems can be solved without them. I can
say that would be true for me and those I work with.

Thanks for the tip on LAG()! It's not a function I've used before (which makes it a great
example for the previous para).

>>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.

>This appears to be good advice with SQL Server, which I’m coming to learn has a fairly poor query optimizer. But I would have thought Postgres’s optimizer would usually use a temporary table where appropriate.

I'm sure Pg's optimizer does its work better than I would manually, but that's not
always the point. For some I've talked to, thinking relationally and in sets is hard.
I'll even admit that while I think I do it pretty well, there are times I have to stop
and really consider what I'm doing. If you're used to thinking about how to solve a
problem with a computer language, most of them are imperative and that mindset can be
hard to get out of. So your "philosophy" approach is a well-aimed arrow in many ways, IMO.

Also, don't forget about "maintenance". Solving something in bite-sized chunks allows
for easier changes in the future, as well as understanding by newcomers -- generally speaking.

>Curious to hear if that’s wrong.

Depends on what version. In the earlier versions of Pg, CTE's were a fence and there was
no crossing over. That fence now has gates in some places in the latest versions from
what I read.

>>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.

>An important consideration will be when relational is inappropriate. My biggest goal, though, is to get folks to understand how much relations *can* do — far too many devs in my experience don’t use the power of SQL because they don’t understand it.

>Thanks for taking the time to give me this feedback.

I appreciate the efforts of teachers in all forms. I hope your project goes well and the feedback has some use.

Kevin
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.