When working with databases you should be very careful to avoid anti-patterns. Anti-patterns could lead to performance pitfalls. In the following blog post you are going to get familiar with the common and most popular anti-patterns in relational databases and how to avoid them.
N + 1 problem
This is a data access anti-pattern where the database is accessed in a sub-optimal way. Detecting Select N+1 problem usually means that the data fetch strategy of the application can be optimized. By default traversing a collection of related objects would lead to the execution of an additional query for each object in the collection. While this would still work, it is highly inefficient.
In Sitefinity there's a special Diagnostics module that you could use to detect N+1 problems.
Once you detect an N+1 problem, you could use the FetchPlans API to force an eager load of the query collection. For more information, see Fetch plans and strategies.
Cartesian product and Long Running Queries
When you perform a multiple-table query, you should be careful to avoid Cartesian products. Also referred as cross-join, the Cartesian product returns all the rows in all the tables listed in the query. Each row in the first table is paired with all the rows in the second table. This happens when there is no relationship defined between the two tables. Such query could be very time-consuming especially when you are not interested in the whole result.
Here's an example of an implicit cross-join, from the Microsoft Northwind sample database:
SELECT * FROM employee, department;
In order to detect a long-running query or a Cartesian product, you can again use the Sitefinity's Diagnostics module with the RunLongQueryAnalysis report. See Diagnostics module reports for more information.
Too Many Records Fetched problem
Too Many Records Fetched problem indicates that a query fetched more records than usual. Such queries might take long time to execute, decreasing the performance of your application.
In Sitefinity, you could detect this problem by using the RunBigResultSetAnalysis report of the Diagnostics module. For more information, see Diagnostics module reports.
You can easily solve such problem by adding Skip and Take LINQ operators to retrieve the needed information only.
Avoid LIKE operator in SQL Queries
The LIKE operator is scanning the full table in the query which might decrease the performance of your application. As a solution, you could use Full-Text Search Queries. For more information, see Full-Text Search (SQL Server).
Avoid aggregation queries
Similar to the queries with LIKE operator, all aggregation functions like SUM, AVG, MIN, MAX over many rows might result to a long-running query. Try to either minimize the amount of rows to handle or pre-calculate these values.
Do not use Update and Delete queries over the whole tables
Deleting and updating large amounts of data from huge tables can be a nightmare. The problem is that both of these statements run as a single transaction, and if you need to kill them or if something happens to the system while they're working, the system has to roll back the entire transaction. This can take a very long time. These operations can also block other transactions for their duration, essentially bottlenecking the system.
The solution is to do deletes or updates in smaller batches.
You can also speed up the Update queries using CASE statement. For more information, see The power of SQL CASE Statements from 4guysfromrolla.
Use LINQ deffered execution
When working with the Sitefinity API, developers usually make requests that eventually go to the database and fetch some data to display or work with. The Sitefinity API hides to complexity of all this work and makes things seem easy. There’s a lot going on behind the scenes, and you should understand the implications of calling the Sitefinity API when you work with it, in order to avoid performance problems. For more information, see Use LINQ deffered execution.