Tom Mollerus' Weblog

I've just come back from an CommonSpot Advanced Developer's Training class, where some people expressed the thought that server-side validation wasn't required as long as you have sufficient client-side validation through JavaScript. Then this morning I read Mark Kruger's excellent (if rather unfortunate) example of why you should always implement server-side data validation in your applications. Always remember that users, whether friendly or malicious, can submit anything they please via form submissions, URL query strings, or even cookies.

The discussion over on Ben Nadel's posting on tips for writing better, more-optimized SQL is pretty interesting, with a number of users writing in with good tips on SQL optimization and even db optimization. I suggested that any column used in a WHERE clause should be indexed, which in truth is rather a strong statement; I suppose it's more accurate to say that any column in a WHERE clause should be considered for indexing. The possible drawback to the over-use of indexes, as some people pointed out, is that indexes can possibly slow down your database by taking up too much disk space or by being so large that they take too much overhead for the database to maintain.

While I suppose that's technically possible, and those folks say they've observed it, I have to say that I've never experienced that kind of problem, even on high-traffic tables with several millions of records. In the end, you just have to experiment to see whether an index makes your query run faster. But do consider indexes for more than your foreign keys.

People have been asking for some solid technical rules or advice on when to implement indexes, so I thought I'd post links to the best guides on indexes:

• Microsoft TechNet article on indexes in SQL Server: http://technet.microsoft.com/en-us/library/ms190197.aspx
• MySQL documentation on indexing: http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

There's a pretty good discussion that I've been part of on Andrew Powell's blog and Ben Nadel's blog about whether a web developer should have to write SQL code. Andrew says that developers should concentrate on web code and either leave the SQL in the expert hands of DBAs or should use an ORM that abstracts the SQL code for the developer. Ben thinks that developers should have some understanding of SQL for the sake of writing better applications.

I think that web developers should know SQL because SQL is a great, and sometimes necessary, skill to have throughout your career. Simply put, DBAs (and ORM software) arent' available for every project. While I will glady (eagerly! quickly!) hand over the responsibility for database creation, maintenance, and query input/output to a DBA if one is available, most of the time such a person isn't there. I've worked in medium-sized companies, tiny companies, and by myself, and for most every project I've been the only resource available to manage databases and SQL. I've had to learn it. And even if you, as a developer, currently work at a company that has a DBA, I feel confident in saying that you're not going to find as many opportunities with other companies if you don't know any SQL.

Andrew does make the good point that developers probably aren't going to know SQL as well as a DBA will, because the DBA makes a focus on the SQL language. I would agree. And I also agree with his suggestion that you should never be afraid to ask how to do something, or to do it better. But I disagree with the contention that developers shouldn't need to know SQL. I think that the web industry gets a lot more good out of developers' SQL than bad.

A nice little coincidence just happened for me: right after my boss had emailed me asking if a certain click-tracking report in our site's admin section could run any faster, the developer next to me, who was looking through some of our existing code, asked me what this "NOLOCK" mention in an MS-SQL query meant. Talk about perfect timing! I probably wouldn't have remembered NOLOCK if he hadn't brought it up.

The other day, our company's managed hosting provider alerted me to the fact that the hard drive on our SQL 2000 database server was running out of filespace. So I logged on to the server to check out what was going on. What I found was that besides accumulating a few extra testing databases over the years, certain of our high-traffic databases were taking up quite a lot of space on the hard drive. And while looking around, I remembered a technique that I haven't had to use for years that helped me to regain a lot of the space on the drive: "shrinking" SQL Server databases.

Boy, am I in a much better mood today than in the last two days.

I had come into the office over the weekend to re-install ColdFusion MX 7 on our production server, thinking that switching to single-server mode from multiple-server mode might solve some of the strange, unpredictable errors that we were seeing on our live site. The re-install, which we had practiced on our dev server first, went without a hitch and the site seemed to perform quite well. But as Saturday progressed to Sunday, the site saw even more errors than before. The same happened as the week began on Monday, with our site becoming increasingly slow and unstable.

The errors that we saw varied, and happened in all different parts of the site, but had one major theme in common: they all had to do with queries. For instance, we saw errors saying that "qQuiz" wasn't defined when we tried to loop over a query of that name-- but the cfquery tag for qQuiz, which executed before the loop, didn't throw an error. We also saw errors stating that queries were timing out, and even the infamous "null null" errors, which you see if you're using cferror templates to provide nicely-formatted errors for your users. Our pages, especially the queries themselves, were taking an amazingly long time to execute-- anywhere from 2 to 15 seconds per query. Try as a might, I couldn't get a handle on what the problem was...

I was taught a simple lesson in the power of database indexes today. Here's how it happened: for a while, one of the sites I'm responsible for seemed to become slower and slower over time, even to the point of timing out during a database query. I always figured that it was because the web server was over 5 years old and needed a re-install or something like that. So, I switched to a new server and migrated the database to the new machine, which has more RAM and an has a more modern version of MySQL.

But guess what occurred on the new server when I started testing on it? The site and its database were as slow as ever. At that point I could no longer blame the problem on the machine, so that left... me. I had to figure that code optimization and/or database optimization were the only issues that could be at fault. So, I fired up a browser and looked at the MySQL docs. I knew enough to start investigating indexes to get things working a little faster, so one of the first lines I saw was this:

Use of indexes on the relevant columns is the best way to improve the performance of SELECT operations.

I'm definitely no database administrator, but my suggestion would be to create a two-column index for each join that you perform in your application. One column would contain the primary key of the table that you're indexing, and the other column would be the foreign key in that same table.

For instance, to create an index on a table of users that you join to a table of accounts via a foreign key in the users table, you'd write a MySQL index like so:

CREATE INDEX users_accounts ON users (userID, accountID)

Do this for each of your joins and your database should be nice and speedy for a good, long while.