The table with a tiny key

Whenever I model a new database table I like to start with an auto-incrementing primary key column. Maybe it's the Ruby on Rails convention creeping up on me, but I've even adopted the minimalist id as my default name for such columns. A lot of times I go ahead and make it an int. Sounds pretty good, huh? After all, the good old int is the de facto standard when it comes to integer data types. What else would you use, right?

Since you ask, there are a few alternatives out there: smallints, tinyints and bigints. Let's start with bigints. I don't think the name does it justice. Bigints aren't just big - they are massive. Massive in the range they can represent but also pretty hefty in the storage space they require: 8 bytes instead of the usual 4 an int takes. How can an additional 4 bytes be a problem? We'll come to that in a second but for right now, just ask yourself if your table will eventually have more than 2 billion rows. In most cases, that's a very reasonable upper limit. For example, if you started with an empty table and added a new row every second it would still take you approximately 60 years to reach that limit.

OK, so you probably don't need a bigint unless you're Google, Flickr or maybe Walmart. What about the other data types? Smallints and tinyints can't represent the large range of numbers that ints can. To refresh your memory, smallints go up to a little over 32,000 and of course tinyints a mere 255. And that's precisely where things get interesting. You see, the truth is, any database has a bunch of tables that have very few rows each. They might be domain tables like MaritalStatus ("Single", "Married", "Divorced", etc.) or PhoneType ("Work", "Home" or "Cell/Mobile"). Other tables might be limited by the nature of the data they store. For example, a table called WorldAirports is probably going to have less than 50,000 rows. My point is why use an int as a unique identifier for each row if you're not going to have that many rows? Why not use a smallint and save 2 bytes per row. Even better, use a tinyint and chop 3 bytes off every row. If that doesn't seem like a lot, try looking at it this way: a tinyint requires 75% less space than an int.

Now, wait a minute! What's the point of optimizing a table if it only has a few rows anyway? The total savings are going to be negligable! Well, not quite... The magic of these small tables is that their primary keys get used as foreign keys with the same data type in much larger tables. Let's say you have a PhoneDirectory table with one million phone numbers and each phone number has a PhoneTypeId that's a foreign key from PhoneType. The PhoneDirectory.PhoneTypeId column alone would take up 4MB of storage as an int but only 1MB as a tinyint. Not bad, huh?

But disk space is cheap and it's measured in terabytes these days. One megabyte, four megabytes, who cares? While there's certainly a lot to be said for throwing more hardware (in this case, storage) at a problem, those savings can add up and remember that a smaller database doesn't just mean less disk usage. It also means faster backups and restores, less replication errors, and smaller bandwidth usage for remote archiving and DR. Did I mention better performance? That's right, smaller rows means you can fit more of them in each page of data and therefore reduce the number of reads and writes.

Next time you create a table, have a think about that and choose your data types accordingly. Or get started on refactoring your existing tables right now.

Data Object Interfaces in Linq

I'm currently working on a database where a lot of tables have a common group of columns, like this:

created_on datetime not null,
created_by smallint not null,
updated_on datetime not null,
updated_by smallint not null

The smallints are foreign keys from the User table. Together they provide first level auditability to answer questions like "When was each row created and by whom?" and "Which user made the last edit to this entity?". They're present in every table that has CRUD operations exposed in the application (that includes User itself). While the columns are always exactly the same, I prefer to keep them inline rather than make a separate table since I don't think they form an entity in their own right.

Anyway, there are places in the application where I just want to deal with those columns regardless of which table they came from. The footer that says "Created by user1 on ..." is a perfect example. The aim is to have all the classes that Visual Studio automatically generates implement an interface that encapsulates the columns above. Something like this:

public interface AuditInformation {
    DateTime created_on { get; }
    short created_by { get; }
    DateTime updated_on { get; }
    short updated_on { get; }
}

How can we achieve that? Remember that those classes are re-written every time you use the design surface so directly editing them is a clear no-no. Well, if all your tables use columns defined exactly the same, the classes already implement the interface - you just have to let the compiler know that. (This is where being very consistent in your naming and choice of datatypes can pay off nicely.)

Instead of editing the classes themselves, we can extend them using partial definitions. Consider a Product table and class. We can add this code to a separate file:

public partial class Product : AuditInformation { }

Is that it? Actually yes! Like I said, if you created your columns correctly, the auto-generated class already implements the interface because it contains exactly the right properties. Now your CreatedByFooter user control (or helper method) can reference the interface without caring which class is really being used.

For extra bonus points notice that you aren't limited to the column values. You can also use the relationship properties generated from the foreign keys. In other words, the Product class probably contains a User property that corresponds to the created_by column (returning a User object in its full glory instead of a plain user id value). You can add that property to the interface too. The only tricky bit is that you have to manually fix the relationship names in Visual Studio when you import the tables. In the example above, by default, the created_by relationship is called User and the updated_by User1 (sometimes, the other way around). That's not very intuitive so rename the relationships to something sensible like CreatedBy and UpdatedBy and add those same names to the interface.

That's it, job done!

The 15 Minute Backup Strategy

For a long time our only backup strategy was to take a full backup of the database every night and try not to worry about it too much. Eventually the database grew to a point where the job that copied the backups to off-site storage couldn't keep up any more, not to mention that all that copying was consuming some serious bandwidth. Then some bright spark realized we couldn't really afford to lose a whole day of work if the server ever crashed and we had to restore the last backup. We obviously needed a better plan.

While any good DBA will tell you there are a million things to consider when deciding on your backup strategy, we needed a plan that was simple enough for a developer to implement and maintain. It also had to create small(er) backups and provide better coverage. Too complicated? Well, we came up with a pretty good solution.

To improve our coverage, we could take more frequent transaction log backups since they are small and fast even with a reasonably large database. The only problem is that log backups have to be restored sequentially on top of the correct full backup. If one of those files is corrupt, all the data from that file onwards is lost. So, we didn't want too many log backups.

On the other hand, we had to reduce the frequency of the full backups to ease the load on the bandwidth. That's when we turned to differential backups to fill the gap. Differential backups are cumulative i.e., they include any changes that occurred since the last full backup. To restore, you only need to apply the last differential on top of the correct full backup. The downside is that, over time, the differential backup will grow to the same size as the full.

This lead to our three-pronged approach. We decided to have:

• full backups once a week
• differential backups every 4 hours
• transaction log backups every 15 minutes
  

In the event of a crash, we lose at most 15 minutes worth of data. The backup set shrunk in size from 7 full backups a week to 1 full backup, 1 differential backup and, at most, 15 transaction log backups (to cover the period between differentials). The restore procedure is a bit more complicated than just restoring a single full backup but not by much. There are up to 17 files to be restored and this can easily be automated by a stored procedure or script. Overall, it's a pretty good improvement over the previous strategy.

Does ASPState need full recovery?

I prefer to use SQL to keep session data in my ASP.NET applications. In order to do so, you need to create the state database (usually called ASPState) in SQL Server. Have you noticed that its transaction log will keep growing unless you take regular backups?

I don't know about you, but I don't really need backups of my session database (the main application database is a different matter, of course). The data that I store in session is very transient. If it ever crashes, I'm not going to try to recover the data. I'm happy to recreate the (empty) database and let the users come back to the application with a brand new session.

So, what's the solution? Well, I set the ASPState database to use the simple recovery model. Once that's done, the transaction log won't be a problem any more. Additionally, if you already have a large transaction log, you might want to shrink it after you change the recovery model.

Auto Login in Firefox

Here's the setup: I'm building a web application that's only accessed by users within a Windows network. The application authenticates the users by their Windows logins and to make things interesting, the users prefer Firefox.

If they used IE, the browser would automatically send the user credentials to the web server (the same credentials used to log in to the Windows domain in the first place). Firefox, on the other hand, will ask the user to re-enter their username and password. A small annoyance but annoying just the same.

Well, there's a way to avoid it. Go to about:config (type that into the address bar), look for a setting called network.automatic-ntlm-auth.trusted-uris and add the URL of the application as the value. Problem solved!

It's not a novel solution – in fact it's described on a number of different websites. I happened to find it on Stack Overflow.

Don't forget to show the page

If you ever find yourself writing Postscript documents by hand, or creating an application that writes them, don't forget to use the showpage operator. You typically want to call it at the end of each page in your document since it tells the Postscript interpreter to grab all the things you've drawn up to that point and actually render them into a page.

Yes, I know it sounds obvious - who would forget to do that, right? All I can say is it happened to me recently. Both GSview and an office laser printer I was using to test the document displayed it fine without including the operator. I guess they figured, "Well, you've drawn a bunch of things on the page, and I've reached the end of the document, surely you want me to print something so I'll go ahead even though you didn't tell me to do that".

My problem was that the production press the document was intended for didn't use the same common sense approach. It RIP'ed the document without errors and then just sat there (with a smug look on its face, I'm sure). It makes sense, once you realize what's going on, but it took quite a while to figure it out. Anyway, remember to add showpage and everything will be fine.

Joining Tables... in Perl!

Is it just me or is joining tables in SQL just like processing text files in Perl? OK, maybe when I put it like that they don't sound very similar but there's actually a connection. Let me start from the beginning.

There are a number of ways your database server can join tables to execute a query. In SQL Server, for example, there's the nested loops join, the hash join and the merge join. What do you mean you haven't heard of those types of joins? What about the inner and the outer join, you say. They're valid as well, of course, but in this case I'm talking about physical, not logical joins.

The difference is that logical joins tell the database which rows from table A you want to join to which other rows from table B to produce the output. On the other hand, physical joins tell the server how the rows should be matched up. In other words, what algorithm to use. The reason you might not have heard of physical joins is that you normally don't have to specify them at all. SQL is basically a declarative programming language. Instead of telling the machine what to do (as you would in C, Java, VB, or Python) you just tell it what you want and it works out the best way to obtain that data for you. One of the main steps for the machine is deciding on an execution plan for your query. That's where the physical joins come into play. They are some of the "building blocks" used in the plan.

Although SQL Server already picks physical joins for each query, you can add hints to tell it to use different ones. The reason you'd want to do that is usually performance but, for the record, let me say that you should always avoid hints whenever possible. In the long run, the server stands a better chance than you of finding the best joins possible.

OK, but what has this got to do with Perl? Even better, what has it got to do with processing text files in Perl? Hold on, I'm getting there. Perl is commonly used to process large input files with regular data (delimited, fixed-width, you get the picture). "Processing" the file might mean looking at each line to extract only a few columns or filtering out certain lines depending on the values they contain. For example, a Perl script might run through a web server log and extract only the Timestamp and Requested URL fields for lines where the Timestamp was between 9:00 and 11:00. Doesn't that sound like a query? The input file is pretty much like a very simple database table.

Processing becomes more interesting when you have more than one input file. That's when you have to combine data from the multiple inputs, something you could call "joining". Maybe you have two web servers and you want to combine both log files into a single output. In Perl, you could open the input files and merge the contents according to the Timestamp field. That's essentially what the merge join does. The key factor is that the input files are already sorted which makes merging pretty simple.

If the inputs are not ordered, and especially if one of the inputs is a lot bigger than the other, the hash join would be a more appropriate choice. For example, let's say you are still looking at those web server logs but now you decide you also want to include the name of each server in the output. The problem is the input only has the IP address of the server. The mapping of IPs to names is stored in a separate file. The trivial solution in Perl is to first read the mapping file, load the contents into a hash and then process the other files, using the hash to output the correct names for each input line. The main limitation is the fact that one of the files is loaded into memory. Try using one that's really large and you'll see your machine crawl to a standstill.

Last but certainly not least, there's the nested loops join. This particular workhorse does exactly what it says on the label. Essentially, the join looks at the first input and for each line it runs through the entire second input. To put it another way, it sets up one loop nested inside the other. There are basically no limitations on how your input files can be organized.

What am I trying to say with all this? Well, you certainly shouldn't stop using SQL and start writing the joins yourself in Perl. But, I think it helps to understand what big complex software like a database server is doing behind the scenes. Not only does it make it all seem less like magic, it also gives you more knowledge to deal with those pesky performance problems.