How well do you know your database server? And no!, I’m not talking about favourite colours, favourite things to do, or other first date questions here… I mean how well do you know the technology behind your database server and what makes it tick? Many developers out there focus on the web and application layers and only look to the database layer as a persistence medium. At least until they need to get a LOT of data in or out in a hurry that is…
More often than not, small companies can’t afford to have a dedicated database administrator (DBA). So it often falls to the technical operations guy/gal and the developers to manage the company database(s). I’ve been in the position of one of those developers, where looking after databases was more of an add-on task than any official duty, and that experience has taught me that a good DBA is worth their weight in gold.
Every developer should get to know at least one database technology intimately because your database can do a lot more for you than just persist data. Get to know the engine of your database server: how it works; how it makes decisions on query plans; different algorithms for joining and grouping data and how it all evolved.
As an example, have you ever written a program that processes data on-the-fly and batching bulk-inserts? Assuming all that bulk-inserted data goes into a set of tables that will later be processed for rollups, you’ll have some indexes on those tables, so that the rollups run faster… right? Well those indexes will have a dramatic performance impact on your bulk-insert operation… I’ve learned that when processing large amounts of data for crunching rollups later on… only create the indexes just before you need them and then drop the indexes again afterwards. The reason is that the bulk-insert is probably going to be the long-running operation and writing to a table with 3 indexes across columns can take 2-3 times longer than writing to a table with no indexes.
My database of preference is SQL Server because I’ve worked with Microsoft technologies all my working life and found the tool-sets generally hit the mark on best compromise between functionality and easy learning curve. SQL Server 2000 was the top dog around the time I was finding my feet and an invaluable resource was Kalen Delaney’s book Inside Microsoft SQL Server 2000. I have *huge* respect for the team that created one of the first databases that was capable of running on desktop computer hardware, whilst having a .dll/.exe footprint in the order of 10’s of Megabytes and, to this day, is capable of managing databases in the order of Terabytes!