How to tune a SQL script

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/how_to_tune_a_sql_script.htm]

Performance is critical, and slow SQL procs are often a huge performance bottleneck. The problem is how to measure it. Microsoft provides SQL Profiler to help with that.

While I'm certainly no DBA, here's a basic tutorial on how to tune a SQL script (check here for more on SQL profiler).

1. Get a database with production-sized data.

Because performance can very exponentially (i.e. there may be twice as much data, but it goes twenty times slower), you absolutely need to test with production-sized data, else all your measurements could be off.

2. Be able to run the SQL script in an isolated, deterministic environment

We don't want to chase ghosts, so make sure you have a deterministic environment: (A) no one else is changing the script-under-test, (B) you can call the script with a single SQL command (like exec for a SP, or select for a function), (B) you can call the script repeatedly and get the same functional result every time. Once the script works, we can make it work fast.

3. Open up SQL Profiler for a tuning template.

SQL profiler lets you measure how fast each SQL command took. This is invaluable if you have a complicated script with many sub-commands. It's almost like stepping through the debugger where you can evaluate line-by-line.

1. Open up SQL Profiler (either from SQL Studio > Tools > SQL Server Profiler, or from the Start > Programs menu).
2. In SQL Profiler, go to File > New Trace, and connect as the SA user.
3. In the "Use the template", specify "Tuning"
4. 
5. Profiler starts recording every command being sent to the database server. To filter by the specific SPID that you're running your SP from, run the SP_WHO command in your SQL Studio window to get the SPID, and then in SQL profiler:
   1. Pause the SQL Profiler trace
   2. Goto File > Properties
   3. A new window opens up, go to the "Events Selection" tab
   4. Select SPID, and in the filter on the right enter the value into the "Equals" treeview option.
   5. 

 

4. Run your SQL statements and check the Profiler

Simply run your SQL statements in SQL studio, and check the results in SQL profiler.

The tuning template in profiler will record every command and sub-command being run. It will show the total duration (in milliseconds) for each line, and the full SQL text of what was run. This allows you to identify the bottlenecks, and tune those by changing the SQL code to something more optimal.

5. Compare the output to ensure same functionality

If you don't have an exhaustive suite of database tests, you can still help ensure that your proc is functionally equivalent by comparing the original SQL output (before tuning) to the new SQL output (after tuning). For example, you could save the output resultset as a file and then use a file-diff tool like Beyond Compare to ensure they're identical.

Summary

Again, books could be written on how to SQL tune. This is just a brief high-level tutorial to get you started.

 

---

Living in Chicago and interested in a great company? Check out the careers at Paylocity.

Development Trivia

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/development_trivia.htm]

Real-world development has so many miscellaneous facts and trivia, so I'm going to experiment writing a "Friday Trivia" blog post. The intent is to discuss trivia that arose during the week.

Specify the default editor for a file

In windows explorer, right click a file, select "open with" > "choose program", and then check the checkbox that says "Always use the selected program to open this kind of file". You can then automatically open the file (in the designated editor) just by running System.Diagnostics.Process.Start(strFullFileName).

Selecting Comments with XPath

XPath is a powerful way to select nodes from an XML document. While XPath commonly selects normal nodes, you can also use it to select elements. For example, you may want to select a comment if you're inserting a node into a document, and want the comment to be a placeHolder for where you append that node.

      XmlDocument xDoc = new XmlDocument();
      xDoc.LoadXml(@"
       
            Additional Notes
           
            Text notes
       
        ");

      XmlNode n = xDoc.SelectSingleNode("/employees/comment()");

 

MSBuild - Command line properties that contain CSV strings

In MSBuild, you can specify properties via a or with the /p: switch in the command line. These two are supposed to be identical, but they're not. You can specify a CSV string in a PropertyGroup just fine, but you can't in the command line switch because it interprets commas as a property delimiter (just like semi-colons). A work around is to use another character (like a hyphen '-'), or have the MSBuild script import the PropertyGroup from a separate file and write the CSV string to that file. Perhaps there's also a way to escape the comma (maybe with a carrot '^')

---

Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.

12 more things that will really help your team

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/12_more_things_that_will_really_help_your_team.htm]

Every enterprise software department should have some basic processes and standards. Joel Spolsky has a great list here. There are some additional things that will really help your team:

1. A team vision - What really is your team striving for?
2. The willingness to pay for talent - Because a good developer is much more productive than an average developer, it is only cost-effective to pay for good talent. A CXO whose business model is to get cheap developers will probably be as successful as a cook who tries to save money by buying moldy ingredients. On a related note, a department needs to retain it's top talent. Managers need to ask themselves - why would our top developers stay here?
3. Thought Leadership - By contributing to the technical community, via articles, presentations, blogs, open-source, etc..., a department (1) shows that they're doing innovative work, (2) shows that they have the intellectual muscle to break new ground, and (3) creates a sense of company pride. This helps market the company, making it more attractive to top talent.
4. Innovative Process - Are your processes better than they were a year ago, or did someone set things up and now you're done "wasting time" on process? Good process, like continuous builds, automation, and utilities will save your team an immeasurable amount of time. A department that refuses to invest in its process is doomed.
5. A way to enforce standardization - such as code generation, code reviews, resuable components, and static code analysis.
6. Tools beyond just Visual Studio, like a file comparison tool (like Beyond Compare), and a non-VSS source control system (we use Subversion). Of course, there are tons of tools out there:
   • Scott's list of awesome tools - http://www.hanselman.com/blog/ScottHanselmans2005UltimateDeveloperAndPowerUsersToolList.aspx
   • GnuWin32 tools - http://gnuwin32.sourceforge.net/packages.html
   • Windows Server 2003 Resource Kit Tools - http://www.microsoft.com/downloads/details.aspx?FamilyID=9d467a69-57ff-4ae7-96ee-b18c4790cffd&displaylang=en
   • Sys Internals - http://www.microsoft.com/technet/sysinternals/default.mspx
7. Ways to track progress - How do you know how much more development time a feature needs? Ideally your department has some sort of time-tracking system, with categories for how time is spent. This lets you see how many hours a feature initially took, including the time spent in design, and fixing bugs. If management wants a knee-jerk mentality that always delays doing any good process or innovation because "we don't have the time", a tracking system will give you the hard data to make the business case that you save more time by doing the feature correctly first. I.e. If you can show management that developers spend 50% of their time fixing bugs (both a  high-risk and hard-to-estimate activity), then you can at least explain, in business terms, why it's best to invest in good architecture and process. (If management still doesn't see this, then your department will have a bigger problem).
8. Automated Deployment - Ultimately every step in deployment should be able to be automated. Doing manual deployment is just too risky and time consuming. It's not just your IT department that needs this - your QA needs automated deployment for performance or functional testing (because QA's environment should mimic Production). Ideally your continuous build will create an install package that your deployment framework can then just install from the command line. Maybe you need to serialize your deployment strategy to an XML file, and pass this in via the command line, maybe you do some manual steps once (like copying your config files to their appropriate locations), but ultimately you don't want to be wasting time manually doing a mission-critical step that could be automated. If a department has some special exception case that they need to do a manual deployment - that's their choice - but at least they'll also have the choice of doing it automatically too.
9. Hiring of college grads -  If your company is growing and investing in its future, getting smart college grads is a good investment. Most of the brand name companies, like Microsoft or the big consulting companies, have a place for college grads because they know that in a few short years, the smart ones will already be delivering impact. It can also be much easier to home-grow a star than try to poach an established senior star from another company.
10. Have knowledge collaboration - Wikis (like FlexWiki or SharePoint) are just too valuable to pass up on. If your department doesn't have a wiki "because we can't afford it", then get the open-source FlexWiki. It's free and runs on a Windows XP box. You can have it set up in less than an hour.
11. Have mentors - Good companies invest in their employees, and one of the best ways for a software company to do that is by having senior devs mentor junior devs. You don't need a suffocating formal process, even just having a senior dev consistently review someone's code, encourage them to invest in certain technologies, or just meet them for a casual lunch, is a good start.
12. Have career goals - do you know where you, as a developer, want to be in 5 years? Ideally your department has a clearly defined career path. "Better at the current technologies" isn't really specific enough. At Deloitte and CSC (my previous companies), every consultant needed to list several short and long term goals for their yearly review. This forced the consultant to think  about their future, and gave the consultant something to shoot for.

In hindsight, it's east to say "we should have XYZ", but most departments lack these things. In my experience, it's a combo of: (1) an inexperienced department that doesn't know what they're looking for (I've certainly been there when I was younger), (2) lack of budget to build this infrastructure, or (3) lack of motivation for developers to build this infrastructure. If your department is missing a lot of things on this list, and you think it would help your specific case, you can try gradually chipping away at them one-by-one.

Once you start doing a good process, you wonder how you ever could have have lived without it.

---

Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.

How to encourage standardization

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/how_to_encourage_standardization.htm]

You cannot control people, you can merely encourage an option to be in their best interests.

With respect to code, we all want standard, consistent code - but the question is how? Especially if you have a group of individuals who all are used to doing things their own way. There are some ways to encourage standardization:

1. Code Reviews (XP uses the ultimate code-review: peer programming). This will encourage people to share ideas and "melt" their concepts together, ideally picking the best of everyone's world.
2. Refactoring and Reusable components. If you eliminate the possibility of bad variation, your encourage good standardization. For example, there are many different ways to format a DateTime. But if you have a strongly-typed DateTime control that handles the formatting and validation for you, and developers simply drag and drop that control onto their page, you've got a win-win: (1) it's faster for the dev to create, and (2) all your DateTimes are now standardized.
3. Static Code Analysis (like FxCop, built into VS 2005). This is like compiler warnings on steroids. This gives you proactive warnings about your code, like naming standards and style. It's great if you're starting to code from scratch, but could give excessive noise if used on legacy code. It's also hard to enforce - i.e. FxCop gives you back a 1000 warnings, and most developers then just dismiss it as noise.
4. Code Generated templates based on model-driven languages - Using a tool like CodeSmith, you can take an XML file and generate a bunch of ascii text - like C# and HTML code. This shrinks the gap between what it takes to describe a feature (a minimalist XML file), and what it takes to implement the feature (the generated C# and HTML).

A lot of this ties into software factories. I'm currently reading Practical Software Factories in .NET by Gunther Lenz and Christoph Wienands - it provides a lot more insight about this.

Making a transparent Gif with the free Paint.Net

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/making_a_transparent_gif_with_the_free_paintnet.htm]

Sometimes a developer will need to make a transparent GIF, such as for a web page. This should be trivial - you essentially want to say "make every pixel that is color X be transparent", but most standard development tools (VS, Paint, etc...) don't let you do this. While there are lots of expensive graphics programs out there, most developers don't have these.

An easy way to make a transparent gif is using the free Paint.Net (written entirely in .Net).

1. Download Paint.Net. It's a pretty good, free, graphics editing tool.
2. Open up your image. Save it as a gif.
3. In the Tool section, use the "Magic Wand" feature.
4. Set it's tolerance to '0%'
5. Drag it over the section you want to make transparent. The magic wand catches an entire region of adjacent, same-color-range, pixels
6. Once the region is selected, then hit the delete key. [Updated 12/11/2007]

I find this convenient for simple web imaging needs. Also, Paint.Net has a ton of other features, like gradients, blends, and special effects.

 

---

Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.

Why would someone work for your company?

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/why_would_someone_work_for_your_company.htm]

I mentioned last month how everyone likes to say that they recruit the best, but that's just silly. Not every company can all have the best developers. Especially in a good job market, it merits asking - Why would someone work for your company?

At Paylocity, because we're always hiring for top talent, we constantly focus on this question.

Ultimately, you need a niche, such as:

• High salary
• Enjoyable work - the company does the exact kind of thing you like.
• Brand name recognition - i.e. the opportunity to work for a Microsoft, Google, Amazon, IBM, etc...
• Good work-life balance
• Job security - such as a government job where you likely won't get fired.
• Good learning opportunity - like consulting companies that fly you around the country to work with the latest and greatest technology
• Future potential - maybe a startup that you think will hit it big
• Good coworkers - whether it's just team chemistry, or there's a star you want to work with (i.e. ThoughtWorks having Martin Fowler)

In my experience, most companies think they have most of these things when they don't. Sometimes they think they have enough of some niches such that they don't need others (i.e. "we don't need competitive salaries because we have XYZ instead").

Obviously big salaries are the easiest thing to point to. Hiring managers may insist they can't get good recruits because their budget simply doesn't allow them to be competitive -  and there's substance to that. However, the interesting thing is that several of the things on this list can be addressed without your CXO changing their budget:

Niche	How to get it without changing your budget
Enjoyable work	Focus on new problems and process, and not repetitive, copy & paste tasks. It's a win-win: developers will like the challenge more, and managers will like the better code that results from it. Managers can try delegating work items to developers based on their interests.
Good work-life balance	Giving developers laptops lets them work from home. I've found that people are far more willing to do "homework" in the evening (after they've had downtime or dinner with their families) in the comfort of their own home, as opposed to staying late at the office. It also gives you the option of working from home. For example, at Paylocity we often let developers work from home on Tuesday and Friday (we use laptops and instant messenger, and we have mature developers who don't abuse this). If you're firm doesn't require travel - it's much easier.
Good learning opportunity	Good developers have the intrinsic need to grow and learn. Managers can champion having estimates that include even a 5% research cushion, purchasing a requested technical book, or making the business case to upper CXOs how the new technology helps the bottom line. Any manager that stifles that for any reason will simply push their best developers away. Such excuses may include: "we don't have time", "that's not in the requirement", "just do it this way because I said so", "this approach worked 5 years ago", "your job is to support the business, not research technology", etc...
Good coworkers	A manager has tremendous influence over their team. First, they can lead by example - not gossiping, respecting team mates, not showing favoritism, etc... They can also refuse to hire someone who is socially incompatible (i.e. don't hire a jerk), such that the team doesn't get tainted. If a people-problem does arise, such as someone sends out a dumb email or makes an inappropriate comment, a leader can proactively isolate the incident and encourage quick resolution (as opposed to "waiting to see what happens").

 

I'm proud to say that at Paylocity, we do these things - we work with cutting edge technology, have an amazing work-life balance that allows developers to stay mentally fresh for long-term development, always encourage learning, and have the best team chemistry I have personally seen.

 

---

Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.

Why are the good devs ten times faster than the average ones?

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/why_are_the_good_devs_ten_times_faster_than_the_average_ones.htm]

A lot of developer commentators point out that the best developers can easily be ten times as productive as the average ones. Why is this? I think for two main reasons:

First, unlike many jobs that are limited by physical ability, a developer is limited primarily by their own thought, which has a much broader range of ability. In other words:

1. Humans are physically in the same range. An average person may be able to list 200 lbs, a very strong person may be able to list 400 lbs, or about twice as much. Look at every physical record - how fast people run, how precise they throw, how balanced they are, and the range of people's abilities is a relatively narrow spectrum.
2. Most jobs are limited by physical ability. This is obvious for manual labor jobs, but even other jobs - a salesperson can only be in one place at a time, or a cook can only work as fast as their hands let them. Sure someone who knows what to do will do it faster and better, but you're limited by how fast you can physically do certain tasks.
3. Software engineering is limited by your mental ability. While you need to physically type code in the keyboard (and there are shortcuts for that), most development time is spent thinking, not typing. You can essentially code as fast as you can think. And unlike traditional jobs, you can essentially use more than two hands by automating processes on other machines, using code generation, refactoring your code, making reusable libraries, etc...
4. Mental ability varies far more than physical ability. A smart math student could multiple solve an equation ten times faster than an average student - if the average student could even solve the equation in the first place. Practically, your best developers will solve problems that your average developers lack the ability to even describe, and thus potentially would never solve. For example, I sometimes read the CLR blogs at Microsoft, and I don't even have the background or depth to describe the problem domain, let alone solve it in a timely manner.

So, fundamentally, software engineering is not traditional physical jobs where two average guys can build something as fast as one good guy. There's a similar phenomena for all "Thought Work". For example, the best books sell 100 times more copies than the average ones.

Second - in software engineering, because there is such a low entry bar, there is a huge gap between the amateurs and professionals. For example, in order to become a Civil Engineer, you'd first need to go through several years of schooling at a credited University. There is a high bar to meet. But many "programmers" just start playing around with Visual Studio and gradually move from hobbyists to full time developers. So, practically there are a ton of developers out there that have the mental ability, but just lack the training and education such that they're not nearly as productive as star developers. 

---

 

Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.