18 July 2009

Common Sense isn't always common

I have been meaning to write about common table expressions (CTE), since this syntax (sugar, some say) explicitly implements hierarchies, and given the reactionaries out there pining for IMS and such won't be quiet. I first used them on DB2/LUW years ago. SQLServer 2005 added support, and now Postgres 8.4 has, too. Oracle has had its CONNECT BY syntax much longer. CTE syntax got added to SQL-2003.

I read various database sites and blogs; among my favorites is PostgreSQL.org. (Note to self; add to Good Stuff.) It has a rotating set of links, and today there is an article discussing CTE in postgres. Since I haven't got around to installing 8.4 yet, the work is done for me. Read it. The article compares using CTE versus functions, which is a neat idea in and of itself. They demonstrate that CTE can run faster than standard syntax on a HDD machine. Very neat.

CTE is germane to the main point of this endeavor, SSD multi-machines, since a CTE is just massive joining behind the scenes. You get the point. What would be somewhat inefficient on HDD, becomes trivial on SSD. One more reason to put 1960's datastore behind. Paul McCartney played on the marquis of the Ed Sullivan theater a few days ago, but he still looked as ancient as Ozzie Osbourne. Hierarchy is from the same time. Dump it. XML is IMS in drag; all sequins and rhinestones.

1 comment:

tikitodo said...

maybe the service of fix broke pdf better suits your needs? I have recently tested several data recovery applications and I think it is the easiest one