Fix database issues

Databases are one of the main sources of performance issues in web applications. As a database has to deal with huge amounts of data, even a minor algorithmic error when preparing a query can lead to serious consequences. This is especially true when using ORM systems (the examples in this chapter use Entity Framework).

DPA detects database issues related to long query execution times, large number of database connections, large number of the same database commands, and large number of records in a response. The issues are grouped under the Database tab in the Dynamic Program Analysis window.

In this chapter, you will find examples of code design that may lead to such issues and tips on how you can fix it.

DB command time

If a command execution time exceeds the specified threshold, DPA marks the code that runs the command with a DB Command Time issue. It is tough to single out any one reason for long execution time of a particular command. Such issues could be related to a complicated resulting query, to network connection problems, and so on. It's quite possible that a long command time is not an issue at all – it's just the way the application works, and nothing can be done to fix the situation.

The default threshold is 500 ms.

How to fix

As there could be numerous reasons behind the issue, the only advice that could be given is to:

Find the problematic code and open the corresponding issue in the Dynamic Program Analysis window.
In the issue details, check the SQL query.
If it's still not clear what makes the problem, try running the query directly on the database server and rule out one by one all possible causes, e.g., communication issues, limited cache size, non-indexed columns, table locks, deadlocks, and other.

DB connections

If a number of simultaneously opened connections to a database exceeds a threshold, DPA marks the code that opens a connection with a DB Connections issue. For example, during the execution, a function opens and closes 100 connections, then 200 connections, then 150 connections. Supposing the threshold is set to 50 connections, the resulting issue value will be '200 connections'.

The default threshold is 10 connections.

Below you will find some of the possible reasons for the connection leaking.

Connection leaks in 'try' blocks

Consider the following code:

                private static void TryFinallyLeak()
                {
                    var connection = new SqlConnection("Server=DBSRV;Database=SampleDB");

                    try
                    {
                        connection.Open();
                        var command = new SqlCommand("select count (*) from Blogs", connection);
                        command.ExecuteScalar(); // in case of exception here, connection won't be closed
                        connection.Close();
                    }
                    catch(Exception e)
                    {
                        // handle exception
                    }
                }
            

Though we close the connection after running the command, if the command throws an exception, the connection will remain open. What is worse, we will handle the exception without even knowing about an open connection.

How to fix

You must close the connection in any case using the finally block:

                    private static void TryFinallyLeak()
                    {
                        var connection = new SqlConnection("Server=DBSRV;Database=SampleDB");

                        try
                        {
                            connection.Open();
                            var command = new SqlCommand("select count (*) from Blogs", connection);
                            command.ExecuteScalar();
                        }
                        catch(Exception e)
                        {
                            // handle exception
                        }
                        finally
                        {
                            connection.Close(); // connection is closed in any case
                        }
                    }
                

Connection leaks via SQLDataReader

When using SQLDataReader for reading stream of rows from a database, make sure you're using the correct CommandBehavior. Consider an example:

                private static void ReaderLeak()
                {
                    var reader = GetReader();
                    while (reader.Read())
                        ;
                    reader.Close(); // closing the reader doesn't close the connection
                }

                private static SqlDataReader GetReader()
                {
                    var connection = new SqlConnection("Server=DBSRV;Database=SampleDB");
                    connection.Open();
                    var command = new SqlCommand("select * from Blogs", connection);
                    // the created reader doesn't close the connection as
                    // it doesn't use CommandBehavior.CloseConnection
                    return command.ExecuteReader();
                }
            

An SqlDataReader instance doesn't close the connection after executing a command. If the reader is created many times, this spawns a corresponding number of open connections.

How to fix

Make sure you use an instance of SqlDataReader that closes the connection via CommandBehavior.CloseConnection. The problem here is that an SqlDataReader with the default or another behavior may be required by some other code. In this case, you should refactor the code so that it creates instances of SqlDataReader with behaviors for all required use cases.

                    private static void ReaderLeak()
                    {
                        var reader = GetReader();
                        while (reader.Read())
                            ;
                        reader.Close();
                    }

                    private static SqlDataReader GetReader()
                    {
                        var connection = new SqlConnection("Server=DBSRV;Database=SampleDB");
                        connection.Open();
                        var command = new SqlCommand("select * from Blogs", connection);
                        // add behavior that closes connection
                        return command.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                

DB commands

If a number of the command executions exceeds a threshold, DPA marks the code that runs the same command multiple times with the DB Commands issue. The default threshold is 50 commands.

The main reason why this check exists is to prevent the well-known N+1 problem. For example, you have a table of blogs and each Blog has a number of posts inside. Thus, Blog to Post is the one-to-many relationship. Suppose you want to get the list of all posts in all blogs. The straightforward way to do this with Entity Framework would be:

            private void nPlus1(int count)
            {
                using var dbContext = new BlogContext();
                var blogs = dbContext.Blogs.ToList(); // get list of blogs (1 query)

                // for each blog get all posts (N queries)
                foreach (var blog in blogs)
                {
                    Console.WriteLine($"Posts in {blog}:");

                    foreach (var post in blog.Posts)
                        Console.WriteLine($"{post}");
                }
            }

            public class BlogContext: DbContext
            {
                public DbSet<Blog> Blogs { get; set; }
                public DbSet<Post> Posts { get; set; }
                // ...
            }
            
        

The code above will result in N+1 queries where N is the total number of posts (select all blogs + select posts from each blog).

How to fix

You should try getting all the required data in a single request to the database. For example:

                private void nPlus1(int count)
                {
                    using var dbContext = new BlogContext();
                    var blogs = dbContext.Blogs
                        .Include(b => b.Posts) // get all posts to memory (1 query)
                        .ToList();

                    // the code below works locally (0 queries)
                    foreach (var blog in blogs)
                    {
                        Console.WriteLine($"Posts in {blog}:");

                        foreach (var post in blog.Posts)
                            Console.WriteLine($"{post}");
                    }
                }
            

DB records

If a database command returns a number of records that exceeds a threshold, DPA marks the code that runs the command with the DB Connections issue. In some cases, getting many records is implied by design. But sometimes, this may happen accidentally due to suboptimal code patterns.

The default threshold is 100 records.

Casting IQueryable to IEnumerable

IQueryable implies querying an external data source while IEnumerable queries only in-memory data. So, if you query an IEnumerable collection, the application will first get all related data from the database and then apply the query to the in-memory data. With IQueryable, the application will send the query to the external database directly. Consider the following example.

                // some custom filter that takes IEnumerable as input
                private IEnumerable<Post> CustomFilter(IEnumerable<Post> posts) =>
                    posts.Where(_ => _.PostId % 2 == 0);


                private void FilterFail()
                {
                    using var dbContext = new BlogContext();
                    // get count of posts matching the custom filter
                    var postCount = CustomFilter(dbContext.Posts.Where(_ => _.PostId > 0)).Count();
                    Console.WriteLine(postCount);
                }

                public class BlogContext: DbContext
                {
                    public DbSet<Blog> Blogs { get; set; }
                    public DbSet<Post> Posts { get; set; }
                    // ...
                }
            

All we want to get in the example above is a number of posts matching some condition. In theory, this could be done with a SELECT COUNT database query. In practice, as CustomFilter accepts only IEnumerable collections, our query is casted from IQueryable to IEnumerable. As a result the line CustomFilter(dbContext.Posts.Where(_ => _.PostId > 0)).Count() loads all posts to memory with:

                SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
                FROM [Posts] AS [p]
                WHERE [p].[PostId] > 0
            

Next, the filter is applied to the in-memory collection of posts. A DPA issue shows how many records we receive due to this query:

The shown example is very obvious. In a real application such casting may be hidden inside numerous calls (e.g., inside some query filter chains).

How to fix

The filter function must explicitly use IQueryable collections. For example:

                    private IQueryable<Post> CustomFilter(IQueryable<Post> posts) =>
                        posts.Where(_ => _.PostId % 2 == 0);

                    private void FilterFail()
                    {
                        using var dbContext = new BlogContext();
                        // get count of posts matching the custom filter
                        var postCount = CustomFilter(dbContext.Posts.Where(_ => _.PostId > 0)).Count();
                        Console.WriteLine(postCount);
                    }
            

As now CustomFilter works with IQueryable, the CustomFilter(dbContext.Posts.Where(_ => _.PostId > 0)).Count() query is translated to:

                    SELECT COUNT(*)
                    FROM [Posts] AS [p]
                    WHERE ([p].[PostId] > 0) AND (([p].[PostId] % 2) = 0)
                

So all the filtering happens on the server side, and the application receives only one record with the count result.

Last modified: 21 March 2024