Monthly Archives: November 2011

Order of the day

Posted on by
2

I’m in the middle of designing a db schema for a project I’m working on.

As I was sitting in my car, waiting for the light to change, I remembered a mantra of SQL design.  And what is funny is I often see this fundamental aspect overlooked or even in at least one case, intentionally ignored: namely tables do not have any intrinsic order.

Almost all of my experience has been using MSFT SQL Server, so my thoughts will be based on that, but the general idea is true.  Beginners to SQL will assume “if I put the data in in order, it will come out in order.” Now if they’re a bit more than a beginner, they’ll even test that.  And lo and behold, they’ll probably find their assumption is right when they test it.  Then they’ll put the code and schema into production and find that suddenly things aren’t working right.

They’ll wonder why it worked on their machine, but not on production.  Generally there’s two reasons.  SQL Server is very aggressive about caching, so there’s a chance it’ll read the data out of memory in the order they put it in.  In addition, if it does have to read it off a disk, it’ll most likely do it in a single-threaded manner, following the primary key and the data will come out as expected.

On production though, between the time the data is inserted and later read back, the cache may look very different.  But even more so, a production machine is liable to have multiple disks and multiple CPUs which means multiple read threads will occur and SQL Server will then put the data together in the faster way possible.  Suddenly it’s no longer in the order the programmer wanted it or expected it.

Now, if you’re writing a quick ad-hoc query to get some data out quickly, that’s probably ok.  I’ll admit I do a lot of queries without an order by when I just need to quickly get some data.  But if it becomes time to productize the code, I’ll use an order by.

As I mentioned at the top of this post, the lack of an Order By seems to be a fairly common mistake made by folks new to SQL programming.

But what about the case when it’s intentional?  Years ago we were rolling out some new code and in the code there was a query that did a query against a table.  In theory the table would only ever have one row.  The programmer decided with one row no order by was necessary.  However, as always theory and practice don’t always match and I asked what would happen if there was more than one row?  His answer was to use a “TOP (1)” in the query.  So I asked him what would guarantee he’d get the top row he wanted.  He said he didn’t think the problem would ever arise and resisted using the ORDER BY “for performance” reasons he said.

Well since I wasn’t his manager, I wasn’t about to fight this particular fight.  But I did make a note of it.

Sure enough, about two years later (a year after he had left the company) the page that used this query started to return the wrong results.  A quick look and a quick addition of an ORDER BY and all was well.

It’s always the little things.  And that’s the order of the day.

Xbox Kinect

Posted on by
Reply

So, saw a commercial for the Xbox Kinect last night.  My son asked a bit about it.  What was the most interesting about the commercial wasn’t that it was trying sell games, it was trying to sell the Kinect itself.  And it wasn’t doing that by highlighting games.  It was highlighting http://www.xbox.com/en-US/Kinect/Kinect-Effect. When the game console wars re-ignited a few years ago, there was a scramble between the Playstation, Xbox and Wii.  The Playstation and Xbox took the traditional route. More features.  Higher resolution.  Faster chips.

The Wii took a different route, almost like a guerrilla warfare tactic of not going for the faster chips, higher resolution.  They decided to change the gaming experience and go with their Wii Remote.  This changed the battle tremendously and  honestly is probably the only reason the Wii survived the battle at all.  I think if the Wii had gone the traditional route, Nintendo would have fallen by the wayside as Sony and Microsoft duked it out.

Then, a few years later Microsoft struck back with its own game-changing (sorry, couldn’t resist the pun) strategy.  They introduced the “jet-engine” known as the Kinect.  I use the term “jet-engine” not because it made anything faster, but because it changed the face of gaming dramatically, much like jet-engines changed aerial combat and strategy.

Originally tied strictly to the Xbox 360, within a month or so, open-source drivers for the PC were available.  This made it not just a new way to play games, but a completely new way to interact with a computer.

Now you can paint in three dimensions using it.  You can “grab air” and rotate and manipulate a digital image on the screen as naturally as if it physically in front of you.  You can swim through the Universe if you wish.  You can build a robot to navigate your house (Though it still can’t make the perfect martini.  Yet.)

And of course you can play games.

But like many great designs, its reach is far beyond its original purpose.  It’s innovations like this that truly drive the industry forward.

Next time you design something or build something, don’t fear someone using it in a way you didn’t intend.  Hope for it.

Connectivity

Posted on by
Reply

So I’m sitting Amtrak train 280, headed to New York City and then on to Atlanta.  And I can tweet, blog, check email and do business.  This is one reason I like train travel. (Though I’ll admit taking the train to Atlanta from Albany is far from the quickest way of getting there.)

I’ve been able to access the Internet from the train for over a decade now thanks to Sprint.  This time though I’m posting through Amtrak’s on-board WiFi service.  Amtrak is slowly moving into the 21st Century.

Given the technical limitations Amtrak is facing, I have to say so far they’ve done an acceptable job.  They still require you to hit their proxy web page before you can get any connectivity, which stopped me for a bit since I was trying to do non-HTTP based work at first (dialing into my home VPN and a particular chat program I use.)  I understand their point (so they can put up their disclaimer and all) but it is annoying.

One nice feature, though I suspect not much used is that once you agree to their terms of service, it takes you to a page that shows a map with your current location.  This can be useful if you don’t have a GPS and are curious as to where you are.

And from all reports, they’ve done a decent job future-proofing the system.    Briefly, each car basically has a repeater that talks to the cafe car.  The cafe car has the antennas and cell-modems and is designed to be upgradeable as technology improves.

Well, I was going to add more, but my old laptop keeps crashing (time to upgrade, but have to get some business!).  So for now I’ll end with saying I’m on train 19 now, on my way to Atlanta.  Where, thanks to the modern technology I’m now aware there are tornadoes.  Wonder what that will do to my trip.

 

Simplicity

Posted on by
Reply

Over the years I’ve been involved in a number of web-based companies.  All had great ideas for their business model.   One had one of them had a great idea for classified ads.  It had the latest in taxonomic matching and advanced search capabilities.  If you were looking for a Mustang, it could tell direct you to ads for cars or horses depending on context and other factors.  Its search capabilities were ahead of the time.  It had pretty much every bell and whistle the newspapers asked for and that the design folks could think of.

Then Craigslist came alone.  Craigslist was free (at least compared to newspaper classified ad sites where the newspapers typically charged.)  It had no taxonomic matching.  Its search capabilities were and still are bare-bones.  In fact, it very much relies on the user to narrow down and define searches.

But it succeeded where the other product failed for what I believe one very simple reason.  It was simply blazingly fast.  It didn’t matter if it returned bad results the first time.  It was so fast the user didn’t mind typing in new search parameters and narrowing down their search.  It was faster than any of the “advanced” newspaper classified engines I saw.  Sure, they might try to do a better job of returning results, but the honest truth was, in most cases people would end up doing multiple searches anyway trying to narrow down their search.  And in the time it took to do 2-3 searches with a typical website, Craigslist allowed the user to do 10-15 searches.  Time was money and people wanted to do things quickly.

Over the years with numerous sites I’ve seen the design get in the way of the end-user.  The truth is, 80% of the time, people will use 20% of the features, but they want those 20% to be as fast as possible.

So, keep it simple and keep it very fast.

One of these days though I’ll relate the story of the 3,000 mile Steinway search.