Database indexing is a development task. The most important information for indexing is not the storage system, or the configuration of the server - but instead how the application queries the data. This knowledge is not easily obtained from DBAs or external consultants - so it's on us, the developers.
In an earlier blog post, I touched upon the cardinal sin of performance issues - N+1 queries. In this blog post I am going to explain through a few examples what the N+1 query problem is, and also suggest how to fix each in your application code.
Honestly - the main reason I chose to move away from Wordpress and to GatsbyJS wasn't to upskill in the No. 2 Web Framework from 2020 (according to stackoverflow - but just because.. I liked it better.
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.