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