Search Postgresql Archives

RE: Is there a good discussion of optimizations?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



From: Guyren Howe <guyren@xxxxxxxxx>

 

>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

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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux