Joe Celko's SQL for Smarties: Advanced SQL Programming

Rating:
Summary: Empirical Data?
Review: It's been several years since I read this book, but do I not remember seeing comparative statistics. The premise of "fewer passes through the data means faster queries" sounds quite logical. I was on a development team in the late '90s that followed the techniques in this book to the letter.

Characteristic functions looked really "cool" at first. The ability to, essentially, have different WHERE clauses for each column in the SELECT clause is impressive. I like and sometimes recommend this technique for reporting. However, for at least one OLTP development effort, this technique was a speed bump.

By the middle of the initiative, we had a lot of very complicated and slow code. Debugging takes much longer in the land of "DECODE(ABS(SIGN(TO_CHAR( ))))." Database calls were unacceptably slow after production-level volumes of data were interrogated during integration testing. There were two reasons for the performance degradation.

Before function-based indexes were available (as they have been starting with Oracle8i), applying ABS, TO_CHAR, SIGN and any other functions automatically lead to full table scans. Indexes that are not sorted according to DECODE-like functions will simply not help find the data faster. Keep in mind that function calls consume CPU time as well; use a utility like TKPROF to see how much (custom functions are especially expensive). With suppressed indexes and thousands of function calls per SQL statement, it is easy to see why characteristic functions are generally not performance enhancing.

This book offers useful some reporting techniques that should generally be avoided in OLTP systems. If you decide to use characteristic functions as an alternative to conventional WHERE clause lines, strap a scalability test harness onto the database before issuing a single ABS function call.

Rating:
Summary: Anthony Mays
Review: It's too bad I cannot rate this book as a ten. A word to the wise: Celko's books cover the ANSI STANDARD SQL 92 (mainly). If the database you are working hasn't implemented the ANSI Standard SQL 92 ... then how could his examples compile? If your database implements a vendor flavor to the standard ... how could his examples compile on your database? No excuses ... please ... for not knowing your database, because Celko plainly and clearly explains the standard (ANSI SQL 92 for example) he is writting for. It is the readers job to know their database and perform some interpretation and translation in order to get some of the examples to work. Celko writes generically for the standard. If you need a specific database book on SQL call your vendor ... I am sure they will refer you to something ... and if they cannot ... FIND ANOTHER DATABASE VENDOR!

Hey JOE, where can I get my hands on a pure theoritical SQL book that really explains the MATH behind SQL. If anyone knows, [e-mail] is my email address.

Rating:
Summary: A must have...
Review: Joe Celko is a very interesting author. His approach to teaching by using puzzles gets the mind thinking. He does not keep you guessing too long. I like his sense of humor as well.

I consider this book a must-have for all Database Administrators and developers...

Rating:
Summary: Great book
Review: Joe Celko knows SQL. He may make some typos as a writer but make no mistake... this guy knows what he's talking about. He knows the language and the mechanics of the major database engines. But more importantly, he understands the logic of it all. Some of his SQL moves are simply brilliant.

I develop enterprise applications and am called upon now and then to create some very challenging SQL. Celko has bailed me out several times. I have the book as a reference source right next to my database reference manuals. He is full of ideas and often times I am looking for an idea, not a recipe.

This is a book for people who have a good knowledge of SQL. It will stimulate your thinking and programming. It isn't a book for beginners. In the right hands, SQL for Smarties will really add some sizzle to your SQL coding.

Brianne Elizabeth Corbett - IBM Global Services

Rating:
Summary: Excellent book
Review: Lots of examples of complicated/advanced SQL. Not for people who need to learn SQL

Rating:
Summary: typos wreck this potential masterpiece
Review: Lots of interesting ideas in this very well written book. However, ideas are where it ends, in my experience. The example code is full of typos, which makes the book less useful as a tool. For example, check out Celko algorithm 2 for median (it won't even compile).

A list of corrections or a second edition are badly needed. Too bad, it has the makings of a great book.

Rating:
Summary: Promising ideas suffer from poor editing
Review: Mis-prints and typos in example code will stop you dead.

Rating:
Summary: Full of academic exercises and toy examples
Review: My first problem with this book is its errors. ... there must be one on every page. There's way, way too many typos for a book like this. Half the examples won't work without corrections.

My second problem is that the book is rife with academic exercises that have little application in the real world. Code that uses cross joins will not perform well against even moderately sized data sets. Most of the examples make great magazine pieces, but fall short of anything that's actually useful in the real world.

Third, Celko likes to use syntax that has been implemented on paper only. For example, he uses set operations that no vendor has yet implemented (and probably never will since they became part of the ANSI standard years ago). A fair number of the examples in this book use code that will not run on any DBMS - even after you clean up the typos....

In sum, this book was a good idea, it's just a poor implementation. Celko needs to do some real DBA work and write from that experience. Saying "different DBMSs implement things differently - see your vendor docs" on every other page doesn't help anyone.

Rating:
Summary: Not sure of when/where this book would prove useful...
Review: My usual approach to CS books is to read them from cover to cover. I don't try the examples, don't run the code, don't do the exercises.

What I try to do is getting a "coarse memory map" of what the book teaches, so when I encounter a problem in my line of work I can say "well, I remember something about it in ZZZ" or "YYY had a chapter on this". Obviously I tend to favour cookbooks on purely abstract theory. When I bought Joe Celko's book I hoped to get just this: an SQL cookbook with easily appliable tips and techniques to a vast range of recurring SQL problems.

In my opinion, the book tries to cover too much, and ends up unfocused. There are a lot of examples, but they look a little too vague to me (and as others noticed, they seem to be plagued with typoes). Often the method works for the toys tables used to introduce the problem, but I'm left wondering what would happen if the data were a little more complex.

There are chapters devoted to subtle problems in SQL standards, numerical precision, query optimizations, but the general message seems to always boil down to "well, different vendors do things differently, so check out your product docs".

The book hints to powerful SQL techniques to solve some recurring problems: it devotes two chapters to modeling trees and graphs in RDBs, but again, the discussion is not really complete, and even if it proves that trees can be represented and manipulated in standard SQL (without STARTS WITH or other proprietary dialects) a lot of important things are missing, like what to do if you need to represent different trees in the same table, instead of a single tree. They also suffer from the "toy example syndrome" I explained before.

The book devotes some pages to design constraints and strategies, but does not discuss how to present object oriented classes in standard RDB. Maybe it's just me, but I think this should have been included, considering the widespread use of OO or quasi-OO languages in the industry.

To sum it up, the book demonstrates that the author has a very good knowledge of standards, is intimate with at least a dozen different commercial RDBS, and has a tendency to write intricate SQL queries to show that "it can be done in SQL, no needs to resort to procedural language". This is fine for his magazine columns, but I wonder if and when I will ever consult the book to solve a real-world problem in my job.

Rating:
Summary: I'm a little underwhelmed...
Review: Not so much unimpressed at Joe's knowledge, which is impressive indeed: the book reads a lot more like a teaching text than most technical books.

But there are things in here which may lead astray some who have already done things that Joe advises strongly against. I will concentrate on one example: In chapter 3 "Numeric Data in SQL", under the heading "Generator Functions" (e.g., IDENTITY, AUTO_INCREMENT) we get this doozy: "This is a horrible, nonstandard, nonrelational proprietary extension that should be avoided whenever possible". Just a statement, no reason whatsoever provided for it, because I guess he assumes we know some "math rule" or something behind why it is such a bad idea. Now, we must think for a minute why one uses such a data column. In my own case, I have a table called Parts that contains parts from several different companies. So, I guess Joe would have me make a composite primary key from PN and CompanyID. But, wait a minute, that complicates matters when I need to have a foreign key reference to the Parts table, and, oh by the way, just what is CompanyID anyway, maybe some other composite key, or some goofy "rule-based" (can you say TRIBAL KNOWLEDGE) thing? You can't seriously believe that "ALFKI" is a better key than,say, 33. What happens when I get a new customer named "Alfred Kiplinger", and have to change the "rule" that I came up with for defining the primary key? See the problem? You're not going to remember the ID anyway, because the rule will be broken at some point. I also happen to think that a part number (to give one example) should be changeable. So, I don't make PN the primary key (because you should NEVER change a PK), I simply have the database generate one for me. What am I missing here? It was not explained to me in this book, it was just a blanket statement of preference, put across like a hard and fast rule.

But then come the contradictions. In the very next chapter on temporal data types, we get a very long paragraph on "key generators" and how they need to be designed to eliminate or minimize identical keys (I kid ye not!). He talks about elaborate hashing algorithms, the server system clock, random number generators, and how pseudorandom numbers are not usually a problem since the cycle size can be "hundreds of thousands or even millions of numbers". Huh? Amazon has 50,000,000 customers! I'm sure they wouldn't be too happy if "only" every millionth one had the same id! No mention in this entire section on GUID or UNIQUEIDENTIFIER, which won't repeat forever in the known universe!

Then there is seeming randomness to the topics introduced. I think I work with a guy that's a lot like Joe, but man, can it be hard to follow the "why" of what he is talking about! I usually figure it out about two days later when I'm sitting at my desk working on something completely different. Here's one example: We go from an incredibly long section on Domain Key Normal Form, with all of its calculus functional dependency stuff ("A determines B, therefore if CA = B, &c, &c, &c....."), to a paragraph right after this about normalization, and how a Students table should not have "Student data and also bowling scores". But come on, that's DB101, not Math335!

Bottom Line: The reason I gave three stars to this book is that I think I misread its intention. I believed it to be a book for someone who knew SQL, and wanted to become more advanced in SQL. Now that I ponder the title, however, I believe that it means "OK, here's a book for you scientific math types out there who want to apply your math degree to learn SQL", i.e., SQL for smarties, not for non-degreed dummies like myself. That, to me, is exactly how the book is written, and it probably succeeds against that yardstick.