What are your thoughts?

This poll will close on Thursday, March 18 at 8AM EST and I’ll publish the results in a separate blog post.

Are we not men? No, we are Hammurabi!

I’m here reminding you that you need to pay attention.

This isn’t like in Middle School history when you were learning about the Epic of Gilgamesh and how he killed a demi-god with the help of a hairy little man and together they survive watching the movie Ishtar and end up wearing a cow’s ass as a hat or something. Did you pay attention to that story? Yeah, me neither. This is nothing like that.

I’ve noticed something that bothers me – we constantly distract ourselves. People are always on the phone or listening to music or basically not paying attention. Stop it.

Breathe

Take a deep breath and let it out slowly. Breathe in. Breathe out. Fill up your lungs, focus on breathing.

Do you feel that? That’s you calming your tiny little primate brain and making it pay attention to one very simple thing that it already does on its own. Feels good, doesn’t it?

Keeping doing for a bit. Focus on your breathing. If you drift off for a second, just focus on your breathing. Your mind is still there, you just shifted your focus. This page will still be here when you get frustrated.

Think

What’s the point of this?

Honestly, the point was to get you to stop for a minute.

Think about how often you’re constantly worried about what to do next. Think about how often you’re thinking about a meeting you had earlier in the week, an argument, or anything else that happened in the past. You think about the past and the future a lot, don’t you?

Now, think about how much time you spend focusing on right now. Be really honest with yourself. I bet it’s not a lot.

This is really mature, guys.

You’re boring.

Did you perk up and pay attention just now? I thought so. You probably aren’t boring. You’re probably a fascinating person with diverse and varied interests and I’m sorry if I hurt your feelings, but you’re boring the shit out of me. Fact: life is not full of roller coasters and orgasms. Sometimes you have to peel potatoes. When you’re peeling the potatoes, peel the potatoes. Don’t think about how you’re going to have pie for dessert or how your 14 disk box set of Quantum Leap is going to come in the mail in a few days. Just peel the potatoes.

Be

Zen students are with their masters at least ten years before the presume to teach others. Nan-in was visited by Tenno, who, having passed his apprenticeship, had become a teacher. The day happened to be rainy, so Tenno wore wooden clogs and carried an umbrella. After greeting him, Nan-in remarked “I suppose you left your wooden clogs in the vestibule. I want to know if your umbrella is on the right or left side of the clogs.”

Tenno, confused, had no instant answer. He realized that he was unable to carry his Zen every minute. He became Nan-in’s pupil, and he studied six more years to accomplish his every-minute Zen.

from Zen Flesh Zen Bones, compiled by Paul Reps and Nyogen Senzaki

To put it another way – an ordained monk was so humbled by his own inattention that he once again became a student. We all slip up, but if you try you can manage to pay a little bit more attention to what you’re doing. Take pleasure in the things you’re doing. When it’s time to peel the potatoes, peel the potatoes. When it’s time to indulge in your Margaret Thatcher fetish, don’t call me.

Fact: the earliest recorded use of cloud computing was ancient Greek porn

Anyway, it is rumored that Io was attractive. So attractive, in fact, that Zeus, lord of the perverts, saw her taking a bath and got more than a little bit aroused. Zeus then behaved in a way that would end up in a savage beating and restraining order back where I come from – he pestered Io for nookie until her father drove her out of the house – probably because some horny lunatic who could shoot children out of his forehead was bothering his daughter. Io, being a bit strange in the head, relented. Or something. My records aren’t 100% clear seeing as how they’ve been written on pottery. The point is that Zeus turned into a giant cloud and turned Io into a cow (no, your hooves don’t make you look fat).

Somehow Zeus’s wife got involved and there was bondage involving a cow tied to a tree or something. Eventually Io gets turned back into a real live girl and gives birth to Zeus’s son. Which brought about an ethics probe into cross-species cloning.

Disk… disk… oh yeah I already mentioned that the ancient Greeks were clearly insane and thought that the world was a giant metal plate floating on a huge river name Oceanus all of it encased in a hemisphere with clouds and the sun and the moon and stuff painted all over the inside of the hemisphere.

What’s outside of the hemisphere? Shut up, that’s what. It’s turtles all the way down.

There’s a bit of humor thrown into your T-SQL Tuesday

Blog memes are great. They give me an opportunity to pretend to be inventive and creative, take someone else’s great idea and spin it in a centrifuge to extract the good stuff and then pass the detritus along to someone else to deal with. As a career developer turned developer berating apparatus, I have seen my fair share of cobbled together dung heaps. In fact, I would be proud to have someone call some of my solutions a cobbled together dung heap.

Bad Process? Just Add Software

Many years ago I was asked to put together a simple proof of concept application showing code movement using VSS automation. The phrase “simple proof of concept” should have sent me running. Of course, I’m assuming that the me of 5 years ago had any sense in his head.

I’ll back up a minute and let you see the whole situation. This organization had a complex development lifecycle. Developers were frequently developing several releases into the future in a combination of major and minor releases. Hot fixes would go out in production and when the next major release went out the icon be cornflower blue but the hot fix would be nowhere to be found. The hot fix would be hot fixed into production and the icon would be back to alice blue. The situation was, in short, a nightmare. What makes the nightmare worse was that it was a process nightmare: development practices in this organization dictated that multiple releases be developed simultaneously by separate teams.

Think about complex, line of business, software. Important software. Software that is core to your business. Imagine that three separate teams of developers are actively developing new features and fixes at the same time in three separate source trees. This is exactly what was going on at this organization. My job was to automate the problem away with software.

Bad Software? Just Add More Software

This organization was heavily invested in using Visual Source Safe. For those of you young enough to have worked with modern version control systems (such as a team of scribes working around the clock), let me tell you about VSS. VSS exists entirely on the client computer. VSS’s source control mojo operates through a network share using a combination of black magic and pure luck. Like all things that work with luck, it fails. It is safer to print your source code, shred it, and burn the paper shreds that to keep your code in VSS.

Despite the pain of VSS, VSS was the platform. How do you automate a platform when there is no server-side component? You install a VSS client on a server and you begin automating it. Thankfully VSS supplied an API – a poorly documented COM+ API.

I created a proof of concept application that showed code motion through various stages of development in VSS. The client was happy. The client signed a statement of work. We took their money and strode purposefully towards what appeared to be a set of rotating blades.

As the project unfolded it became very apparent that no amount of software could solve this problem. The problem was process, not software. Valiantly, foolishly, 26ishly, I soldiered forward. I worked 14 hour days. I build a cobbled together GUI that attempted to model a software development process that was beyond complicated. The GUI was cumbersome to use and brittle to code. A small change in one line of code meant that 17 other bugs showed up in various parts of the program. There were easily 100,000 lines of code in what should have been a simple management application. Edge cases lead to other edge cases. After one marathon 24 hour programming session (after four 18 hour days) the GUI tool finally worked. The users could configure a process. A process that potentially didn’t work.

Throwing Worse Code After Bad

The actual automation was a supposed to be easy. The users could apply VSS labels and the automated software would, in theory, pick up the changes within 15 minutes. The automated software would pick up the changes on a regular basis but it frequently exploded in a fiery error when confronted with real world problems. When two teams are actively developing against the same code base, code merges don’t go very well.

The process was intrinsically human but the client wanted automation. Being young and stupid, I didn’t know enough to say no. I cobbled together a working knowledge of text parsing and attempted out outfox the people who wrote the VSS Merge Tool. Armed with a fistful of academic papers, articles, and APIs I attempted to build a better merge tool using existing libraries.

At the end of the day, the tool built a log of errors using the unified diff format (which no human can read), collected the log and emailed a copy of it to the developers who committed the conflicting files. Or, it would have had anyone used the software.

Didn’t You Read the Manual

I actually wrote a software manual. It was actually a good manual. Want to know how I know? I gave the manual to the project manager and had him create instructional videos. They worked.

The project sponsor was promoted before the project was completed. The primary users transferred to another department. The new users were too busy to read the manual and expected the software to work easily and obviously. Who wouldn’t, right?

The GUI tool looked like the directory structure in VSS, but it didn’t reflect the changes in VSS, it simply let the users configure how the tool should behave. Sound confusing? It was.

At the end of the day, I had written a complicated manual for a complex piece of software that actually worked. It was a cobbled together solution built out of several hundred thousand lines of buggy .NET 1.1 code sitting on top of a buggy COM+ API intended to solve a buggy process. It’s a miracle that this software abomination wasn’t fueled by the shattered dreams of orphans.

Silent Rage Would Be Better Than This

I said that was the end of the day. It wasn’t. I got one support call about the product about 3 months after it had been delivered, signed off, and paid for. I went back to the client’s location and sat down with the current team to learn their problems. None of them knew how to use the software. No original team members were around who had been involved in the original project. All of the original documentation had been lost in some kind of bit rot tar pit.

My beautiful, cobbled together, duct tape and baling wire solution would never see the light of day. It may have been ugly, but it worked. There’s nothing like feeling the triumph of having saved the day and then having that day get flushed right down the toilet.

Calling Other MacGyvers

Since Brent Ozar stole my default victim, Matt Nowack, I’m going to have to find more victims. I’m going to point the finger of shame at TJay Belt, Grant Fritchey, and Alex Moore.

You’re probably asking, “Jeremiah, why the heck would I ever need a table of minutes?” Well, dear reader, I’m going to tell you that. Please stop interrupting.

Let’s say you have a report. This report shows the sum of sales per 5 minute increment. You could do a lot of trickery with math to make this report happen, doing things like this to get the 5 minute interval:

SELECT
    (DATEPART(mi, CAST('2009-01-01 00:01:00' as datetime))+4) / 5 * 5 AS [05]

It works, but it’s ugly as sin. I said to myself, “Self, there has to be a better way.” Turns out that there is a better way: table valued functions!

I created a table valued function to return 60 rows, one for each minute. It also rounds to the nearest 5, 10, 15, and 30 minute intervals. This makes it possible to change the reporting interval very easily by using a join. Check it out:

SELECT DATEPART(hh, s.SaleTime) AS TheHour,
  mt.Five AS NearestFive,
  SUM(s.SalesAmount) AS AmountSold
FROM dbo.Sales AS s
INNER JOIN dbo.MinutesTable() AS mt ON DATEPART(mi, s.SaleTime) = mt.[Minute]
WHERE -- something is true
GROUP BY DATEPART(hh, s.SaleTime), mt.Five

Why do I call this the lazy way to do this? Because now that I’ve written it once I never have to do it again. I can add new columns to dbo.MinutesTable() without having to worry about breaking anything or copying code incorrectly from one query to another.

Here’s the code to create the function:

CREATE FUNCTION dbo.MinutesTable ()
RETURNS @minutes TABLE (
  [Minute] TINYINT,
  Five TINYINT,
  Ten TINYINT,
  Fifteen TINYINT,
  Thirty TINYINT
)
AS BEGIN

INSERT INTO @minutes VALUES (0,60,60,60,60)
                            (1,5,10,15,30),
                            (2,5,10,15,30),
                            /* you get the gist of it */
                            (59,60,60,60,60);

RETURN ;

END

Update: Thanks to Brad Schulz this has gone for a single post about my laziness to an example of a refactoring you can make. If you ever see a table being generated like this, you can take a look at it and determine how you can change it into an inline select. The query optimizer is going to do something completely different for each plan. With the inline select, SQL Server is able to determine that there are 60 rows in our table and can build a much more efficient execution plan.

CREATE FUNCTION [dbo].[MinutesTable] ()
RETURNS TABLE
AS 

RETURN
  SELECT  0 AS [Minute], 60 AS Five, 50 AS Ten, 60 AS Fifteen, 60 AS Thirty
  UNION ALL
  SELECT  1, 5, 10, 15, 30
  UNION ALL
  /* ... this still makes sense, right? ... */
  UNION ALL
  SELECT  59, 60, 60, 60, 60 ;
GO

For this example, I’m using the Northwind database, but you could do this on any database.

The first thing to do is put an index on the OrderDate column:

CREATE INDEX IX_Orders_OrderDate
ON dbo.Orders (OrderDate) ;

Take a look at this first query:

SELECT  COUNT(*)
FROM    dbo.Orders AS o
WHERE   DATEADD(MM, 2, o.OrderDate) < '1997-05-04' ;

Scanning the stars... or something.

The database is forcing a table scan because of the use of the DATEADD function on an indexed column. SQL Server will have to compute the value of every OrderDate plus 2 months in the database and then compare it to May 4th, 1997.

What happens if we flip this around? What if we change our query so that we’re doing the math on our input value of May 4th, 1997?

SELECT  COUNT(*)
FROM    dbo.Orders AS o
WHERE   o.OrderDate < DATEADD(MM, -2, '1997-05-04') ;

Seek and ye shall find

So, there you have it: another easy fix that can save you a lot of headache when you’re trying to tune queries.

I originally installed OS X using the one click installer from EnterpriseDB. Unfortunately, the installer hung while attempting to finish the installation process and they only thing to do was to roll back the install. I attempted to build from macports, but that proved to be a huge pain in my ass and reminded me a little bit too much of using Linux, so I scrapped that idea as well. I started browsing the EnterpriseDB installation notes and came across an unattended install. Since my installation failed trying to launch the final interactive stage of the installation, I said to myself “Self, I bet we should try this.”

1. Download PostgreSQL from EnterpriseDB http://www.enterprisedb.com/products/pgdownload.do#osx
2. Mount the OS X disk image file
3. In the Terminal, change to the directory where the image file was mounted:
   cd /Volumes/PostgreSQL\ 8.4.2-1/
4. Start the unattended installation
   sudo ./postgresql-8.4.2-1-osx.app/Contents/MacOS/installbuilder.sh
--mode unattended
5. Once the installation has finished, add the following line to your .profile:
   source /Library/PostgreSQL/8.4/pg_env.sh
   If you’ve never created your .profile you can do the following in a terminal window:
   1. touch ~/.profile
      This will create the file if it doesn’t exist and will only change the last modification date if it does.
   2. open /Applications/TextEdit.app .profile
      This opens your .profile in the TextEdit application
6. Save and close your .profile
7. Create a new database user.
   createuser [YOUR NAME HERE] --pwprompt --username=postgres
   

   This could be anyone...

8. When you’re prompted for the “Password:”, use “postgres”. This is the default password for the postgres superuser. Because you’ve just created another superuser, you’ll never have to touch this account again, but you should probably look up some fantastic documentation and learn how to change the password.
   

   I'm a real live boy!

There you have it: how to set up and install PostgreSQL on OS X. If you want to continue forward and install a Ruby library to interact with PostgreSQL, just type
sudo gem install postgres-pr to install the pure Ruby version PostgreSQL driver. If you want to install one of the C-based drivers, you’ll need to mess around with macports.

Note: EnterpriseDB make and distribute a binary version of PostgreSQL for a variety of platforms. In addition, they provide paid support and also offer a variety of enterprise features. They have in no way paid me for this post.

A Plea for Plain English – Tony Davis’s “A Plea for Plain English” rings home with me. Far too much writing is full of heavy, pompous words used purely to make the author feel smarter. Joseph Conrad – one of the greatest writers of the English language – was not a native speaker. Yet he wrote with a simplicity, grace, and style that is still unequaled. While we all can’t be Joseph Conrad, we can all strive to write in clear, concise, readable prose. Technical writing doesn’t need to be dry, writing effective prose can be just as much an art form as creating a brilliant short story, novel, painting, photograph, or song.

I’ve been getting a lot more interested in mathematics. Not just how they related to computers, but also how mathematics relate to design. Design, art, and aesthetics are partially governed by universal principles. There are ratios that are more visually pleasing simply because we find them in nature. Being aware of these ratios helps us create effective designs that draw in the reader and hold them to the content.

B-movies. I watch a lot of old slasher, exploitation, b-movies.

Flash, iPad, Standards – Jeffrey Zeldman talks about why the total lack of Flash on the iPad is a good thing: it provides an incredibly compelling, public, reason for designers and developers to abandon proprietary formats like Flash and Silverlight and focus on open standards. Some people would argue that a lack of Flash would kill the device but on the flip side what can you do in Flash that people need to do that you can’t do with JavaScript, HTML, and CSS? Combine that with the in browser relational data storage that HTML5 provides and there is no reason to use a proprietary graphics engine apart from vector graphics bullshittery/professional masturbation.

We have a slow running query that frequently times out when run by the users. A quick look at the query told me what was wrong. I found this clause in the middle of an otherwise simple query:

        LEFT JOIN (
                   SELECT DISTINCT
                          key,
                          column_a
                   FROM   ImportantData
                   WHERE  FieldName = 'VALUES'
                   UNION ALL
                   SELECT DISTINCT
                          key,
                          column_a
                   FROM   ImportantData_History
                   WHERE  FieldName = 'VALUES'
                  ) AS t ON other.key = t.key
        LEFT JOIN (
                   SELECT DISTINCT
                          key,
                          column_b
                   FROM   ImportantData
                   WHERE  FieldName = 'VALUESXY'
                   UNION ALL
                   SELECT DISTINCT
                          key,
                          column_b
                   FROM   ImportantData_History
                   WHERE  FieldName = 'VALUESXY'
                  ) AS t2 ON other.key = t2.key

The problem with this query is that we’re reading each table twice for the same data. I checked in the execution plan and, sure enough, SQL Server was performing two scans on the underlying data. After some careful thought I realized that I could accomplish the same thing with a single query:

        LEFT JOIN (
                   SELECT key,
                          VALUES AS column_a,
                          VALUESXY AS column_b
                   FROM   (
                           SELECT DISTINCT
                                  key,
                                  FieldName,
                                  CASE WHEN FieldName = 'VALUES'
                                       THEN column_a
                                       WHEN FieldName = 'VALUESXY'
                                       THEN column_b
                                  END AS content
                           FROM   ImportantData
                           WHERE  FieldName = 'VALUES'
                                  OR FieldName = 'VALUESXY'
                           UNION ALL
                           SELECT DISTINCT
                                  key,
                                  FieldName,
                                  CASE WHEN FieldName = 'VALUES'
                                       THEN column_a
                                       WHEN FieldName = 'VALUESXY'
                                       THEN column_b
                                  END AS content
                           FROM   ImportantData_History
                           WHERE  FieldName = 'VALUES'
                                  OR FieldName = 'VALUESXY'
                          ) AS source PIVOT( MAX(CONTENT) FOR FieldName IN ([VALUES],
                                                              [VALUESXY]) ) AS pvt
                  ) AS t ON other.key= t.key

The upside to this unreadable pile of junk is that it scans each table once and only once. This reduces the load on disk, the amount of data that needs to be read into memory, and it most likely reduces the amount of data stored in memory. And, while this only reduced the estimated query cost by 40 (580 down to 540), we all know that the query cost is only one factor in what actually affects the overall run time of a query.

Many readers are probably mortified that there’s a SELECT DISTINCT in this query. I am too. That being said, I plucked the low-hanging fruit and managed to get a huge performance boost as a result – query execution time went from over 1 minute to 14 seconds. With an improvement like that, removing that distinct isn’t on the radar any more.

One immediate thing I look for in an execution plan is a Key Lookup. In a Key Lookup operation, SQL Server has to reference the clustered index on the table because a value it’s looking for is not present in the index that was used to find the row.

Normally, when you look at the Key Lookup, you will see a list of output columns. This is a great way to help you modify your indexes and make sure that you can avoid these extra disk hits by sacrificing a tiny bit of storage space.

A Normal Output List from a Query Plan

Today, while troubleshooting a query that is never finishing, I ran across this gem.

Empty. Bereft of Meaning. Nothing.

There’s no output list in the Key Lookup. I, wrongly, thought that Key Lookups would always include an output list. What’s going on here?

Well, here’s the scoop. I have the following, sample, table:

CREATE TABLE dbo.ServiceList (
  ServiceListId BIGINT NOT NULL PRIMARY KEY,
  BillId BIGINT NOT NULL,
  ServiceMonth DATETIME
);

In this query, ServiceList is referenced through a CROSS APPLY’d inline function. The inline function references the BillId and ServiceListId. There’s an index on ServiceList that contains both of these columns:

CREATE INDEX IX_ServiceList_BillId ON dbo.ServiceList
(
  BillId
)
INCLUDE (ServiceListId, ServiceMonth);

This index is correctly being used by the query optimizer but in the past it’s only been used to perform lookups and push data to screen (or somewhere). So, what we’re seeing here makes sense. The CROSS APPLY needs to reference the BillId so the compiler checks the situation out and decides to use IX_ServiceList_BillId in order get the BillId. Since the index only includes ServiceListId, it has to perform a Key Lookup.

Protip: included data is only included in the index; it is not indexed.

To avoid this kind of behavior, I need to change this index to actually index on the combination of BillId and ServiceListId like so:

CREATE INDEX IX_ServiceList_BillId ON dbo.ServiceList
(
  BillId,
  ServiceListId
)
INCLUDE (ServiceMonth);