SQL Server Blog

My Blog

Monday, September 16 2013

Keywords: Agile, SCRUM, Database Development, SQL Server, Oracle, TFS, Process

Agile Database Development, Part 3 of 5

This blog is part of a series on Agile Database Development.
Find Part 2 of series here.


*) Lesson 10: Believe in automated tests, all the way
You should put a lot of emphasis on automated tests. The more thorough and complete your automated tests are, the more robust your application will be, the less painful the refactoring will be, the less detailed your comments and documentation need to be, the more maintainable your databases will be.


Agile, SCRUM, Database Development, SQL Server, Oracle, TFS, Process

All of them would require an environment to be set up. Do not mock the database, use a RDBMS. The first two types are to be run continuously throughout the sprint and ideally every night. Stress test could be run much less often.


*) Lesson 11: Unified development standards
Call me old school, but I believe in standards. I know that some younger Agile followers might disagree with me and that is fine. I believe that in the long term standards will save the company lots of time, money and pain. Folks change teams and positions, and new folks get hired continuously…global standards help these folks in two ways: firstly by helping them understand existing code…and secondly by helping them get settled and start coding faster.


*) Lesson 12: Document but don’t spend amazing amount of resources on it neither
High-level documentation with flows and diagrams is an obvious choice, but not the same could be said about detailed explanations and detailed comments, in the context of Agile development. First of all business rules change…clients’ requirements change…database schemas change…and very soon it becomes apparent that maintaining detailed documentation and comments is a painful time consuming and error prone procedure…as they get out of sync /data often.
Another problem with detailed comments and documentations is that they might get written by folks without writing skills, they could get misinterpreted in different ways…and they could even contain errors. Automated tests on the other hand won’t have spelling errors…they are written in C# and T-SQL and could easily be understood by anyone…even if they don’t speak the language.
So do comment and write documentations, but do not overdo it neither…put more time and emphasis on automated tests.


*) Lesson 13: Do optimize your queries, but do not go wild with that neither
I’m giving this advice in the context of Agile development only, as otherwise I am a perfectionist myself when it comes to database performance. If your long complex search query takes 0.9 seconds to run, and no one complains and your PM is more than happy, then do not be a perfectionist and try to bring it down to 0.09 seconds. You’re better off giving your time to something more useful.
Also maybe your client has a better server than the weak VM on which you are testing. Chances are your client’s hardware is either better than your testing environment, or they can upgrade their hardware to make it even better.
What you should do instead, in fact each team should at least 4 weeks of performance tuning on every enterprise caliber application. During this period of time you might find some big bottlenecks and very slow queries, and you could then prioritize the list of slow procedures and get the most urgent ones done as part of regular sprints and with actual user stories (aka PBIs). What you don't want to happen is ignoring/bypassing this performance tuning phase and then having to fix them upon pressure based on customers' feedbacks...then everyone around you will look at them as 'bugs'.


*) Lesson 14: Simplicity is the key
Simple queries...simple stored procedures and triggers...simple tables….simple simple everywhere…think simple.
Complex queries have often sub-optimal performance. They are hard to maintain and modify, and require lots of detailed comments and documentation.
Complex queries’ performance is not always reliable due to choice of execution plan by the optimizer engine at run time.
Break down your complex queries to a group of simple queries. systems you build must be able to adapt changes...they need to be easily modifiable…and simple queries allow less-hard refactoring.
Don't over complicate the features with a plethora of bells and whistles; go with the simplest solution that will respond to your client's or business's needs. Do not take 3 sprints to create one feature!


*) Lesson 15: Code/Peer Review
Spend 15-60 minutes to code review each user story. Make it more than a habit if you want by requiring a code review task in each user story / PBI in TFS…I don’t recommend implementing a mandatory code review rule in TFS unless the team really abuses it. Have code review tasks’ title have a consistent naming convention, something like ‘Code Review – xyz’ or ‘xyz – peer review’ etc, this way you could also report on user story’s that do not have a code review, at the end of the year.
If possible, if you have the luxury of, have a DBA code review all peers' database development work.


*) Lesson 16: Clarify requirements
Focus on delivering quality applications that meet the exact business needs of clients…for today and for tomorrow. You’ll achieve this in two ways:
By listening to clients and receiving feedback from them.
By having in-house brain storming sessions...grooming sessions... compound to clients ideas…if this were year 1999 and you had asked your clients requirements of a better phone, they wouldn’t be recommending a smart phone…it’s you and your team who would need to come up with those innovating ideas!



Written by Ramin Haghighat