Anti-Patterns and Malpractices, Volume 1: Tumbling Data

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

Paul Nielsen — Sat, 15 Dec 2007 16:37:44 GMT

I've noticed this pattern - I call it the data bucket pattern but it's the same idea - is popular among prior Access programmers who used lots of make-table queries to move the data to the desired answer. And I've seen some production databases that are set up this way - different table for the same data but different workflow states of the data, so the data moves from table to table in the lifecycle of the data.

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

Adam Machanic — Sat, 15 Dec 2007 16:49:38 GMT

Paul,

You and Scott think alike -- he also mentioned Access as the source of some of this, and I forgot to write that in the post. Thanks for bringing that up!

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

Denis Gobo — Sat, 15 Dec 2007 19:00:56 GMT

No matter how far down the wrong road you've gone, turn back.

Turkish Proverb

I think people get emotionally attached to their code, they think it is their baby and feel hesitant to throw it out (understandibly so). They try to patch it here, patch it there until their code looks like a quilt. We have all done this, I have done it too. I spend 2 days on that code, I am not throwing it out, I know I can fix it.....

This is simillar to the fact that you have slowly changing dimensions in data warehousing, this thing changes too.....only for the worst. Every change you add makes it worse. You have to be bitten a couple of times until you realize that rewriting is the only option. Unfortunately it takes time (and money) until you realize that. Enough rambling, time for a Crown Royal with ice....:-)

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

dmarkle — Sat, 15 Dec 2007 19:13:32 GMT

Adam:

Good example. I'll say, though, that this can be a good way for beginners to START to code. They might not have the skills to have thought out the whole thing beforehand in a set-based way. This is surely the way I started to write SQL waay back when I was first learning it. But the missing step for a beginner is this: REFACTOR!

Beginners: If you're learning, start out by gathering your datasets independently, then figure out how to join them together into a concise, high-performance query. Once you do do that a few times, you'll gain the skills to do as Adam says and think out more involved queries in just a few minutes. Don't just stop with the "Tumbling Data" pattern and call it "good enough".

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

finckle — Sat, 15 Dec 2007 20:17:55 GMT

I agree with everything in the post but I sometimes prefer it when something (reports?) is written in the Tumbling Data Anti-Pattern way! I often have to look at stored procs used by reporting services reports, the reports are complex beasts and the stored procs producing them typically consists of a single very complex SELECT statement.

These SELECT statements take time to understand and making a change to such an animal really can be a daunting task. I get a sinking feeling when I open one of these and have to make a change, on the other hand, when the stored proc was done using the Tumbling Data Anti-Pattern I can more easily understand how the original developer was thinking and subsequently my change becomes easier. Looking at little snippets and understanding them as you go are just easier IMHO.

I guess these complex SELECT statements can be documented and that would ease my frustrations. In an environment where complex reports are requested everyday and sometimes never looked at again that just isn’t going to happen.

Perhaps the Anti-Patterns is more self documenting than the alternative?

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

Adam Machanic — Sun, 16 Dec 2007 01:58:36 GMT

Finkle:

I agree that this kind of code CAN be easier to understand, but not necessarily so. I wish I could post a sample of some of the real code I've recently been working with so that you can see just how bad it can get. My re-written, single SELECT query is heavily commented and I guarantee that the next developer who sees it will only have to pull out 1/16 of the hair that I had to pull out while reading the gigantic temp table-based solution :-)

Plus, there's the performance issue. When it takes an hour to run each step because there are no proper predicates used, it can be very difficult to debug and/or figure out what's going on...

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

Scott Diehl — Mon, 17 Dec 2007 03:28:31 GMT

Credit for naming an anti-pattern - a dubious honor, but I'll take what I can get.

We can all agree that the tumbling data anti-pattern is inefficient; as Adam mentions, the unnecessary hammering of hardware resources can make you very unpopular with both end users and other developers sharing your environment.

The more subtle danger, however, is the habits of mind produced by repeatedly implementing such an approach to complex query logic. What happens when you have to extend such a query? Seems to make good sense, at the time, to fill up another temp table (or..egads...truncate and fill up some of those "reporting" tables you've left lying about), tacking the output onto the end of the existing statements. It's like query crack - just one more temp table, please - when a more effective, multi-module design might actually be both less complex AND more self-documenting.

I've seen a number reports based on this anti-pattern lately. When you see a query moving on to it's fifth temp table, after pumping output from one #NotAnotherOne to another like some out-of-control chain of Unix shell scripts, well, be very afraid. And talk to the developer about implementing a nice derived table, maybe.

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

Merrill Aldrich — Mon, 17 Dec 2007 17:24:46 GMT

Well put - plus "tumbling" makes me thing of the sound forgotten change makes in the dryer; a server probably sounds similar when running a bunch of these temp-table-based solutions at the same time!

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

Madhivanan — Tue, 18 Dec 2007 13:43:15 GMT

Newbies always start with this approach.

I remember now how a newbie wrote VB code to find out date_joined value for a particular employee

The code was something like

set empname=txt_emp.text

Set Rs=con.execute("Select * from employees")

While Rs.count>0

if Rs("emp_name")=empname then

messagebox rs("date_joined")

end if

Rs.movenext

Loop

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

Joe Celko — Tue, 18 Dec 2007 15:44:44 GMT

This pattern is the old "scratch tape" model of electronic data processing (EDP). Tape drives were expensive, so small shops would have only two or three of them. You would hang a source tape on drive #1, and extract a subset to drive#2. Rewind #1 and either hang a new scratch tape or write a file separator on #2 and extract the next subset.

We had no physical choice about this with magnetic tapes. What I cannot figure out is why people who are not that old got the idea.

A year in review, The 21 + 1 best blog posts on SQLBlog

Denis Gobo — Thu, 27 Dec 2007 21:11:42 GMT

A year in review, The 21 + 1 best blog posts on SQLBlog Best posts according to me, it might have been

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

Jeff — Thu, 14 Feb 2008 02:23:23 GMT

Great post, Adam. And even though many great SQL programmers indeed embrace the "set-based" concept of doing things all in one big step, that doesn't mean that we still don't need to stop before coding and really think about what we are trying to do, and the best way to get it done. I discuss that a bit here (it's an older post, but I am not sure if you've seen it):

http://weblogs.sqlteam.com/jeffs/archive/2007/04/30/60192.aspx

The lesson, as always: if you spend more time coding than thinking, in ANY language, you are doing something wrong.

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

Ward Pond — Tue, 26 Feb 2008 15:10:55 GMT

Great post, Adam.

My only quibble is that the Supremes would've been ridden out of Motown on a rail if they recorded "Tumbling Tumbleweeds," which was most famously recorded by the Sons of the Pioneers.

It's also an important component of the soundtrack of The Big Lebowski.

http://en.wikipedia.org/wiki/Tumbling_Tumbleweeds

-wp

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

Ward Pond — Tue, 26 Feb 2008 15:12:57 GMT

Okay.. I'm wrong.. Wikipedia says the Supremes covered it in '95.

Wonders never cease..

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

Adam Machanic — Wed, 27 Feb 2008 16:43:50 GMT

Well it's my fault for not properly researching the origins of the song. Clearly it is not the Supremes who should get the credit for the lyrics ... thanks for pointing that out!

Building complex data sets

SimonS Blog on SQL Server Stuff — Tue, 01 Apr 2008 22:49:57 GMT

If you've got a complex data set to produce how should you do it. Adam Machanic has posted about

re: Anti-Patterns and Malpractices, Volume 1: Tumbling Data

SDC — Wed, 02 Apr 2008 17:14:05 GMT

Access is evil. It's the Access of Evil (TM)