The effects of DISTINCT in a SQL query

Earlier today, I had a great Twitter conversation with Tim Mitchell (@Tim_Mitchell), Jorge Segarra (@SQLChicken), and Jack Corbett (@unclebiguns) about the use of DISTINCT in a SQL Server query. By the way, this is yet another example of how Twitter can be used in a good and positive way within the work environment and within the SQL Server Community; Twitter is not just a time sink that squeezes productivity out of your day as some claim.

Many people (not those mentioned previously) resort to using the DISTINCT keyword in a SELECT statement to remove duplicates returned by a query. The fact that the resultset has duplicates is frequently (though not always) the result of a poor database design, an ineffective query, or both. In any case, issuing the query without the DISTINCT keyword yields more rows than expected or needed so the keyword is employed to limit what is returned to the user.

So what’s the problem? Is using DISTINCT that really bad? Why not just type the 9 additional keystrokes that will save us a lot of hard work? (8 letters and a space. You thought I miscounted, didn’t you?)

Running without the DISTINCT keyword

To answer that question, let’s have a look at a quick and simple example. Let’s consider the following query run against the AdventureWorks database.

withoutdistinct

As you can see from the query and its execution plan above, SQL Server resolves this query using a Clustered Index Scan, aka a table scan. Generally speaking, table scans are not something you want to see in your query plans but in this case it makes sense. I’m asking for 4 columns from all rows in the table.

Now let’s look at the cost of the subtree below. We see that the estimated subtree cost for the SELECT statement is 0.438585.

Looking at the execution time information from SET STATISTICS TIME ON we see the following information.

SQL Server Execution Times:
CPU time = 15 ms,  elapsed time = 586 ms.

Running with the DISTINCT keyword

Now, let’s type those 9 additional keystrokes, adding the DISTINCT keyword into the query as shown below. Before we run this, we’ll clear out our buffers and procedure cache to make sure we’re comparing apples to apples.

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

NOTE: Do not run the aforementioned DBCC commands on a production server. If you do, your phone will ring, your pager will go vibrate, your users will have a hard time forgiving you, and performance will slow to a crawl for a little while.

withdistinct

A quick examination of the query plan reveals that a table scan is still being used to retrieve the data from the table. That’s to be expected. However, a new node has been added to the plan. A Sort node. This node is responsible for 75% of the total cost of the new query!

Looking at the node cost for the SELECT statement, we see that the cost shot up to 1.75954 from 0.438585. That’s a significant difference.

And finally comparing the time it took to resolve this query with the prior version, we can see that the total CPU time increased too.

SQL Server Execution Times:
CPU time = 78 ms,  elapsed time = 655 ms.

An exception to every rule

So, generally speaking the addition of DISTINCT to a SELECT statement will increase the workload for SQL Server. But this is not always the case. If a unique index exists that can guarantee that each row will indeed be distinct, the Sort operation can be skipped and the results immediately returned.

For example, consider the following trivial query as an example.

And compare it to the following query that includes the DISTINCT keyword.

Notice that the query plans are identical for these two queries. Why? A unique index exists on the ContactID column which guarantees that the rows will be distinct.

But wait, there’s more

These are really trivial examples of how DISTINCT can make a difference in a query plan and thus the performance of a query. In real life, very few queries are this simple. But I hope that these examples will serve to illustrate that DISTINCT does add an addtional load on the SQL Server. So, if you can do a little work up front by rewriting your query, you can avoid having use DISTINCT in your query. SQL Server will thank you for it.

For more information how a unique index can help performance, have a look at Rob Farley’s blog post entitled “Unique Indexes with GROUP BY“. It’s a good read.

Have you resorted to using DISTINCT to limit the results of a query? I’d love to hear of your experiences. Or if you’ve found an better alternative, please share.

Joe

Closing your laptop in meetings

About a year ago, a friend of mine introduced me to a weekly podcast from Manager-Tools. In each cast, Michael Auzenne and Mark Horstman share some of the lessons they’ve learned about how to be a more effective manager. These are actionable techniques rather than purely theoretical concepts. I regularly listen to one of their podcasts while I’m driving to visit a client. While I don’t necessarily agree with everything they say, most of it is spot on. It’s good stuff.

My laptop, my security blanket

Recently, I listened to a cast about how to make your meetings more effective by leaving your laptop at your desk. “That’s just crazy; that’s pure nonsense!” I thought as I listened. “What will they suggest next? Adding cursors to your stored procedures to improve performance?”

Like most professionals, I’ve sat through more meetings than I care to remember. Some are very productive. Others, not so much. They seem to go on and on like a bad movie. How could one make it through such meetings without a laptop with which to multi-task?

Having a laptop with you is like having a security blanket. When a meeting takes a non-productive detour, you can turn to your trusty laptop and get some things done. At least you’ll stay productive.

I’ve been to some meetings where I was able to reach inbox zero while others belabored a really inconsequential point way past reason. And I’m not alone. I remember in one all-day meeting, I received a flurry of emails from several others sitting in the same room. Many of those emails were in response to questions I’d sent to them days or weeks earlier.

If at first you don’t succeed…

But as crazy as Mark and Michael’s advice sounded to me, they have a proven track record of offering good advice. Could this really be true? It sounded so foreign to me.

So I decided to conduct an experiment. For the next couple of weeks I’d leave my laptop closed during meetings and devote my entire attention to the meeting. No more emails. No more remoting into a client’s system to check something. Just the meeting at hand.

At first it was tough, really tough. When a meeting seemed to take a nonproductive tangent, I instinctively reached for my laptop. Argh! It was missing in action! What had I done? Stupid, stupid, stupid!

But over the next several meetings I persisted. I stubbornly stuck to my experiment as hard as it was.

The Payoff

I found that it got easier over time. Not having the laptop in which to seek refuge forced me to mentally stay in the meeting. I was more engaged. I paid attention. I contributed more.

Soon I noticed that the meetings were better as a result. I realized that I didn’t have to sit back and allow the meeting to go astray. I could do something about it. I could subtly step in and redirect the meeting back on track by suggesting we take that point off-line. I could even do this in meetings that I didn’t run. It wasn’t very difficult; most people were buried in their laptop and not paying attention.

I also realized that what Mark and Michael had said was true. While I felt that having a laptop made me more productive, the meeting is not all about me. It’s about the group and making good decisions. If I’m being more effective as an individual, it’s detracting from the meeting and thus making the meeting less productive. That wastes a lot of other people’s time and costs the company.

When I closed my laptop, I was able to influence the meeting and the outcome for the better. I wish I’d learned this lesson years ago.

Excuses

I know what you must be thinking right about now. “Joe, you’ve spent too much time in the server room. Your brain has frostbite.” I know. I thought that at first, too. But give it a try. I think you’ll agree.

Mark and Michael talk about some excuses that people may have when you, as the leader of a meeting, decide to banish the portable computers from your meetings. I won’t recount those here. But I will give you some that immediately came to my mind along with my current thoughts on them.

  • “I really need my laptop to take notes.” That really is an excuse. Almost everyone claims to need a laptop to take meeting notes, but very few actually do take notes with it. The temptation to do other work is too great. If you really want to take notes, and I encourage you to take notes, use pen and paper. If you must have them electronically, transcribe them later.
  • “I need to be able to look up the latest figures at the meeting.” That may be, but reporting the latest figures to the group can probably easily be an action item that you take away from the meeting. Taking time during the meeting to look them up is not the best use of everyone’s time.
  • “I need to have my calendar available.” Again, coordinating a future meeting can be done after this meeting ends. Spending an extra five minutes trying to schedule another meeting takes up valuable time during this meeting. Besides, if everyone was fully engaged in this meeting and decisions could be made, perhaps another meeting wouldn’t even be necessary.

For every rule there is an exception

Like the guys from Manager-Tools, I’ve found that not having my laptop open in meetings has made the meetings more productive. I think that’ll be the norm from now on. I’m even going to add this suggestion to the How to Conduct Effective Meetings Lunch and Learn that give to companies.

There are, of course, some exceptions to this general rule. But I’m beginning to think that those are few and far between.

So what do you think? Do you take your laptop to meetings? Have you tried leaving it closed during the meetings? What has your experience been?

Revive your User Group, part III

Anyone who’s ever been to a well-run and successful user group meeting knows how valuable they can be in helping you to staying touch with others in your industry as well as helping you to stay abreast of the latest technologies. However, good user group meetings don’t just happen; there’s a lot of hard work that goes on behind the scenes before the user group meeting ever happens. In this, part three of the Revive Your User Group series, I’ll share with you some tips to help you manage your user group more successfully and with less effort on any one person’s part.

Before going any further, it is worth mentioning that as the name implies this is the third part in the series. If you’ve found this posting directly, it’d probably be worthwhile to go back and read the first two postings in the series as they really lay the groundwork for this post.

The series kicked off with a discussion of how and why User Groups should be an important part of your career development, not only from a technical and educational perspective but from a networking and “soft skills” vantage point as well. In the second installation, I shared some best practices I’d learned from my experiences running a user group.

Alright. So, how we can we be more successful running our user groups?

The load is large

When you think about all of the tasks associated with  running an user group, what comes to mind?

Well, you’ll need to find speakers for each meeting. You should have a presence on the web that should be updated regularly. You’ll need to find and coordinate meeting locations. You’ll work with sponsors to get door prizes and food delivered on time and to the right locations. There will be lots of emails flying around to help organize all this.

All of these things take time. And time is one of our most precious commodities. As a professional with paying job, you simply don’t have the time to undertake all of the activities of running a user group successfully by yourself. In trying to do so, you’ll have to cheat someone – either your family, or your work, or if nothing else your leisure time. Eventually it’ll catch up with you and you’ll not be able to carry on.

Many hands make light work

So what do we do? We get others involved. In my experience, the most effective, the most sustainable, user groups are those do not depend exclusively on one person to shoulder all of the responsibilities to keep the group going. User groups that are solely dependent on one person usually collapse at some point. When that one person changes jobs, or moves to another town, or simply burns out from the extra load she’s been carrying.

But just adding people is not enough. Everyone must know what their responsibilities are. Otherwise everyone considers himself an advisor to the group and no real work gets done.

Consider having at least three people in leadership positions. More is even better, but at least three should be part of every group. The titles don’t really matter, but for simplicity and ease of reference, I’ve provided some position titles to go along with the roles.

  • President – The face of the group who provides direction and vision for the group. He makes decisions and helps with everything that needs doing. It’s best if he has some connections to the community and potential vendor and sponsors. The president typically is the person who starts off every meeting with a brief introduction and discussion of any group business.
  • Meeting Coordinator – The guy who ensures that the meetings happen. He schedules the venue, the speakers, and even the sponsors. He coordinates the delivery of food and door prizes. This is probably the leadership position that takes the most time. The meeting coordinator should be a detail person with good connections to potential speakers.
  • Communications – The person in charge of communicating with the group. The communications role keeps everyone “in the loop” by regularly updating the web site before and after each meeting. He sends emails, posts tweets, writes blogs, etc, about the meetings and other group happenings.

These are just some to consider. The point is to share the load so that no one person has to do it all.

In the next and probably final part in this series, I’ll share with you some ways to keep users engaged.

Until then, I’d love to hear any experiences you’ve had in running a user group. If you’ve found that different roles makes sense, please share for the benefit of others out there trying to run a group.

Joe

Revive your User Group, part II

In the first part of this post, I discussed the importance of local user groups as a technical and even career resource. If you haven’t already read that post, I’d encourage you to go back and read it now since it really lays out the groundwork for this one by outlining how and why local user groups are important.

At this point, I hope we can all agree that local user groups can play an important role in helping us to furthering our careers. I’m not say that the do play that role for each of us, but that they can have a positive impact by helping us to gain knowledge in our field, to meet new people in our industry, and to stretch ourselves by making presentations.

Some Lessons Learned About Meetings

In this post, I’d like share some lessons I’ve learned along the way about how you can help to ensure that your local group’s meetings are as vibrant and enduring as possible. I’ve learned these lessons by being in the trenches myself, first as a local user group leader for the Nashville SQL Server User Group, and then as one of the leaders of the world’s largest SQL Server User Group, PASS.

Meet regularly
The more successful user groups meeting regularly. That should go without saying. And it’s almost by definition that that’s the case. But it’s really hard to get people used to coming to a user group meeting, and to feel invested in the user group, when meetings are held haphazzardly or infrequently. Check with other user groups in your area and find a regular meeting time that doesn’t conflict with something else. For example, schedule your group to meet on the third Thursday of each month.

Depending on the time of your meeting, some days of the week are less appealing that others. Wednesday evenings are generally not good user group meeting times for people who attend mid-week worship services. Here in the U.S. in the fall of the year, you’ll likely see a noticiable decrease in attendance if you meet on Monday nights due to the NFL football games.

So do some planning, come up with a time that makes sense and stick to it.

Consider lunch meetings
The defacto standard for a lot of user group meetings is to meet in the early evening on a weekday, say 6:00pm on the third Tuesday of each month. And if that’s works for your group, that’s fantastic.

But in my experience, many people are getting more and more protective of their personal and family time. They do, indeed, want to grow professionally but giving up an evening for a user group meeting may not be as alluring or even as acceptable as it once was.

My group used to meet on Thursday evenings and for years we struggled to keep a good critical mass of regular attendees. We’d average 10 to 15 people attend depending on the topic for the evening. And because of scheduling difficulties, many meetings had only at 6 or 7 people there. At that point, those who came questioned why they even bothered. Eventually the group collapsed, meeting less and less frequently until it just faded away.

When we put the back together, we decided to do things differently. We decided to meet during the lunch, from 11:30am to 1:00pm. Our first meeting back had almost 100 people. Since then we’ve averaged around 60 per meeting. People are willing to take a little extra time for lunch when it’s work related.

There is such a thing as a free lunch
Another way to attract and keep people coming to your user group meeting is to provide a meal there, especially a free meal. People love free.

At our group we have a sponsor for each meeting. The sponsor provides some kind of food and beverages. Nothing fancy, typically pizza or Bar-B-Que sandwiches and sodas. It usually costs them a couple of hundred dollars. It’s inexpensive enough that they can pay for it out of their expense account or petty cash.

And in return they get plenty of exposure and good will. They get to stand before the group for 5 minutes to introduce themselves and talk about their product or service if they’d like. Most don’t focus on that. Most just simply say ‘hi’ and that they’ll be in the back if anyone wants to learn more about them or their company. We also thank and mention the sponsors on the web site and in our email communications.

From the sponsor’s perspective, it’s a great return on investment. They get a lot of great publicity in front of people expressly interested in a certain topic or with a particular skill set for just a couple of hundred bucks.

You can also ask vendors and other organizations for door prizes. Microsoft, PASS, Quest, Red-Gate, and other SQL Server related organizations are usually more than happy to provide some kind of give-aways.

Next Up

In part three of this series, I’ll share some lessons I’ve learned about setting up the leadership and managing the group.

Until then, if you know of some other best practices for user group meetings, please share in the comments below! I and the community will thank you for it.

Cheers!

Joe

Follow

Get every new post delivered to your Inbox.