My company bought a new company in the last year and we have created a new web application to provide some functionality based on an existing system but created from scratch so we could use as many best-pratices as we could from the start.
Currently the new company only uses a small amount of the existing functionality but plans are afoot to increase this. One of the things I have been working on is in the quotation part of the system where the business selects the equipment for a quotation, adds certain services and then produces tender documentation. Our current system has around 22,000 quotes (some of which are test quotes) and one of the luxuries this provides is a set of genuine test data to try out any designs we create for the new company to see whether it scales well. Let's be honest, how many times have we implemented something by putting in a few rows of test data and then deployed it into a system that might end up with 10s or 100s of thousands of rows?
Anyway, there have been two things that have been obvious with our major re-design of the table layout and which have produced an increase in performance of around 5 times for the same data set running on a virtual test server with much less memory and CPU than the live server - pretty impressive. The first thing is that you need knowledge to write good software! This might seem obvious but someone might understand databases at a basic level but without understanding things like indexes, foreign keys and referential integrity, at best you will write something average, at worst it will simply not perform or scale and you won't understand why. Foreign and primary keys are not just for integrity but allow certain queries to perform much faster because of assumptions the system can make about the number of rows that will join in a query. The second thing is that we spent time in design. To be honest it was a Visio diagram that was sent round and which we all looked at. We considered the various objects in the current system and how they would work in the new system, which led to additional columns and the like but a large reduction in the number of tables required and therefore a reduction in joins and a performance increase in queries. Why? Well although you can partially think and design while you implement, in reality, you will never make large changes and see the big picture while adding tables into a database. If we had dived straight in, we would probably have something much closer to the old system whereas the new design is radically different and more efficient in the process. The few days of time we spent checking out the design has saved some time in implementation, a lot of time in potential rework and loads of performance. It seems counter-intuitive to be designing things and not just getting on and doing it but to be honest nowadays, implementation only needs to take a few days once the design has been completed.
Knowledge and Design - don't take shortcuts.