Don't Use Select *

Something you see in a lot of database access code is a select statement that looks something like this:

SELECT * FROM TableName WHERE ...

While there's technically nothing wrong with it, using the SELECT * syntax could be stealing away precious performance from your application, and even it it's not now, it might someday soon.

When you do a SELECT * against a database table it tells the database to return all the fields from the selected rows in the given table. The problem with doing that is that you rarely actually need all the fields for any one page and moving around all that extra data can really slow things down. This is especially true if your database and web server run on separate computers since the extra data then needs to be transferred over the network.

The response that I usually get when I tell people this is that the table is small and it doesn't really matter. I wholeheartedly disagree. Even if you're retrieving data from a table that only contains a few fields, how do you know that table will only contain those fields in the future? Very few databases and web development projects are static. Things change and your application needs to be able to roll with the punches. Here's an example to illustrate my point.

Let's say that you've got an employee table in your database that lists your current employees. You'll obviously want a page on your intranet (and maybe even your public site) that lists these employees. Assume the employee table contains just a few fields: id, first name, last name, department, and phone number. If you were to build a simple phone list that lists employees by department and provides their phone numbers you might use a database query something like this:

SELECT * FROM Employee

as opposed to typing out what you really mean:

SELECT id, first_name, last_name, department, phone_number FROM Employee

Right now there's really no difference between the two, but if six months down the road you decide to add a picture of each employee to the database, are you going to remember to go back and change the SQL query? If not, now with every call to that page the web server requests every field and is transferred a picture of each employee that it doesn't even use. Since pictures tend to be large, you'd be transferring a lot of data for no reason!

It may take a few extra seconds to type out the field names, but it's a good habit to get into and the performance savings can make it well worth the time.

Basic authentication vs. NT Challenge and Response

When you password protect a web page using Internet Service Manager, you have the option of choosing either Basic authentication or NT Challenge and Response (aka: Integrated Windows authentication). The difference in the two methods is in the way the username and passwords are transmitted over the Internet. NT Challenge and Response encrypts the password so malicious snoopers can not intercept and use the information. Basic authentication sends the password as plain text. While it would be great to use NT Challenge and Response for all secured web pages, the only web browsers that currently support this protocol are Internet Explorer 3 and higher. If you might have users with other web browsers, your only choice is Basic authentication.

If you would like to have a secure website take advantage of using encrypted usernames and passwords but still want to be compatible with Netscape browsers, you can use Basic authentication over SSL. Using Secure Sockets will encrypt the user name and password but at the same time will still let Netscape browsers use your site, the best of both worlds.