« | » Main « | »

My own private WTF?

Thursday 31 March 2005

As I've mentioned, I've taken over responsibility for a body of C# code. The layout code is filled with stuff like this:

this.Size = new Size(this.Size.Width,
   this.widgetsLabel.Location.Y + this.widgetsLabel.Size.Height);

To paraphrase Tevye, would it have spoiled some vast eternal plan to write this instead?:

this.Height = this.widgetsLabel.Bottom;

I spent quite some time today applying shrinking transformations:

x.Size.Height                             -->  x.Height
x.Location.Y                              -->  x.Top
x.Top + x.Height                          -->  x.Bottom
x.Size = new Size(x.Width, y);            -->  x.Height = y;
x.Location = new Point(x.Location.X, y);  -->  x.Top = y;

Believe me, it's much more readable afterwards! Of course, I've a sneaking suspicion that a lot of this layout code could just be chucked by setting proper constraints in the forms designer! To quote another movie character, I can't think about that today, I'll think about that tomorrow.

I've slightly edited this post to remove asides about developers that were distracting from my point. Reactions from readers about those comments have also been removed.

Argent: Cog for Ruby

Tuesday 29 March 2005

Cullen O'Neil wrote to tell me that he liked Cog enough to re-implement it in Ruby. The result is Argent. Now you can use Ruby for code generation:

// This is my C++ file.
...
/*[[$argent
  ['DoSomething', 'DoAnotherThing', 'DoLastThing'].each do |fn|
    $argent.outl("void #{fn}();")
  end
  $]]*/
//[[$end$]]
...

Reading the Argent code was an interesting experience: I don't really know Ruby, but I'm familiar with how Cog is implemented, so I "knew" the code without knowing the language. It helped me understand more about Ruby.

Also cool: Cullen uses Argent to manage its own Makefile. He generates test classes and Makefile entries for them using Argent itself.

Wrapping delegates in C#

Monday 28 March 2005

Continuing my education in C#, I don't understand what happens to exceptions in event handlers. For the most part, when I register an event handler for a UI event (like button click), if the handler throws an exception, I get a detailed dialog box showing what happened. But for some events, the exception is eaten silently. One of my strongest passions when coding is to know what is going on under the covers, and to be absolutely sure that error conditions are at the very least visible.

I'm starting to get the hang of events and delegates. It isn't yet another language for me, still a foreign language. But I figured there ought to be a way to write a delegate wrapper, so that I could take an event registration like this:

button1.Click += new EventHandler(button1_Click);

and using some yet-to-be-written class, make it look like this:

button1.Click += new WrappedHandler(new EventHandler(button1_Click));

where WrappedHandler would call the event handler passed into it, but inside a try-catch block, so that exceptions could be displayed.

I had to take a few stabs at it, and I ended up with three "new"s rather than the two I thought I would need, but here's something that works:

public class WrappedHandler
{
    private EventHandler handler;

    public WrappedHandler(EventHandler handler)
    {
        this.handler += handler;
    }

    public void Handler(object sender, EventArgs e)
    {
        try
        {
            handler(sender, e);
        }
        catch (Exception ex)
        {
            // Our handler threw an exception.
            // Show it.
            MessageBox.Show("Exception: " + ex.ToString());
            // Then re-throw it.
            throw;
        }
    }
}

Now the event handler can be registered like this:

button1.Click +=
    new EventHandler(
        new WrappedHandler(
            new EventHandler(
                button1_Click
            )
        ).Handler
    );

Is this the simplest it could be? Did I miss a left turn a half mile back?

BTW: as I was making a new tiny project to experiment with this, I noticed that all of the event handlers were nicely reporting exceptions. My real project still has exceptions which aren't being reported. I'll have to track down whether it's because they are different handlers, or because of the third-party controls we're using, or even because my predecessor is eating exceptions somewhere. Fun fun.

Robyn Miller is entirely cool

Friday 25 March 2005

The coolest thing to happen as a result of our Myst cake is that Robyn Miller sent me an email about it. Robyn (along with his brother Rand) created the Myst world (including the games) which inspired the cake. In his email, he pronounced the cake "entirely cool".

My son and I were thrilled and flabbergasted. It was as if I'd mentioned Star Wars in a blog posting and got an email out of the blue from George Lucas about it. I wrote back to Robyn about my son's interest in his work, and in video games, movies and their intersection in general, and asked Robyn if he had any advice for a budding auteur. Robyn wrote back:

Firstly, tell your son to have many more happy birthdays. Secondly, tell him that it's not the tools, but the ideas behind them. An author isn't concentrating on "technology" when approaching that next book. Instead he's thinking story, ideas, content. Because technology as a medium is boring. But great ideas, conveyed through even the most low-tech media, can come alive.

Hmph. Very highbrow sounding. Don't I sound just-wow? Professor of Interactivity?

Sincerely,

Robyn Miller, PhD

P.S. You can visit me at me at my new, online-university (I've accredited myself, by the way): http://www.robynmillersamazingschoolofmultimediawhereyouwilleventuallylearntobeagenius.com

Entirely cool.

Python limericks

Thursday 24 March 2005

A thread on comp.lang.python asking about text-to-speech libraries turned into a call for Python limericks. The discussion meandered on for a while (let's just say there's a reason most of these people are engineers rather than poets), until Michael Spencer posted this piece of brilliance:

How about a category for executable limericks?

Here's one to get the ball rolling:

# voice only the alphanumeric tokens

from itertools import repeat
for feet in [3,3,2,2,3]:
     print " ".join("DA-DA-DUM"
     for dummy in [None]
for foot in repeat("metric", feet))

Michael

P.S. I know 'three' doesn't rhyme.

Not only is the Python code itself in the form of a limerick, but if you run it, it prints this:

DA-DA-DUM DA-DA-DUM DA-DA-DUM
DA-DA-DUM DA-DA-DUM DA-DA-DUM
DA-DA-DUM DA-DA-DUM
DA-DA-DUM DA-DA-DUM
DA-DA-DUM DA-DA-DUM DA-DA-DUM

Genius!

I Boing-Boing'ed myself

Thursday 24 March 2005

Monday night it occurred to me that my Myst birthday cake would appeal to readers of Boing Boing, so I filled in their handy "suggest a site" form. Three hours later, it appeared on the site: Myst island birthday cake. Something interesting always happens when Boing Boing links to me. The last time was in 2003 (Bizcard origami), and that resulted in my page becoming a top Google hit, which lead to ad revenue, which lead to a New York Times mention, and so on. So I was interested to see what would happen this time.

It's only been two days, but already, stuff has happened. For one thing, my visitor traffic doubled (to about 6000 visits per day). The first day, as expected, it all came from Boing Boing. But the second day, I was surprised to get even more hits from a site I'd never heard of: Blues News, a compendium of video game news. In fact, there were so many hits (3000 compared to Boing Boing's 2000), I assumed they had linked directly to an image (so that my hits were a reflection of their page views). But no, they had a simple text link to me, buried in a long page of text and links. They must have a huge readership.

The Boing Boing entry is now in the top ten results in a Google search for birthday cake, and of course, the first for Myst birthday cake. That last search reveals a previous Myst-themed cake, along with other geeky cake goodies.

Velcro snail and Jimwich

Tuesday 22 March 2005

I was fiddling at work with a velcro cable tie, and I ended up making a snail:

Velcro snail

It reminded me of something I saw years ago: Sculptures in my Geekosphere, a collection of animals made from the type of junk found lying around any office. The blog it came from was a favorite of mine, Jimwich, but Jim Leftwich stopped writing it over two years ago. My velcro snail inspired me to go look up Jimwich again, and hurray!, Jim is writing again! His blog is eclectic, with unusual finds in architecture, vehicles, and art. Welcome Jim back, and let's hope he doesn't take another long break.

SCons

Saturday 19 March 2005

A Cog email correspondent asked a question about using Cog with SCons, so I read up on it. It's very interesting: A "make" replacement for building software, but uses full Python scripts instead of declarative Makefiles. But don't worry: most build tasks are handled in declarative style. For example, building hello.exe from hello.c is simply:

Program('hello.c')

Another interesting feature is that SCons determines the need for building using an MD5 checksum of the file, so the need to build is determined more accurately. For example, a C file may change, but if the .o file it produces hasn't (because the C file only changed a comment), then there's no need to execute the link step.

BTW: the Cog question had to do with using Cog in an SCons environment, where the same file is both the input and output for Cog, and how to get SCons to do the right thing. Anyone know?

Opening a file with an unknown extension

Friday 18 March 2005

I was trying to fix a bug at work yesterday that had bothered me for a while. Our product launches document files, and the bug was that if the file has no file association, it wouldn't do what most Windows programs do. If you attach a file called foo.xyzzy to an email, and double-click it, you get a dialog that says "Windows cannot open this file", and it lets you choose an application to open it with.

In our product, the user got a message box that said "There is no application associated with this file", and nothing she could do about it. The original developer of the feature was the type to try for a while, then claim it couldn't be done. He had marked the bug as fixed because he put up the alert telling the user they were screwed.

I remembered seeing tips on how to get the whole choreography right, so I started digging. Here's a bit about the solution, and how I found it.

» read more of: Opening a file with an unknown extension... (9 paragraphs)

New Microsoft typefaces

Thursday 17 March 2005

Here's a story about six new faces that Microsoft will be shipping in 2006: The Next Big Thing in Online Type. I'm way disappointed. I've got a pretty discerning type eye, and these don't look that different than the faces Microsoft already ships, Trebuchet, Georgia, and Lucida Console. They don't even look different from each other. And the names of these "new" faces merely underscore their carbon-copy nature: Calibri, Cambria, Candara, Consolas, Constantia, and Corbel. Couldn't they at least have given us something to replace the awful Comic Sans? In any case, get used to them. If Microsoft ships them, we'll be seeing a lot of them.

Amaztype

Thursday 17 March 2005

OK, I'm ready to announce the winner of the award for the most unusual visual search interface: Amaztype, a "typographic book search". Type a word into its search box, and it starts loading pictures of books at Amazon that match. But (here's the unusual part), the images are scaled and positioned so that together, they form the word you were searching for. What's the point? I don't know. Is it different? Yes. Do I think it's cool? I do.

Microsoft DLL help database

Thursday 17 March 2005

This is one of those things I post so that I can find it again later: DLL Help Database. It's Microsoft's searchable database of which DLLs shipped with which products. If you build Windows software, and you rely on a non-redistributable Microsoft DLL, this thing helps you figure out which Microsoft products you pre-require. If you don't know what I'm talking about, you don't need it.

SQLFairy

Wednesday 16 March 2005

I haven't tried it, but SQLFairy looks useful. It's a set of Perl modules that

manipulate structure data definitions (mostly database schemas) in interesting ways, such as converting among different dialects of CREATE syntax (e.g., MySQL-to-Oracle), visualizations of schemas (pseudo-ER diagrams GraphViz or GD), automatic code generation (using Class::DBI), converting non-RDBMS files to SQL schemas (xSV text files, Excel spreadsheets), serializing parsed schemas (via Storable, YAML and XML), creating documentation (HTML and POD), and more.

Drawn!

Wednesday 16 March 2005

As I have mentioned here in the past, my youngest son is a drawing fiend. We wonder sometimes how best to encourage his developing skill. We figure one way is to expose him to visual excellence where we can find it. That's why I was pleased to find Drawn!, "a collaborative weblog for illustrators, artists, cartoonists, and anyone who likes to draw." The rest of my family (me included) have an appreciation for graphic works as well, so I expect we'll all find things of interest there.

The original pointer to Drawn! was to Spamusement ("Poorly-drawn cartoons inspired by actual spam subject lines!"), but in keeping with the kid-art theme, I also enjoyed Lizette Greco, who make physical artifacts (toys, bags, and so on) from drawings by her young children.

Another interest my kids share is how movies are made, and a recent favorite movie is The Iron Giant, so we all enjoyed the post about Whatever happened to.. Mark Whiting, the art director for the movie, complete with links to concept art for the animation. Cool!

Robotype

Monday 14 March 2005

RoboType is an online doo-dad for making pictures out of type. You choose typeface and character, then drag and stretch it where you want, then get some more until you have a picture. Here are two good ones I found in the gallery:

A cat and an enraged king, made out of type

Most of the pictures are along these lines, though there are quite a few R- or X-rated attempts as well!

Happy π Day

Monday 14 March 2005

Today is Pi Day. All day long, in the back of my mind, I've been wondering what tidbit I could find to post here to appropriately declare my math geekiness. I didn't do anything about, though, and it turns out I didn't have to: a friend forwarded along news of Ned Raggett's π record, wherein a bigger-math-geek-Ned-than-me reads forty-five minutes of the digits of pi. Yee-hah!

BitTorrent + RSS = TiVo?

Sunday 13 March 2005

Pedro Alcocer explains How to never miss an episode with BitTorrent and RSS.

As it happens, I first installed BitTorrent this weekend (to get an update to the Ubuntu CD I picked up at the Python Meetup). I was very impressed with the BitTorrent technology. Before I had even finished downloading the Ubuntu image, I was serving parts of it to other clients.

So when I saw the description of using RSS with BitTorrent to automatically download episodes of TV shows, I figured I'd look into it. TvTorrents implements the idea well: torrents are available for many shows, organized by year and episode. Each user has to login to download, and a credit system is used to ensure that downloaders also provide upload bandwidth. I haven't read about the details of the BitTorrent protocol, but it seems to have been very well thought out if credit systems like this can be built on top of it.

Of course, sites aggregating TV episodes and offering them for download are illegal, and I'm sure the TV industry is taking steps to bring them down. But it is thrilling that free open source technology is available which can be used to build services like this.

NYPL Digital Gallery

Sunday 13 March 2005

The NYPL Digital Gallery is the New York Public Library's large searchable collection of scanned images available for free use:

NYPL Digital Gallery provides access to over 275,000 images digitized from primary sources and printed rarities in the collections of The New York Public Library, including illuminated manuscripts, historical maps, vintage posters, rare prints and photographs, illustrated books, printed ephemera, and more.

If you want historic or old-fashioned images, this is the place to look. I found this forgotten piece of western civilization:

Cover of Ann Batchelder's Own Cookbook

The long tail of software

Saturday 12 March 2005

The idea of the "long tail" is that however you categorize things, a few of the most popular will make up the bulk of the domain, and a huge number of less popular will stretch out in a long tail. The idea applies to many different domains: songs, movies, books, and of course, blogs.

It's especially applicable in the online world, because online, interactions are freed from the limitations of physical stores, or movie theaters, or whatever. This lowers barriers, and makes the long tail accesible. It's easy to see this if you look at (for example) the range of topics represented in online communities and publications compared to those in printed publications. The online world has a far broader bestiary of interests, sliced much finer, than the printed world. The internet has a reputation for being home to wackos, but those wackos live in the physical world too — they just can't get together with their buddies except online. The virtual world makes the long tail of wacko fascinations accessible.

But I digress. I'm trying to tell you that Joe Krauss has written about The long tail of software:

In the software business, the traditional focus has been on dozens of markets of millions instead of millions of markets of dozens. The traditional software model is to make software have enough features and address enough of a homogeneous market that you can sell millions of copies of the same software. In the past, that’s been the only way to make money.

and:

Serving the head isn’t a bad strategy. You can build a great business. But, figure out how to serve the tail of your market efficiently and you’ve got a blockbuster.

He's made some great observations. Now the trick is to figure out how to do it.

Boston Python meetup

Friday 11 March 2005

Last night was the Boston Python Meetup meeting. We had about a dozen guys, and the informal conversation ranged all over: Webware; Plone; the legal status of the Python license; how to find Python packages; whether (and how) to create a Boston-area Python consultants directory; and so on.

Stephan Deibel of the Python Software Foundation was there, and made a good case for the cause. He claims that 14% of all the world's programmers have used Python, which is twice what it was last year, and that if 10% of them would donate just $10 each, the PSF would have a large coffer of money to do the things that open source doesn't usually do well (marketing, unpleasant chores, and so on). Give some money!

All in all, we had a good collegial time. It wasn't drinking Mojitos until stupid o'clock, but this is Boston after all!

Who's on First?

Thursday 10 March 2005

Chris Gavalier's Who's On First? is a video store dialogue in the style of Abbot and Costello:

CUSTOMER: When is this one due back?

CASHIER: The day after tomorrow.

CUSTOMER: Yeah, when's it due back?

CASHIER: The day after tomorrow.

CUSTOMER: Yes. The Day After Tomorrow.

CASHIER: Right.

CUSTOMER: Right. When's it due back?

CASHIER: The day after tomorrow.

L0rd 0f teh Ringz0rz

Wednesday 9 March 2005

The Lord of The Rings, translated into l33t5p34k: F3ll0wsh1p of teh R1ng (scroll down a bit) and Teh Tw0 T0werz. Not mentioned by most other linkers: The first link starts with a hilarious list of "survival tips" for watching the movies in theaters:

7. Finish off every one of Elrond's lines with "Mr. Anderson."

16. Every time someone kills an Orc, yell: "That's what I'm Tolkien about!" See how long it takes before you get kicked out of the theatre.

LOL!

I'm an Amazon associate

Wednesday 9 March 2005

When I link to Amazon (for example, when writing about books), I do it with an Amazon Associates link. Amazon Associates is an affiliate program: when you follow my link, I get a small percentage of the money you spend at Amazon. Your price remains the same, so it's a win-win situation for both of us.

I'm not going to get rich off of it (I've earned a grand total of $25.31 in the two years I've been a member), but money is money, am I right? I've added an Amazon link to the sidebar on the left. If you want to toss a few pennies my way, the next time you are heading to Amazon, use my Amazon link. Thanks!

Cell phone virus

Wednesday 9 March 2005

What with cell phones really being handheld computers connected to a global communications network, it was only a matter of time: New virus found in phone messaging.

The Commwarrior.A virus tries to replicate itself by sending multimedia messages to people on the phone's contacts list, and also tries to do the same via Bluetooth wireless connections with other devices, eventually draining the battery.

Unlike computer viruses that spread quickly around the world via the Internet, mobile phone viruses have previously been limited by technology.

Three years old

Monday 7 March 2005

Today is this blog's three-year birthday. It all started way back in 2002 with a post about my first job ever. I've had many cool interactions because of this blog, of a greater number and a wider diversity than I would have guessed when I started it. Thanks everyone!

Muy macho shredders

Sunday 6 March 2005

Since I work in software, hardware is pretty much an abstraction most of the time. Not so for the people at Shredding Systems Inc. They make serious industrial shredders, the kind of thing used in factories for "the toughest size reduction jobs in the world." They have a cool page of movies of the shredders in action. For example, ever seen a couch being shredded? Or a washing machine? Or whole computers? Fascinating!

Brookline ScienceFest '05

Sunday 6 March 2005

I manned a table yesterday at the Brookline ScienceFest. I showed two completely unrelated things. The first was Nat's World (the photo exploration game I wrote for my autistic son). We labelled it "Virtual Brookline" and people seemed to really like it. The second thing on the table was folding business cards into cubes. Some kids were just crazy for folding cards, and worked for about half an hour on their constructions. I also brought along a Soma cube I had made with cards, inviting people to solve the puzzle.

Beforehand, I had thought that these things weren't really worth of the "Science" label. But kids liked them, because they could play around with the game and the cards, and no one was going to lecture them. Other exhibits had a lot of good science (tree fungus, effects of smoking on your lungs, that sort of thing), but didn't give smaller kids a chance to play. Nat's World in particular was designed from the beginning to be dead simple for a kid to use, with no "wrong" possibilities. When someone walked up and wanted to know what it was, I just said, "Try it, click around", and let them explore.

Kids really seemed to like Nat's World. I had shown it last year at the ScienceFest, and some kids came up because they remembered it from last year. If people seemed reluctant to move around in the virtual world, I could ask what neighborhood they lived in, and navigate them there (if it happened to be one of the few I had put into the game). A few people found themselves in the game, just because they happened to be on the street when I was taking pictures!

There are a few surprises in Nat's World. One is that there are a few computer screens in the game, and if you click on them, a smaller recursive instance of the game plays on the screen. It's good fun to show kids the screen and ask them what will happen if you click on it. They usually guess correctly, and once they see that the smaller game is really playable, they always say the same thing: "Let's find the computer screen again, and make an even smaller one!". In good recursive form, this works, and you can continue deeper until the screen is too small to click on.

Another cool thing: Marvin Minsky, who I've run into before. His wife is the Director of Health for the school department in town, and had a table right next to mine. So Marvin was hanging around, and we chatted about Nat's World and folding business cards. It felt a little like playing with pencils on the group W bench somehow. He took some photos of the recursive Nat's World, who knows for what purpose?

It was a lot of fun showing stuff to people. Brookline is a good town for this sort of thing because there are a good number of techies around. Every year that I do the ScienceFest, I meet interesting people. Next year it's March 4th. Be there and be square!

Who owns blog comments?

Sunday 6 March 2005

I should have known it would come to this: Charles Smith conducts a serious discussion about Who owns blog comments. As in, when you post a comment on my blog, do I own the copyright to it, or do you? Is anyone really worried about this kind of thing? Other than IP lawyers that is? What's next? Who owns the copyright to cocktail party chatter?

I'm not belittling the value of a blog comment, but please: is there no simple good will left in the world? To complicate things further, a comment on the posting claims that while the individual comments are owned by their authors, the compilation of comments onto the posting are owned by the blogger. Oy!

Charles actually suggests that bloggers use a Terms of Service document that grants them a license to display people's comments:

6. Information Rights. The Proprietor does not claim ownership of Content you submit or make available for inclusion on the Service. However, with respect to Content you submit or make available for inclusion on the Service, including without limitation comments you post to the Service, you grant the Proprietor world-wide, perpetual, irrevocable, royalty free, non-exclusive, fully sub-licensable license(s) to use, distribute, reproduce, modify, adapt, publish, translate, publicly perform and publicly display such Content (in whole or in part) and to incorporate such Content into other works in any format or medium now known or later developed.

I'm starting to wonder about the irony of the name of Charles' blog: Reasonable Man.

111111

Saturday 5 March 2005

Yesterday, driving to work, my car's odometer read 111111. It seemed like some kind of milestone. That's all.

The book stops here

Saturday 5 March 2005

Wired magazine has a good piece about wikipedia: The Book Stops Here. It covers a lot of familiar ground: the history of encyclopedias, old media vs. wikis, is it reliable, what can be done about vandals and so on. Here's something revealing:

"You can create life in there," says Wiki­pedian Oliver Brown, a high school teacher in Aptos, California. "If you don't know about something, you can start an article, and other people can come and feed it, nurture it." For example, two years ago, Danny Wool was curious about the American architectural sculptor Lee Lawrie, whose statue of Atlas sits nearby Rockefeller Center. Wool posted a stub — a few sentences on a topic — in the hopes that someone would add to it. ... Today, the Lawrie entry has grown from two sentences to several thorough paragraphs, a dozen photos, and a list of references.

I never would have thought of that: find out about a topic by creating a stub Wikipedia article about it, and let the fanatic Wikipedians fill it in. Very cool.

Portrait illustration maker

Saturday 5 March 2005

Portrait Illustration Maker is an another online gizmo to create a picture of yourself. You pick from dozens of pieces to put together the perfect 96×96 pixel icon of you. The choices are manga-influenced, and lean heavily toward youthful Japanese tastes (naturally). Here's me (or as close as I could get in 20 minutes of trying):

Me, in 96x96 pixels

Actually, this looks more like Steven Spielberg than it does like me.

Building a modern computer from first principles

Saturday 5 March 2005

This looks like an amazing textbook: Build a Modern Computer from First Principles. Judging from the accompanying presentation (From Nand to Tetris in 12 Steps), it presents a wild ride up the entire computing stack, from nand gates to operating systems. The authors provide a series of Java-based emulators (hardward, CPU, VM), translators (assembler, compiler), and a primitive programming language and operating system with which to do the 12 projects. I'm not sure how much depth they can provide at their fast pace, but it sure would be interesting to try it.

C# and OutputDebugString

Thursday 3 March 2005

I've taken over responsibility at work for a UI written in C#. This is challenging on a number of levels: I haven't done a lot of UI, I haven't done a lot of C#, and the previous owner was (shall we say) not the most disciplined developer.

Debugging UI code is a pain because if your debugger obscures the UI, then exposing the UI causes window messages, which change the behavior of the code, or trip more breakpoints, and so on. So I'm using printf-style debugging: printed messages in key points to understand what's going on. Here's some tips on how to do it.

» read more of: C# and OutputDebugString... (14 paragraphs)

Fork art

Thursday 3 March 2005

While in New York City a few weeks back, we walked past the Guggenheim Museum. Outside on the sidewalk, a young man had set up a table with scupltures on it. Looking closer, they had all been fashioned out of forks! In fact, the man had before him a stack of forks, and in his hand, a needle-nose pliers, and was busy making more fork scupltures.

His gallery at fork-art.com doesn't do the sculptures justice: they struck just the right balance between abstract and figurative, and each had real flair.

SQL hacks: numbers table

Thursday 3 March 2005

I've done SQL work for a number of years, but it's usually been pretty plain vanilla relational data: I haven't had to venture too far into the truly wild and wooly SQL hacks. For example, I'd never heard of a numbers table. It's a table with one integer column, and a row for each integer in a range (for example, 1 through 10,000). The table gets used for all kinds of unusual queries.

But is it a good idea? A quick look at some of these queries gives me the impression that they'd be inefficient:

DECLARE @csv VARCHAR(255)
SET @csv = '1,3,5,7,9,14,36,395'

SELECT n
    FROM numbers
    WHERE CHARINDEX
    (
        ','+CONVERT(VARCHAR(12),n)+',',
        ','+@csv+','
    ) > 0
    ORDER BY n

This will examine each row in the numbers table, and check if the number is in the string. This is bone-headed. In fact, if that exact algorithm were coded in a procedural language, it would qualify as a Daily WTF.

Sometimes the pure relational model nudges you into odd hacks like this, and sometimes it is hard to know the best way to slice a problem. For example, if the alternative to a numbers table is to write a loop in the client, then shuttling all the data to the client may be a bottleneck, and the numbers query may be better in any case.

As with all performance issues, you can take a guess, but the only way to really know is to measure a real system.

BTW: XSLT is another programming model that leads to these sorts of tricks. Both XSLT and SQL give you a fundamentally declarative style of programming. But developers think procedurally. The conceptual mismatch leads to difficulties expressing yourself, and then off-the-wall ideas like a numbers table to make more things possible.

« | » Main « | »