THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

A Computer Scientist Meets T-SQL

TSQLWednesdayThere I was. A freshly printed bachelor's degree in Computer Science tucked under my arm, I walked into my First Real Job. I'd never touched the technologies I was going to work with--ASP 3.0 and SQL Server--but my employer knew that, and I figured I'd be able to pick things up relatively quickly. After all, I'd been programming since the 2nd grade, knew a number of languages (more or less), and had all of the academic background I'd ever need.

I was given a quick tour of the office, oriented by HR, and met the rest of my group. As a member of the small IT team, my coworkers included an Exchange administrator and couple of help desk support/networking guys. No other developers. I was given a cubicle (shared with a finance person) and told to start creating the company's intranet. So, I did. And with no one to learn from or bounce ideas off of, I made up my own rules.

One of the first things I was asked to create was a threaded message system so that the remote marketing and sales teams could converse with one another. I got to work on a basic (and very ugly) user interface, and then tackled the database side of the equation.

I'd never encountered a real live database before, and scarcely even knew what one was, but I took a day or two to read "Teach Yourself SQL in 24 Hours" and figured I was good to go. Somehow I recognized the need for a self-referencing table. I created it, populated it with some test data, and quickly discovered that the simple test queries I'd written to date weren't going to work. How would I navigate the hierarchy and display the messages in a threaded fashion?

After messing around for quite some time and applying all of my Computer Science prowess, I came up with an algorithm:

  1. Using a cursor, start iterating the rows in the table.
  2. Insert "parent" rows into a temp table.
  3. For each "parent" row, recurse, calling the same stored procedure again and starting at step 1.
  4. Finally, return all collected results.

With its nested cursors and various work being done in temp tables, this algorithm scaled so very well that shortly after rolling the message board out to production I got to work on implementing a cache so that users wouldn't have to wait several seconds when making a web request.

Clearly, I'd done something very wrong. And I knew it. I just didn't know enough to know how to find the right answer.

Much later I learned the correct term for what I'd created--an adjacency list hierarchy--and I learned about other methods of modeling hierarchies, including materialized paths and nested sets. I learned that many of the lessons I'd been taught in school--where the curriculum was heavily biased toward procedural languages--didn't apply well to SQL databases.

And most importantly I learned how to ask (and answer!) questions on online forums. Being a team of one doesn't mean that you need to work in isolation. There is a huge community of people online who want to help you succeed. Finding these forums (and newsgroups. RIP, NNTP!) was a revelation. The ability to talk shop with people who understood what I was trying to do and how best to do it was invaluable to my learning how to be a better developer and not just a student of Computer Science.

Many years later and I still get stuck on difficult problems, but these days I don't try to do everything in isolation. I know better than that. I reach out to my network and take advantage of some of the great minds I'm lucky enough to have access to. And you can, too. Next time you find yourself with a less than ideal solution, swallow your pride and ask for help.

And don't forget to help someone else in return. As much as you'll learn from the people answering your questions, you'll get even more value from puzzling over the numerous problems that other people face on a day to day basis. Solving as many problems as you can--your own and those of others--is in my opinion the fastest way to truly master a given technology.

Enjoy your journey--and always remember that you don't have to go it alone.

Published Tuesday, August 09, 2011 9:58 PM by Adam Machanic

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Bob said:

Thanks for sharing your failures!  I torched my way down the same path a decade or 2 ago.  I still cringe when I think about the stuff I did wrong and every now and then I get a panicky feeling that I might still be doing it and am too ignorant to know!  What I can always fall back on is my eagerness to learn new stuff and willingness to stay involved in internet discussions.  I had the great luck to work in a terrific team for 5 years and learned a great deal about solid database design but even in the best teams there is a tendency for group-think.  Getting out on the internet at sites like this and especially Stack Overflow allows me to see what other people are doing.  New methods and techniques.  and I'm always pleased when I'm able to contribute in a meaningful way to advancement of knowledge.

August 9, 2011 11:10 PM
 

Nitin said:

I have great respect for @mrdenny,@peschkaj,@adammachanic and @sqlpoolboy (in the order I've exposed/learn't from you guys) for what youve achieved in your Young age!! I hope to follow your path. Someday I will meet you and probably shake your hands

August 9, 2011 11:33 PM
 

Florian Reischl said:

Thanks for sharing Adam. Reminds me of an alike situation some years ago.

"Next time you find yourself with a less than ideal solution, swallow your pride and ask for help."

I guess that's the most important sentence of your post. Nobody is perfect and the highest perfection we can reach is, to realize our imperfection.

"Being a team of one doesn't mean that you need to work in isolation."

Completely confirmed, too! I can't say how much I've learned from people like you and others in forums, like SSC.

Greets

Flo

August 10, 2011 4:10 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement