Most of the application we are working on are built around the data. Since the information is the core value of the application we store it in the persistent and reliable storage - database. Applications evolve in time so does the way we store the data. In this post, I’m going to present my experiences and thoughts on evolving database schema.
A few days ago I’ve stumbled upon SQL query performance issue. Git claims that I was the author so maybe that’s the reason I remember this feature. There was like 3 classes, everything was super easy and super fast all I needed to do was to let Hibernate do its thing. Then time passed, new features were requested, the model becomes more complex, the number of rows increased to ~4 million and original query became too slow.
When working on query optimization I was really happy to find that detailed tests are in place. With proper test setup I was able to test my new query to make sure all requirements are met and then quickly copy paste query to sqldeveloper run it on a test environment and verify if performance is acceptable. It wasn’t simple query and it took me some time to figure out how to make it quick and work exactly as old one. That was the time I was really glad that we invested in detailed unit tests in the beginning.