Debugging slow LINQ queries

Language Integrated Query (LINQ) is a powerful .NET programming feature. Through a unified syntax, LINQ allows developers to easily query and manipulate data from various sources, such as databases, data collections, and XML documents. LINQ queries have three components—a data source, query creation, and query execution.

Unfortunately, slow-running LINQ queries can create performance bottlenecks for applications by increasing execution time and slowing performance, especially in high-volume scenarios. The compounded wait time for a database server to reply to requests lowers the application's response time and degrades the user experience. Furthermore, slow LINQ queries can cause applications unresponsiveness, especially if the queries block other processes. Therefore, optimizing LINQ queries to minimize the impact on execution time and application performance is essential.

This tutorial will review how to identify and troubleshoot slow-running LINQ queries. You can download the code to follow along.

Prerequisites

To follow along, ensure you have these prerequisites:

• Microsoft Visual Studio installed
• DotTrace installed
• Visual Studio extension for dotTrace called ReSharper

Identifying and fixing slow LINQ queries

Application performance monitoring is essential for them to operate smoothly and efficiently. An important element includes identifying slow database requests, which can significantly affect application performance.

You can monitor application performance using profiling and logging tools. These tools can provide insight into how long different application components take to run, thereby identifying performance bottlenecks.

Logging tools are a great way to store, analyze, and visualize logs, so you can take the necessary steps to improve performance. These tools include:

• dotTrace
• ANTS Performance Profiler
• Visual Studio Performance Profiler
• PerfView
• SciTech Software’s .NET Memory Profiler

In this tutorial, you’ll test app performance using dotTrace, a performance profiling tool from JetBrains that allows you to measure and analyze the performance of .NET applications. It supports various profiling features such as call tree profiling, memory profiling, and CPU profiling.

Begin by creating the following example.

Create a project in Visual Studio

Open Visual Studio and create a new project.

On the next screen, select Console App and click Next.

In the subsequent screen, give your app a relevant project name and click Next.

In this project, you’ll query an extensive set of JSON data.

Download the provided JSON file and move it to the project's directory.

Then, inside the Program.cs file, add the following unoptimized code. The code fetches a list of women aged over or equal to 25. Before you run the application, replace the JSON_PATH variable in the Program.cs, file with the actual path of the JSON file.

Now, create a list of those results containing names, email addresses, and company names.

using System.IO; 
using Newtonsoft.Json; 
using Newtonsoft.Json.Linq; 

namespace LinqQueryDemo 
{ 
class Program 
{ 
static void Main(string[] args) 
{ 
var jsonString = File.ReadAllText("JSON_PATH"); 
var jsonArray = JArray.Parse(jsonString); 

var results = jsonArray 
.Where(j => j["Age"].ToObject() >= 25 && j["Gender"].ToString() == "female") 
.Select(j => new 
{ 
Name = j["Name"].ToString(), 
Company = j["Company"].ToString(), 
Email = j["Email"].ToString() 
}) 
.ToList(); 
Console.WriteLine(results.Count); //32362 
} 
} 

public class Person 
{ 
    public string Id { get; set; } 
    public int Index { get; set; } 
    public string Guid { get; set; } 
    public bool IsActive { get; set; } 
    public string Balance { get; set; } 
    public string Picture { get; set; } 
    public int Age { get; set; } 
    public string EyeColor { get; set; } 
    public string Name { get; set; } 
    public string Gender { get; set; } 
    public string Company { get; set; } 
    public string Email { get; set; } 
    public string Phone { get; set; } 
    public string Address { get; set; } 
    public string About { get; set; } 
    public string Registered { get; set; } 
    public double Latitude { get; set; } 
    public double Longitude { get; set; } 
    public List Tags { get; set; } 
    public List Friends { get; set; } 
    public string Greeting { get; set; } 
    public string FavoriteFruit { get; set; } 
} 

public class Friend 
{ 
public int Id { get; set; } 
public string Name { get; set; } 
} 
} 

In the code above, you have defined three classes: Person, Friend, and Program. Inside the Program class, you’ll now read a JSON file using the File.ReadAllText method. Then, parse the JSON string to create an array where you perform operations to get the desired result.

Profile the app with dotTrace

To profile this application using dotTrace, click Extensions and then click Resharper (the dotTrace extension for Visual Studio). On the left side of the screen, select Profile and click Run Startup Configuration Performance Profiling.

A new window opens, asking you to define three dotTrace options.

In the second option, navigate to the Profiling Type section. Select Timeline and click Start.

After profiling, the Performance Profiler tab opens on the left side of Visual Studio. It should look like this:

Now, click Snapshot on the top left. Open the report in the standalone version of dotTrace to analyze it.

In dotTrace, the following view opens and displays various options, such as Events, Timeline, and Hotspots.

The Hotspot area indicates that the array’s parse method takes a significant amount of time.

Try to optimize the query statements as the total execution time for all calls is high at 18,226 milliseconds (ms).

Optimize the code

Now, you’ll optimize the Where statements in the query and profile the result.

Modify the query inside the Main function by replacing the previously defined results variable with the following code:

var results = jsonArray 
.Where(j => j["Age"].ToObject() >= 25) 
.Where(j => j["Gender"].ToString() == "female") 
.Select(j => new 
{ 
Name = j["Name"].ToString(), 
Company = j["Company"].ToString(), 
Email = j["Email"].ToString() 
}) 
.ToList(); 

This modification significantly reduces the total execution time to 12,892 ms.

Now, optimize the array parsing operation. An optimized version of this query should first deserialize the JSON data into a list of strongly-typed objects. Then, it should perform filtering and projection operations to avoid the overhead of parsing and querying the JSON objects for each item.

Keep the variable jsonString and the console statement in the same Main function. Replace the remaining code with the code below:

var objects = JsonConvert.DeserializeObject(jsonString); 
var results = objects 
.Where(o => o.Age >= 25) 
.Where(o => o.Gender == "female") 
.Select(o => new { 
o.Name, 
o.Company, 
o.Email 
}) 
.ToList(); 
If you create a profile again after these changes, the total execution time plummets to 7,283 ms from 18,226 ms. 
 

Best practices for using LINQ queries

As demonstrated, profiling optimizes the performance of various .NET applications. Here are several best practices for working with LINQ queries:

• Use lazy loading — LINQ queries execute when the results are enumerated. So, if you don’t need to list the entire result set, use lazy loading to avoid running unnecessary code.
• Avoid unnecessary queries — If you’re using LINQ to query a database, minimizing the number of queries is essential. Use eager loading to load related data in a single query or caching to store frequently accessed data.
• Use IQueryable instead of IEnumerable — When querying a database, use IQueryable instead of IEnumerable to take advantage of the database’s ability to optimize queries. IQueryable translates the query into SQL so that it’s executed on the database server.
• Use correct data structures — When working with large data sets, use appropriate data structures such as the HashSet or Dictionary classes to improve performance.
• Minimize data returned — When querying a large data set, only return the required data. Use the Select method to specify the properties you want to retrieve.
• Use lambda expressions — Use lambda expressions instead of anonymous methods to improve readability and reduce code complexity.
• Use caching — If you’re querying data that doesn’t change frequently, use caching to store the results and avoid unnecessary queries.
• Optimize LINQ queries — Tools such as LINQPad help analyze the performance of LINQ queries and identify areas for optimization.
• Profile your queries — Profiling tools identify and optimize performance bottlenecks in your LINQ queries.

Conclusion

Slow-running LINQ queries can significantly degrade the performance of your .NET applications—it’s imperative to identify and improve their performance. This tutorial explained how you can enhance LINQ queries using the dotTrace profiling tool with a hypothetical application. You also learned about dotTrace and how to use it to profile a .NET application.

You can improve application performance considerably using best practices and the correct profiling tools—especially lazy loading, using appropriate LINQ operators, and using the proper data structure.