One day, you got a performance bug report. The report said that the customer got a timeout issue. Because the system database is SQL, you open SQL Profiler and monitor what happened inside the SQL server when a single button clicked.
Right in front of you opened eyes, there are more than 7.000 (yes, seven thousands) queries executed to serve a single request. The request is to search a list of records, and there are more than 2.000 records returned__seven thousands queries for two thousands records.
The system is a typical client – server application. The server side is built with WCF + NHibernate (SQL). The system has the notion of CQRS. NHibernate is used to manage domain object with mapping to the database. Therefore, the request (search records) belongs to Query side.
Root Cause
Lazy Loading! It is not hard to figure out the root cause. We use NHibernate to conduct a search query and load all the found records. Here is a simple overview of the architecture.
The problem happens at the map between domain and view.
Solution
Quite simple. Follow these steps
- Define the Model that contains enough data for the View. Note that I am talking about Model not Domain.
- Define a SQL View that collects all the data to serve the Model.
- Use NHibernate to load data from SQL View instead of Domain.
The general idea is that we should get enough data in a single or two queries.
Lessons Learned
Command and Query Separation
Even the architecture says CQRS notion, there is no real separation of Command and Query. They share the same data model (the domain). The Domain should only appear and use in Command side only. And the Query side, there should be a notion of Model. Domain holds the business logic of the system. Whereas, Model models the read data of the system.
Martin Fowler Command Query Separation.
Overused Tools (NHibernate)
NHibernate is a great tool. However, just like other tools, it has good and bad scenarios. I am sure NHibernate is not a good fit for searching data just to returning partial data for clients. In the example, NHibernate is used to search for list of records, take some of information, and transfer to a view.
However, if we decide to use NHibernate to for this job, then we should read about Select N+1 problem.
Utilize Our Tools (SQL)
We tend to depend so much on ORM framework that we forget how powerful SQL Server is. By defining a SQL view for search request, we can utilize the SQL server. Later we can ask DBA experts to improve SQL performance without redeploying the application.
Final Thoughts
Today, there are thousands of tools at our disposal. We should choose them wisely. Otherwise, there is a big payback in the near future. Every tool has its advantages and disadvantages. You do not have to know them all. However, you should consider studying them before using.
The more I code the more I love CQRS principle. At the highest, it is about Separation of Concern.
I have introduced some concepts in this post. Just in case you have not known about them yet, here are some references:
CodeProject Select N+1 Problem
