Building A Better “Where” Clause

Over my career I have learned a lot about SQL Server, unfortunately, sometimes some fundamental ideas get forgotten. This weekend I went to the Jacksonville Code Camp 2009 where I sat in on a session titled “building a better where clause” here are a few of my notes in case anyone forgot of never knew them.

- Try to use indexed fields whenever possible. Searching indexes in much, much faster than un-indexed fields. This means if you are not searching on the primary or foreign key, consider whether the field in questions should either be searched in the first place. If it does, consider adding an index on that field.

- When writing your where clause, take the order into account. Place the expressions most likely to return false first. Take a look at the below queries. Both return the same results, however unless people with the last name “smith” make up 50% of the table the first expression would evaluate faster than the second.

SELECT fname, lname, gender FROM People where lname = ‘smith’ AND gender = ‘male’
SELECT fname, lname, gender FROM People where gender = ‘male’ AND lname = ‘smith’

This is because as the system evaluates each row, deciding if it should be included in the result set, it will move on to the next row as soon as part of the where clause evaluates to false.

- Avoid wrapping table columns listed in the where clause in a function, doing so prevents SQL Server from using any indexes, which means a significant drop in performance.

- Avoid wrapping table columns with upper() or lower() in the where clause unless you SQL server is set to be case sensitive. By default SQL Server is not case sensitive, meaning the functions are not needed. As noted below using these functions prevent the use of indexes.

- Avoid using any form of “NOT”, including not null if possible as all forms of not also prevent indexes from being used.

- When using an in() statement, if possible place the most likely to be found first. Once the comparison evaluates to true the system can immediately move on to the next record.

Hudson, More than Just a River in New York….

In the past I have written at length about the value of implementing a few select agile principals. One of these principals, “Continuous Integration” (CI) provides so much “bang for your buck”, it just can’t be ignored.

For so long I have wanted to implement continuous integration practices. Unfortunately, until now I never was able to get it going. I did some research on “Cruise Control.net” but all I read pointed to a long and drawn out installation and configuration process. Given the fact that this was a one man show and I would likely not be able to obtain the sys admin support it became less likely that this would be an option. Another issue was the requirement of a server with IIS, meaning I could not simply install it on my workstation for initial testing. Needless to say this was a deal breaker. I looked into another product “TeamCity”, which sounded easier to get up and running but, again it required IIS.

With this I started trying to implement my CI plans via hook scripts attached to our SVN server. These scripts though appeared to work were slow and seemed like a house of cards ready. The idea was to add a post-commit script to automatically checkout new work. The script next called on NAnt to build the project. NANT would also call on FxCop (for static code analysis) and NUnit (for the eventual unit tests we would have). Again, this worked, kind of, but I now felt comfortable sending it into the wild. After all this the project got shelved for a time.

Then one fateful day over lunch I read an article featured in CODE magazine titled “Hudson Continuous Integration Server” by Eric Anderson. Hudson is a continuous integration tool written in Java. As a committed .Net enthusiast I must say I was skeptical. I must say I was pretty lucky to find such a jewel. Within an hour of reading the article I was up and running. I won’t go into details of specific confirmation steps; Mr. Anderson does this so well. Instead I will discuss my impressions of the application and the overall contribution it has made.
Since the app is Java, you must have a recent edition of the JVM. As far as I can tell this is real requirement. I simply put in a few command line statements, and the server installed was installed on my workstation. The whole thing is packaged up into a Java .jar file meaning everything needed is included in this file, upon execution it unpacks and install into memory, no permanent installation is required.

A few more statements and the system was alive and available for configuration via web browser (there is a built in web server). The URL was http://localhost:8080/. The article used a repo from Google Code, with that and of course visual studio I was ready to go. I copied the configuration examples in the article and did a few tests.
Soon I had it linked to a few departmental repositories and now anytime anyone checked in code Hudson would check out a copy and attempt to compile reporting the results. This is a huge advancement. Until now team members checked in code whenever, not knowing that they had forgotten to add one file or another to the repo. Now within a minute Hudson checks out the new changes and attempts to compile them. After I got the system installed on one of our servers, Hudson was be able to include email support, notifying the team of each good or bad build.

Before this system was in place people would check stuff in, then others might check more stuff in, finally at some point someone did an update and their build was broken, stopping work. What followed was usually someone stomping around saying that this system sucks. After that we would attempt to identify the team member who had the missing files and get them added to the repo. Knowing immediately if you broke the build will reduce these incidents significantly, in most cases the team member who forgot a file will know and fix it before anyone else in the team has a problem. Our team needed something better, and here it is.
During my research of “ CruiseControl.net” I became aware of a really nice satellite application enter CCTray. CCTray is a small windows app that sits in the system tray and pulls a feed from the CI server for all projects a specific user subscribes to; it then shows red, yellow, or green depending on the state of the build. The CODE magazine article outlined the basics of what needed to be done to use CCTray with Husdon and again, after a little tinkering we were up and running.

Now that we have been running Hudson for well over a month, I have increased the scope of its responsibilities considerably. I use it to generate change reports for me monthly reports, to analyze and report on adherence to coding standards via its FxCop, and report on compiler warnings reported by MSBuild.
I am totally satisfied with this product and encourage anyone else who wants to implement CI on a shoestring to give it a look.