Today I want to talk about estimating SQL Work. For me, this is the most difficult thing to do. I have colleagues who have their own estimation techniques that work for them.
Take a gut estimate of what it will take, multiply by some factor (Some multiply by 2, others by 3), because your gut is always too conservative. They have found that some factor times their gut is consistently close.
Compare it to something you’ve done in the past. Then add or subtract based on what you consider to be a difference.
Use a banded approach based on complexity. For example, when estimating stored procedures, use 1 hour for simple, 2 hours for harder, 4 hours for medium, and 8 hours for complex. The thought is that things will balance out overall. You may estimate high for some and low for others. The hope is to average out over time.
A banded approach also works for table or view creation. It takes longer for tables with more columns; especially if you have a lot of check constraints, default constraints, and foreign key constraints.
One are that is more difficult to estimate would be database migrations. This takes more work because you should not assume any particular design being in place at the time your migration is executed. It should always interrogate the database to find the state before making changes.
The only thing I have found that helps bring sanity to estimation is to break things out by table, function, procedure, migration, and data. A separate estimation for each object in any of these areas, for me, tends to be more reliable. I include time to create the object, add inline tests in the object’s comments, depending on the object create automated tests, and create the necessary migrations. Because I include all of these processes as the estimate for an object, I don’t get pushback from executives who don’t understand the value of testing, and managing database migrations. I can tell you, they love the results when I build a database with these components as part of my estimate.
Do you have ways of estimating you have found work for you? Drop a comment to add to the list. There must be many more ways, just as effective.
Cheers,
Ben