When working with an ORM, it is essential that you inspect your generated queries. Often when doing so you will find they are not entirely what you expected. The CriteriaBuilder API can be very useful for building dynamic queries, but you must take great care – especially if you let your UI generate many _wonderful_ queries to fulfil all of its filtering/sorting/etc.
Often, you’re may find yourself wondering why application performance has degraded – and almost always that issue originates from your persistence layer. You can interrogate your query statistics in SQL Server using some of the built in (Transact SQL) views – which have been invaluable before to me investigating issues in customer environments.
In my experience, the number one cause of application performance problems is not your application code – it’s your persistence layer. Problems in this area can be caused by many different ‘sins’; Improper entity relationships (think LAZY vs EAGER fetching), Inefficient queries or indeed the cardinal sin – N+1 queries! A lot of the time the application can end up in this way as a result of a lack of awareness of what you are asking the persistence layer to do. Often, you’ll find that simply enabling logging of sql statements will open your eyes to the problem. In fact, when using a data access framework that generates statements on your behalf – it should be mandatory that you inspect the generated statements to ensure both their effectiveness, and their performance.
As a follow on from my last post about running sql server in docker, I thought i’d write about something that I have just introduced for the first time into one of my projects – in the hope that some of you feel as passionate as I do about testing your production code (and not just the equivalent in H2-specific syntax 😱). I hope that through this post I can show you how easy it is to include this dependency into your project, and be on your way to having your integration tests running against your production database!
A few years ago I switched my main work machine from Windows to Mac – despite the reliance I have on SQL Server to run our application (Well, there’s also the dev-only H2 database, but even then I knew that wouldn’t fly due to some of the ‘subtle’ differences).