ProHoster > Blog > Administration > LINQ Query Optimization Methods in C#.NET

LINQ Query Optimization Methods in C#.NET

Introduction

In from one of the authors. Some optimization methods were considered LINQ queries.
Here we will also present other approaches to code optimization related to LINQ queries.

It is known that LINQ(Language-Integrated Query) is a simple and convenient query language for data sources.

A LINQ to SQL is a data access technology in RDBMS. It is a powerful tool for working with data, where queries are constructed through a declarative language, which are then transformed into SQL queries for the platform and sent to the database server for execution. In our case, we will understand RDBMS as MS SQL Server.

However, LINQ queries are not transformed into optimally written SQL queries, which an experienced DBA could write with all the nuances of optimization SQL queries:

  1. optimal joins (JOIN) and filtering results (WHERE)
  2. a multitude of nuances in using joins and group conditions
  3. many variations for replacing conditions IN to EXISTSand NOT IN, to EXISTS
  4. intermediate result caching through temporary tables, CTEs, and table variables
  5. using the statement (OPTION) with directives and table hints WITH (…)
  6. the use of indexed views as a means to eliminate excessive data reads during selections

The main bottlenecks in the resulting performance SQL queries during compilation LINQ queries are:

  1. consolidation of the entire data selection mechanism into a single query
  2. duplication of identical code blocks, which ultimately leads to multiple unnecessary data reads
  3. groups of composite conditions (logical 'and' and 'or') — AND and OR, combining into complex conditions, leads to the optimizer, having suitable non-clustered indexes on the required fields, ultimately still beginning to perform a scan on the clustered index (INDEX SCAN) on the groups of conditions
  4. deep nesting of subqueries makes parsing SQL statements and analyzing the query plans problematic for developers and DBA

Optimization methods

Now let's move directly to the optimization methods.

1) Additional indexing

It is best to consider filters on the primary tables of the queries, as very often the entire request is built around one or two main tables (applications-people-operations) and a standard set of conditions (IsClosed, Canceled, Enabled, Status). It is important to create appropriate indexes for the identified selections.

This solution makes sense when the selection by these fields significantly limits the resulting set of the query.

For example, we have 500,000 applications. However, there are only 2,000 active applications. Then, a well-chosen index will save us from INDEX SCAN scanning a large table and will allow us to quickly retrieve data through a non-clustered index.

The lack of indexes can also be identified through the hints in the query execution plans or by collecting statistics from system views MS SQL Server:

  1. sys.dm_db_missing_index_groups
  2. sys.dm_db_missing_index_group_stats
  3. sys.dm_db_missing_index_details

All data from the views contain information about missing indexes, except for spatial indexes.

However, indexes and caching are often methods to combat the consequences of poorly written LINQ queries and SQL queries.

As harsh business practice shows, timely implementation of business features is often important. Therefore, heavy queries are often shifted to the background with caching.

Partly, this is justified since the user does not always need the freshest data, which results in an acceptable level of user interface response.

This approach allows business queries to be resolved but ultimately reduces the operational efficiency of the information system, merely postponing problem-solving.

It is also worth remembering that in the process of searching for necessary new indexes to add, recommendations MS SQL for optimization can be incorrect under the following conditions:

  1. if indexes already exist with a similar set of fields
  2. if fields in the table cannot be indexed due to indexing constraints (this is described in more detail here).

2) Merging attributes into a new single attribute

Sometimes, some fields from one table that are used in the condition groups can be replaced by introducing one new field.

This is particularly relevant for state fields, which are usually either bit or integer types.

Example:

IsClosed = 0 AND Canceled = 0 AND Enabled = 0 is replaced with Status = 1.

Here, an integer attribute Status is entered, provided by filling in these statuses in the table. Next, the indexing of this new attribute is carried out.

This is a fundamental solution to the performance issue, as we access data without unnecessary calculations.

3) Materialization of the view

Unfortunately, in LINQ queries temporary tables, CTEs, and table variables cannot be used directly.

However, there is another optimization method for this case — indexed views.

The group of conditions (from the example above) IsClosed = 0 AND Canceled = 0 AND Enabled = 0 (or a set of other similar conditions) becomes a good option for using them in an indexed view, caching a small slice of data from a large volume.

But there are a number of limitations when materializing a view:

  1. the use of subqueries, clauses EXISTS must be replaced with the use of JOIN
  2. clauses cannot be used UNION, UNION ALL, EXCEPTION, INTERSECT
  3. table hints and clauses cannot be used OPTION
  4. there is no possibility of working with loops
  5. it is impossible to return data in one view from different tables

It is important to remember that the real benefit from using an indexed view can essentially be obtained only through its indexing.

But when calling the view, these indexes may not be used, and to explicitly use them, it is necessary to specify WITH (NOEXPAND).

Since there is no dot in LINQ queries table hints cannot be defined, so it is necessary to create another view — a 'wrapper' of the following kind:

CREATE VIEW VIEW_NAME AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) Using table functions

Often in LINQ queries large blocks of subqueries or blocks using views with a complex structure form a final query with a very complicated and non-optimal execution structure.

The main advantages of using table functions in LINQ queries:

  1. The ability, as in the case with views, to use and specify as an object, but a set of input parameters can be passed:
    FROM FUNCTION(@param1, @param2 …)
    as a result, flexible data retrieval can be achieved
  2. In the case of using a table function, there are not such strong limitations as in the case of indexed views described above:
    1. Table hints:
      via LINQ It is not possible to specify which indexes to use and determine the data isolation level when querying.
      However, these capabilities are available in the function.
      With the function, a relatively consistent execution plan can be achieved, where rules for working with indexes and data isolation levels are defined.
    2. Using the function allows for, compared to indexed views, to obtain:
      • complex data extraction logic (including the use of loops)
      • data extraction from multiple different tables
      • of the resize property. UNION and EXISTS

  3. The offer OPTION is very useful when we need to manage parallelism. OPTION(MAXDOP N), the order of the execution plan. For example:
    • it is possible to specify forced reconstruction of the execution plan. OPTION (RECOMPILE)
    • it is possible to specify the need to ensure forced use of the execution plan in the join order specified in the query. OPTION (FORCE ORDER)

    More details on OPTION is described here.

  4. Using the narrowest and most required slice of data:
    There is no need to keep large data sets in caches (as with indexed views), from which data still needs to be filtered by the parameter.
    For example, there is a table that has three fields for filtering WHERE (a, b, c) Conditionally, for all queries, there is a constant condition.

    a = 0 and b = 0 However, the query for the field.

    is more variable. c Let's say the condition

    does help us limit the required resulting set to thousands of records, but the condition by However, the query for the field narrows the selection down to hundreds of records. with Here, a table function may turn out to be a more advantageous option.

    Also, the table function is more predictable and consistent in execution time.

    Let's consider an implementation example using the Questions database.

Examples

There is a query

, joining several tables and using one view (OperativeQuestions), which checks by email the membership (through SELECT) to 'Active Requests' ([OperativeQuestions]): EXISTSQuery No. 1

Request No. 1

(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM [dbo].[Questions] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id],
[Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId],
[Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[OperativeQuestions] AS [Extent5]
WHERE (([Extent5].[Email] = @p__linq__0) OR (([Extent5].[Email] IS NULL) 
AND (@p__linq__0 IS NULL))) AND ([Extent5].[Id] = [Extent1].[Id])
));

The view has a rather complex structure: it includes joins of subqueries and uses sorting DISTINCT, which is generally a resource-intensive operation.

The selection from OperativeQuestions is about ten thousand records.

The main problem with this query is that for records from the outer query, an inner subquery on the view [OperativeQuestions] is executed, which must limit the output for [Email] = @p__linq__0 (through EXISTS) to hundreds of records.

It might seem that the subquery should calculate records for [Email] = @p__linq__0 just once, and then these couple hundred records should be joined by Id with Questions, making the query quick.

In reality, however, there occurs a sequential join of all tables: checking for matching Ids of Questions with Ids from OperativeQuestions, and filtering by Email.

Essentially, the query works with all tens of thousands of records from OperativeQuestions, while only the data related to Email is needed.

The text of the view OperativeQuestions:

Query No. 2

 
CREATE VIEW [dbo].[OperativeQuestions]
AS
SELECT DISTINCT Q.Id, USR.email AS Email
FROM            [dbo].Questions AS Q INNER JOIN
                         [dbo].ProcessUserAccesses AS BPU ON BPU.ProcessId = CQ.Process_Id 
OUTER APPLY
                     (SELECT   1 AS HasNoObjects
                      WHERE   NOT EXISTS
                                    (SELECT   1
                                     FROM     [dbo].ObjectUserAccesses AS BOU
                                     WHERE   BOU.ProcessUserAccessId = BPU.[Id] AND BOU.[To] IS NULL)
) AS BO INNER JOIN
                         [dbo].Users AS USR ON USR.Id = BPU.UserId
WHERE        CQ.[Exp] = 0 AND CQ.AnswerId IS NULL AND BPU.[To] IS NULL 
AND (BO.HasNoObjects = 1 OR
              EXISTS (SELECT   1
                           FROM   [dbo].ObjectUserAccesses AS BOU INNER JOIN
                                      [dbo].ObjectQuestions AS QBO 
                                                  ON QBO.[Object_Id] =BOU.ObjectId
                               WHERE  BOU.ProcessUserAccessId = BPU.Id 
                               AND BOU.[To] IS NULL AND QBO.Question_Id = CQ.Id));

The original mapping of the view in DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().ToView("OperativeQuestions");
    }
}

Original LINQ query

var businessObjectsData = await context
    .OperativeQuestions
    .Where(x => x.Email == Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

In this particular case, we consider solving this problem without infrastructure changes, without introducing a separate table for ready results ("Active Queries"), which would require a mechanism to populate it with data and keep it updated.

Although this is a good solution, there is another option for optimizing this task.

The main goal is to cache records for [Email] = @p__linq__0 from the OperativeQuestions view.

We are introducing the table function [dbo].[OperativeQuestionsUserMail] into the database.

By sending Email as an input parameter, we receive a value table back:

Query No. 3


CREATE FUNCTION [dbo].[OperativeQuestionsUserMail]
(
    @Email  nvarchar(4000)
)
RETURNS
@tbl TABLE
(
    [Id]           uniqueidentifier,
    [Email]      nvarchar(4000)
)
AS
BEGIN
        INSERT INTO @tbl ([Id], [Email])
        SELECT Id, @Email
        FROM [OperativeQuestions] AS [x] WHERE [x].[Email] = @Email;
    
    RETURN;
END

Here, a value table with a predefined data structure is returned.

To ensure that queries to OperativeQuestionsUserMail are optimal and have optimal query plans, a strict structure is required, not RETURNS TABLE AS RETURN…

In this case, the target Query 1 transforms into Query 4:

Query No. 4

(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM (
    SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] (@p__linq__0)
) AS [Extent0]
INNER JOIN [dbo].[Questions] AS [Extent1] ON([Extent0].Id=[Extent1].Id)
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id], [Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId], [Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]);

Mapping of the view and function in DbContext (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query().ToView("OperativeQuestions");
    }
}
 
public static class FromSqlQueries
{
    public static IQueryable GetByUserEmail(this DbQuery source, string Email)
        => source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}

Final LINQ query

var businessObjectsData = await context
    .OperativeQuestions
    .GetByUserEmail(Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

The execution time has decreased from 200-800 ms to 2-20 ms, and so on, which is dozens of times faster.

If averaged, instead of 350 ms we got 8 ms.

Some obvious advantages also include:

  1. overall reduction in read load,
  2. significantly decreased likelihood of locks
  3. reduction of average lock time to acceptable values

Output

Optimizing and fine-tuning database queries MS SQL via LINQ is a task that can be addressed.

In this work, attentiveness and consistency are very important.

At the beginning of the process:

  1. it is necessary to check the data the query works with (values, selected data types)
  2. to correctly index this data
  3. to verify the correctness of join conditions between tables

In the next iteration of optimization, the following are identified:

  1. the core of the query and the main filter of the query are determined
  2. repeating similar blocks of the query and the intersection of conditions are analyzed
  3. in SSMS or another GUI for SQL Server the query itself is optimized SQL query (allocating intermediate data storage, constructing the resulting query using this storage (there can be several))
  4. at the final stage, based on the resulting query SQL query, the structure is rebuilt of the LINQ query

As a result, the resultant LINQ query should become structurally identical to the identified optimal SQL query from point 3.

Acknowledgments

A huge thank you to my colleagues jobgemws and alex_ozr from the company Fortis for their assistance in preparing this material.

Source: habr.com

Buy reliable website hosting with DDoS protection, VPS VDS servers 🔥 Buy reliable website hosting with DDoS protection, VPS VDS servers | ProHoster