Wasted Bandwidth

One thing hasn’t fundamentally changed since the pre-historic days of our kind: the method by which we acquire the goods we need to survive and be secure and comfortable. We refer to what they did as hunting and gathering, and what we do as shopping, but it is fundamentally the same. Now it is just easier. And safer. Well, safer outside of the mall parking lot anyway.

Hunting: make a list, head out and procure the items on the list. The method often preferred by men, the pre-historic hunter.
Gathering: go from here to there and anywhere in between looking for what looks good and might be useful.

I certainly am in hunting mode at times, and gathering mode at others.

Those who know me well have heard me say many times for many different reasons, “I’m not very athletic.” From simple math to merely catching a football, it applies.

Well today I tag myself as “digital athlete”. That’s not to say I’m great at everything digital - is Phelps good at all things athletic? No - but what skills I do have are pretty much all digital. Having found no pioneer in my Google search tonight, I am calling myself a digital athlete, and I hope to be introduced to many more of you who may bear the same title. And bear it proudly, friends.

I recently discovered that Oshkosh, WI (my town) has been captured by Google Street View. Of course, since the day I learned that I have to figure out when it was captured.

Well today I had a few extra minutes while I waited for some painfully slow software updates, and I decided to resume my mission of figuring out when the cameras drove through town. From earlier research I already know it is late-September or early-October 2007.

So I decided to try to find a bank that might have the date on its marquee. (Banks show the date on there, don’t they? Or just time and temperature?) Well, I tried a few of the banks I could think of off the top of my head, and at the third one I found a friend of mine driving in his car. And, yes, I was a little jealous that he was on Google Street View. Why? I’m a nerd and a Google fanboy. Anyway, so I followed him down the street, and found myself driving in my pickup!

It appears that I’m heading back to work from lunch, so this must have been grabbed around 12:30 PM. Date still TBD.

Today I was given the chance to complain that stored procedures are called stored procedures. I’ve never liked that name. I’d much prefer they had been named “compiled queries” because that’s what they are. This led to me having the chance to say that derived tables should have been called “named queries” because that’s what they are.

What’s a derived table? Think of it as a query with a name (hence “named queries”).

I’m not going to build tables and data to illustrate and compare why they are often so much faster than temp tables. Thus I have no schema to compare this example against, and I apologize if I miss something silly and give you an example that doesn’t work. If that happens, just comment on this post and I’ll fix it.

Here’s the set-up: I want to send a mailing to all customers who have purchased in the last 90 days. I will use my customer table and my order table. (My customers have only one address — this post does not attempt to get into the problem of many addresses per customer.)

I’ve seen this done frequently like this:

––create proc stuff deleted for brevity
select distinct CustomerNumber into #RecentOrders from Orders where OrderDate >= DATEADD(dd, -90, GETDATE())

select CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerZIP
from Customer
inner join #RecentOrders ro
on ro.CustomerNumber = #RecentOrders.CustomerNumber
––end of temp table example

OK, that works. But this works better:

––begin derived table (er, named query) example, mumbo jumbo excluded
select CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerZIP
from Customer
inner join (select CustomerNumber from Orders where OrderDate >= DATEADD(dd, -90, GETDATE())) as Recent
on Customer.CustomerNumber = Recent.CustomerID
––end example, the named query (er, derived table) is bold

Every time you use a temp table you should carefully consider your options. Might you want a view instead? Or this, a derived table.

I can’t post the example I once sent to my team — it has too much proprietary information. But I once accomplished the same exact task this way that was once done using temp tables. Temp tables took over 9 minutes. Derived, 2.

I can’t explain why it is so much faster. I’m not a hardcore SQL engineer. But I think it has to do with tempdb and disk I/O.

Try it; you’ll like it.

I just noticed a very well done spam blog comment. Somebody appears to have some sort of bot that is scouring blogs for key words. It found mattmutz.com yesterday because of my post about my new iPod touch.

But I know it’s spam. Because when I went to their site to check it out (btw I didn’t click a link, I typed it in, because I don’t want to promote blog spamming) and It is a very well done list of posts about iPod, iPhone, Google Phone, last.fm, etc. A bunch of popular subjects. All of the posts list the title of the blog and have a sentence that looks human-typed but is clearly spam. Because a human wouldn’t post this:

[name/link deleted] created an interesting post today on An AdMob update
Here’s a short outline
Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

View the rest of this top post [link deleted]