How to speed up slow filtering page in MS SQL and ASP.NET MVC?-CodePudding

The

Most filtering combination work fine, but some of them are known to be extremely slow (over 30 seconds when others take less than a second). One of the reasons for this behavior is that for some combinations of filters MS SQL is choosing a bad execution plan with not appropriate indexes (instead of choosing index on WHERE clause it takes index for ORDER BY clause and performs index scan with key lookups). For example, instead of using ManagerId index and performing an index seek this auto-generated query uses Date index and performs an index scan.

SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Clients] AS [Extent1] WHERE ([Extent1].[Paid] <> 1) AND ((([Extent1].[ManagerId] IN (N'd3cbce41-1db3-4b6d-8a14-1d5704090b3d')) AND ([Extent1].[ManagerId] IS NOT NULL)) OR ( EXISTS (SELECT 1 AS [C1] FROM [dbo].[Partners] AS [Extent2] WHERE ([Extent1].[Id] = [Extent2].[ClientId]) AND ([Extent2].[PartnerId] IN (N'd3cbce41-1db3-4b6d-8a14-1d5704090b3d')) ))) ORDER BY row_number() OVER (ORDER BY [Extent1].[Date] ASC) OFFSET 600 ROWS FETCH NEXT 100 ROWS ONLY

For another example, query with 6 different filtering options selected might look like this:

exec sp_executesql N'SELECT [Project11].[Id] AS [Id] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Date1] AS [Date1] FROM [dbo].[Clients] AS [Extent1] WHERE ([Extent1].[Paid] <> 1) AND ((([Extent1].[ManagerId] IN (N''73cdda41-0086-4104-a4c2-4dd59c306s14'', N''d9dfb477-6f56-47de-b73e-0a419f575a00'')) AND ([Extent1].[ManagerId] IS NOT NULL)) OR (([Extent1].[SecondManagerId] IN (N''73cdda41-0086-4104-a4c2-4dd59c306s14'', N''d9dfb477-6f56-47de-b73e-0a419f575a00'')) AND ([Extent1].[SecondManagerId] IS NOT NULL))) AND ( EXISTS (SELECT 1 AS [C1] FROM (SELECT [Extent2].[Code] AS [Code] FROM [dbo].[Codes] AS [Extent2] WHERE [Extent1].[Id] = [Extent2].[ClientId] INTERSECT SELECT [UnionAll2].[C1] AS [C1] FROM (SELECT N''9.7'' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1] UNION ALL SELECT N''9.8'' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable2] UNION ALL SELECT N''9.9'' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]) AS [Intersect1] )) AND ( EXISTS (SELECT 1 AS [C1] FROM (SELECT 1 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable4] UNION ALL SELECT 3 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable5]) AS [UnionAll3] WHERE [Extent1].[Stage] = [UnionAll3].[C1] )) AND ((convert (datetime2, convert(varchar(255), [Extent1].[Date1], 102) , 102)) >= (convert (datetime2, convert(varchar(255), @p__linq__0, 102) , 102))) AND ((convert (datetime2, convert(varchar(255), [Extent1].[Date1], 102) , 102)) <= (convert (datetime2, convert(varchar(255), @p__linq__1, 102) , 102))) AND ( EXISTS (SELECT 1 AS [C1] FROM [dbo].[Table2] AS [Extent3] WHERE ([Extent1].[Id] = [Extent3].[ClientId]) AND (0 = [Extent3].[Status]) AND ((convert (datetime2, convert(varchar(255), [Extent3].[Date2], 102) , 102)) >= (convert (datetime2, convert(varchar(255), @p__linq__2, 102) , 102))) AND ((convert (datetime2, convert(varchar(255), [Extent3].[Date2], 102) , 102)) <= (convert (datetime2, convert(varchar(255), @p__linq__3, 102) , 102))) )) ) AS [Project11] ORDER BY row_number() OVER (ORDER BY [Project11].[Date1] ASC) OFFSET 1000 ROWS FETCH NEXT 100 ROWS ONLY ',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7)',@p__linq__0='2022-12-16 00:00:00',@p__linq__1='2022-12-18 00:00:00',@p__linq__2='2022-08-01 00:00:00',@p__linq__3='2022-12-15 00:00:00'

For some filtering options query has to make subqueries for other tables due to the many to many relationships between them.

We already have a separate index for almost every attribute (except navchar(max) ones) that being part of this filtering query but it’s not enough. Obviously, we cannot make a covering index for every filtering/ordering combination so we are currently considering making nonclustered columnstore index (NCCI) that would cover all this attributes combination. But one thing that worries us about this idea is that the table is being updated quite frequently (about 8000 – 15000 rows per day).

Are there any others approaches that we should look at to make this page fast for every filter combination? Like some external tools, libraries or methods. Or would the idea of using NCCI for our table work just fine?

CodePudding user response：

Intro

To answer your question the db should be analyzed, below I will give you some of my considerations.

First of all your sentences:

First

MS SQL is choosing a bad execution plan with not appropriate indexes (instead of choosing index on WHERE clause it takes index for ORDER BY clause and performs index scan with key lookups).

MS SQL chooses the best plan with the info it has. If you have bad index statistics, bad index fragmentation, etc, MSSQL can't do much.

Second

we are currently considering making nonclustered columnstore index (NCCI)

From Microsoft docs:

Rowstore indexes perform best on queries that seek into the data, when searching for a particular value, or for queries on a small range of values. Use rowstore indexes with transactional workloads because they tend to require mostly table seeks instead of table scans.

Columnstore indexes give high performance gains for analytic queries that scan large amounts of data, especially on large tables. Use columnstore indexes on data warehousing and analytics workloads, especially on fact tables, because they tend to require full table scans rather than table seeks.

From your question, you have row-level filtering, not aggregate (where columnstore are the best), from my point of view creating a columnstore is pointless.

Considerations

From your question:

But one thing that worries us about this idea is that the table is being >updated quite frequently (about 8000 – 15000 rows per day).

Based on this sentence some solutions below may not fit.

EntityFramework

Do you know how to write SQL queries? If the answer is yes, then use it, replace EF with Dapper (maybe with SqlKata or a similar fw) and write your own query.

EF does its best to write queries but it will never be like you who know the DB.

Index

Always check your query plans.

Do not add many indexes can be counterproductive, for every row added to the table the index must be updated.

If you have a column by which you can "partition" the table you can consider table partitioning

Double-check your statistics. if the query has a good index and bad plan check statistics.

Read-only database

Read-only databases are very fast for queries operations (they don't have to check for locking).

Consider Database snapshot.

Consider a read-only Replica

Caching

You can use the .NET MVC caching

You can use Redis or a similar software for caching.

CodePudding user response：

You have number of SubQueries in your example and SubQueries will definitely slow down the process. Try using joins instead, and that will considerably reduce the filtration time.

CodePudding user response：

The best approach IMHO is to use a Stored Procedure (SP) with parameters for your query fields. These parameters can also be used to:

Control the output, for example sort-order, top, etc.

Submit multiple values by the use of used-defined data types

The main advantages are:

You, or your db-dev / dba, have full control of the query that is made

Freedom of using sub queries in the SP

Maintenance and optimization can be done without updating the ASP.NET MVC web app

In code I've come across I saw stuff like:

CREATE PROCEDURE [yourSchema].[GetYourData] @DateTimeFrom DATETIME = NULL, @DateTimeTill DATETIME = NULL, @SortAscending AS BIT = 0, -- control the output @ApplicationName VARCHAR(64) = NULL, @MachineName VARCHAR(256) = NULL, @UserName VARCHAR(256) = NULL, @MaxRowCount INT = 1000, -- control the output @KeyValueTable AS CustomTableType.KeyValueTable READONLY, -- submit a table to filter @SeverityTable AS CustomTableType.SeverityTable READONLY -- submit a table to filter AS /* etc... */

You can call this SP from EF, but you also could use Dapper as suggested earlier by @Max.