developer.* Blogs - Thinking in Sets - Comments

If only I had found this months ago ...

Angus McDonald — Mon, 21 Aug 2006 18:32:15 -0700

I recently had the opportunity of turning a huge VB payroll processing batch job into a set of SQL Server stored procedures. Whilst it took a long time to work out, the use of sets to do the processing resulted in a MUCH faster batch job (>90% of time was saved). However a young .Net programmer wandered onto the team some months later and had great difficulty understandin why we hadn't just moved to VB.Net as he understood it to be MUCH faster at this sort of processing.

If we had stuck to a set of loops he would have been quite right, but because we changed the code to process sets of data at a time it resulted in a batch job that as it got larger got faster per employee record because the processing time was based on the set not the individual records.

Anyway, I wished I'd found this article back then, it sure would have helped explain it to him!

Thanks,
Angus

Code v Sets

Edward G Nilges — Thu, 14 Apr 2005 18:18:31 -0700

"Code" and "sets" are both forms of WRITING. But at the top of most organizations, the ideal is that the CEO shall SPEAK, like Pharaoh, saying "let it be written: let it be done".

Invisible scribes shall write down what old Pharaoh says and other guys shall do the task.

Now, suppose one of the scribes says, based on his knowledge of code and sets, "Pharoah, sir, what you have said is BS because it is a null set".

Do you suppose that old Pharoah will say, "oh, sorry, Hank?"

No, Hank-the-scribe is dragged away from his workstation and set to making bricks with straw with the Children of Israel.

Speech is thought more trustworthy than writing, and Hank may have entered the query wrong.

Which is to say that while conservatism locates the problem of bureaucracy in the lower levels, where self-interested scribes create unauthorized systems, the problem may be that the high concentration of the written leads to a regression at the top, in Air Force One, where undocumented decisions are taken in a way that depends upon, but denies, the material basis in writing.

Heinlein thought the world of Starship Troopers would be controlled by "Symbolic Logic" but he also saw that it would need a warrior caste with oral traditions. But what is invisible to him is that if you specialize and select a caste you are creating the invisible Guardians at the top, and the irrationality of the whole (which Orwell presents in 1984).

The irrationality of the whole creates MIS cynicism. My uncle worked hard for many years at the IRS but told me some real horror stories about their continual vain attempts to get tax systems under control...much less modernized.

This is common knowledge. But what's invisible is that the whole can be rational if it has a humanistic orientation. Note that in all the talk about how "Social Security is in trouble" one doesn't hear anything about their data systems for in fact since 1935 their data systems have worked effectively...perhaps ultimately because what's not to dislike about some old person not having to live on Puppy Chow.

Whereas the IRS and its data systems have to confront Negation from day one where Negation is the permission given in the Ninth Amendment for Americans to pay as little tax as humanly possible.

[In China my wages are withheld. When I asked about filing a return, I had to explain what a return is. It seems that if you pay too much here in withholding, you should be honored to have so contributed to the historic struggle of the people's Leninist state. OK, OK, I am honored.]

[Seriously, during the 1970s I consistently overpaid income taxes to the US because I didn't want to be bothered and figured it was in a good cause.]

I agree that the set is a powerful concept. But note that set theory has its own paradoxes:

SELECT * FROM tableOfAllSets WHERE Not isAmemberOfItself

Also, note that even in constructing SQL you have to use various rules of thumb such as not using * for the field list and NOT sending the query from a client over and over again.

Even if you resolve to program in pure logic you are still embedded, in Heidegger's sense, in the real world.

"Oh, I see your problem, Mr. President. You have sent the query from your Visual Basic program to the server and when you ran it against the live census data base you hosed everything."

[The frightening idea of a President using Visual Basic is not complete science fiction, for Brian Kernighan, now a Princeton professor, uses Visual Basic in his nonmajor computer science classes to teach the best and brightest, he hopes, that programming is not for bozos.]

Data systems remain material and embedded in the real world, in a way that Lerry Lessig shows in Code and the Laws of Cyberspace.

Which means that their study has to start with the normative and not the descriptive. In day one of computer science, it should be announced that computer science is not about getting rich but about the just apportionment of resources using technology.

In other words, I would generalize, somewhat, Bjarne Stroustrup's combined normative and descriptive discussion of C++, in which he describes C++ in parallel with best practice.

The what and not the how

Edward G Nilges — Thu, 14 Apr 2005 17:48:10 -0700

Data base authority C. J. Date, in a book entitled "The What and not the How" reminds us that his original motivation with SQL and the relational model was to replace "epistemology" (what and how we know) with "ontology" (what is).

The opposition between epistemology and ontology is my own. But it may capture the fact that "programming" is the procedural construction of an epistemological path to knowledge whereas nonprocedural SQL declares ontologically that this is the SET which we want.

The trouble is that nearly all SQL environments are incapable of making anything but the most primitive inferences about things like data types.

C. J. Date's original vision was, I think, that highlevel administrators would "tell the computer what they want" in a purified language.

I will merely leave the reader with the image of the current President doing this.

But, you say, the formation of the SQL query is a clerical activity: the Prez merely says to the data base administrator, CJ-Boy, y'all tell me how many terrorist suspects is a-taking flying lessons:

SELECT name,address FROM terroristDB WHERE isASuspect AND takingFlyingLessons

Needless to say, any data base admin worth his salt knows that there are any number of ways of forming the query and designing the data base, so the President's problem becomes not engagement with the world outside the Oval Office, it becomes the self-reflexive task of managing the bureaucracy.

Harry Truman was perhaps the first President to realize that FDR's New Deal had created a bureaucracy which he had to manage as part of his job, and, an explanation of the numerous disasters of the Bush administration is that the bureaucracy is out of control. A strong individual like Clinton could control it but it appears that Bush has been highjacked by unprincipled players and the complexity of the apparatus.

This goes right down to the misuse of Powerpoint (or systems like Powerpoint) and SQL (even including "advanced", proprietary, classified and hardened versions of SQL running at "full military power") the mastery of which in a bureaucracy necessarily includes the ability to tell the boss what she wants to hear.

SELECT peace,justice FROM theEntireMess WHERE NOT aChildIsLeftBehind?

Yeah, right: but MIS cynicism based on engagement is part of the problem.

Build Your Own .Net State Department?

Well, in March 2003, while writing my book, I contacted Geoffrey Robertson (QC), an international lawyer and asked him if my peace group could use international law to stop the war in Iraq. But the technology of spinmeistering, precisely because it embeds the Internet, outpaces the Internet just enough.

Furthermore Microsoft's CEO supports the war which makes .Net sort of the dark side.

But the PROBLEM is thinking in Star Wars terms. Star Wars romanticises the problem.

Take a look at more declarative languages like Haskell, for you are most certainly on the right track.

Query: Is SQL Code?

Rob MacGrogan — Thu, 14 Apr 2005 08:57:57 -0700

Thanks for helping to clarify the terms I was failing to use, Dan. The terms procedural and declarative would have helped me make my point better. And I think I can agree that all of the "stuff" that makes up an application (save the data) could be considered "code", and what I referred to above as code might better be described as procedural code.

I often forget about the SELECT FROM SELECT approach. I've got to remember to use that one. I have often used subselects (i.e., SELECT * FROM myTable WHERE myId NOT IN (SELECT id FROM otherTable), but must admit that I find this construction goofy and suspect that there may be an inefficiency lurking in there somewhere.

Here's another good one. Did you know that in T-SQL you can create a function that returns a table? Thus you could end up with SQL like this:

SELECT * FROM dbo.constructSomeTable() ORDER BY firstName

You can join and apply a where clause and all that. This is actually a pretty useful feature in certain cases.

Far be it from me to crack on the hybrid declarative/procedural languages. I used to work as a Progress developer, for goodness sake! And the application that I bragged so much about above was all based on stored procedures and it used logical structures and (gasp!) cursors. In defense of my use of cursors, I'm only cursoring through my 300+ rules and then applying each one to the 300,000 other objects in batch. Thus, I only cursor where it's least costly.

The point I wanted to make about cursors is that they are very attractive to procedural coders, but that they are not always the best approach and are often used ineffectively and inefficiently. I believe that the great Celko himself has made this point on occasion.

Usefulness Acknowledged

Andy Tegethoff — Thu, 14 Apr 2005 08:38:41 -0700

If it ever comes to a McConnell-esque professionalization (did I just coin that verb?) of Software Development, I think set mathematics and it's application to data should be a large part of the core cirriculum. While I hardly claim to have doctoral-leve understanding of the subject, I do know SQL and I completely agree with the point you make, Rob.

In particular, nested SQL like you mention with the INSERT SELECT FROM statement can be incredibly powerful compared to "one at a time" procedural methods.
Similarly, table aliasing using SELECT statements instead of actual tables can do amazing things as an alternate form of subquery, i.e.:

SELECT T1.columnA, T1.columnB, T2.*
  FROM table1 T1,
       (SELECT a,b,c
          FROM table2
         WHERE [condition]) T2
 WHERE [condition]

This latter device is something I had not been exposed to very much until my current gig.

And while it's true that a procedural mindset can pollute applications of a hybrid language like T-SQL or PL/SQL, I don't think you can underestimate the power available in the efficient combination of both approaches. Often, a single SQL statement can (as you illustrate) save scads of work. This is particlarly true when the procedural route would involve large amounts of network back-and-forth.
But on other occasions, the ability to procedurally control SQL is invaluable. An example of this would be Dynamic SQL constructed inside a stored procedure, or branching logic to determine which of a group of SQL statements to execute.

Set-Based Ramblings

Daniel Read — Wed, 13 Apr 2005 20:43:41 -0700

Great post, Rob, and I share your fondness for the power of SQL. Like you, I very much enjoy sometimes setting aside my hammer and using a wrench instead. It's nice to have a full toolbox.

For the sake of discussion I would, however, like to follow the tangent you resisted in your post. I am not on board with the restriction of the definition of "code" to just procedural code. I'm no computer scientist, but I like to think of languages as falling into at least three groupings:

procedural, within which I would include machine, structured, OO, and aspect oriented languages (though my limited understanding of AOP might be preventing me from seeing that AOP would not fit under the "procedural" label)
declarative, which I would use to label languages such as SQL and XSLT
hybrid, including languages such as PL/SQL and T-SQL, which mix the procedural and declarative paradigms (though XSLT and straight SQL do this also, in that both XSL and SQL can call out to functions that contain procedural code)

I'm sure some might disagree with my groupings, and I'd love to learn more by discussing it with anyone who cares to. That said, I like having examples of all three of these categories in my toolbox. In particular, the power of SQL to operate very efficiently on sets makes it ideally suited for solving certain classes of problem. I also recently had an epiphany about the usefulness of XSLT in certain situations.

As for the scope of the definition of "code," I am happy keeping the definition broad, including all of the following:

stored procedures
configuration files
the metadata associated with specialized tools and middleware like enterprise application integration (EAI) and message queueing
report writer files, like those used by Crystal Reports or Business Objects
the *entire* database structure, configuration, and data, which can all be represented as scripts
and of course the Java, C, COBOL, etc. modules that everyone agrees can be called "code"

The reason I like this broad definition is that it is very useful when thinking in terms of configuration management, change management, and deployment management. And it also goes back to the discussion about the Jack Reeves essays in that all of these things are elements of the final, and only complete, expression of "the design."

Finally, I wanted to mention Joe Celko, a SQL and database expert, and a favorite author of mine. I've followed his "Celko" column for several years. He has a great book called SQL for Smarties that I highly recommend, and another called SQL Puzzles & Answers, that I've never read but that looks great. I see that he has a new book out called Trees and Hierarchies in SQL for Smarties, which I'll have to check out. Several years ago I implemented a tree-based relational/SQL-based solution that was based on (though different than) the "trees in SQL" installments of his "Celko" column.

Anyway, enough rambling. Time for bed.

Dan

Thinking in Sets

Rob MacGrogan — Wed, 13 Apr 2005 14:56:18 -0700

Coders think in terms of code. It's the old saw about the hammer. When that's the only tool you've got, everything starts to look like a nail.

Coders, of course, have more than one tool at their disposal. We have worlds of tools. But coders do like to code (see Andy's recent post if you don't believe me). It's what we like to think we do best.

Before I get too far along here, let me clarify what I mean by code. I argued for a broad definition of source code in a recent comment, and I'm not backing away from that really, but maybe clarifying a few terms. Let's say, for the sake of argument, that code is that which is written in a programming language such as BASIC, C, Java, and the rest. I won't attempt to define it further. And let's say that what I called "source code" in that other post might better be called simply "source." Only three paragraphs into my post and I've already gone off on a tangent. Typical.

The point I want to make here is that coders like to work in familiar territory. Present a coder with a problem and his or her mind will immediately go to work on solving that problem in the software language of his or her preference. For example, if you present me with a problem, I'll probably start right away with a possible solution using Java and some of my recent favorite technologies, such as Hibernate, SOAP, and maybe JMS. Java is my favorite hammer and I like to use it.

But many many problems are not best solved by building a mighty computer program, as I recently learned.