Speed up slow filtering page in SQL Server 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 nvarchar(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：

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.

CodePudding user response：

3 mil rows shouldn't be a huge problem so something's definitely wrong. I'd concentrate on a couple of things:

Don't use auto-generated queries

Remove casts - those are unindexable

ORDER BY row_number() OVER (ORDER BY [Project11].[Date1] ASC) -- why not just order by date1? Row_number often needs to be completely materialized which is a bit unnecessary

Check your indexes, perhaps some fields can be added as INCLUDE, especially if you always search on X, but often also search on Y.

Try OPTION (RECOMPILE) to force plan regeneration

Run your queries with SET STATISTICS IO ON. Is there a lot of "action" on unexpected tables, maybe something is wrong there

See if you can create a indexable view with some of those joins pre-made which might simplify your WHERE conditions

Analyze the combinations that aren't using indexes. Test-run these with actual values and not dynamic parametrized SQL, are the results "better"? This could lead to #9.

Remove parametrized query and just input the values "raw". I know it's often suggested that parametrized queries are better, but since you have such varied queries, the selectivity might be mightly different, so the parameterizing can lead to bad query plans. Of course you gotta sanitize the data since it's coming from the web. Note, don't try this unless you absolutely cannot get it to work fast otherwise and actually have proper ability to sanitize the stuff, or you might leak data / end up on dailywtf

CodePudding user response：

Your can replace all that stuff by a series of light queries combined by EXCEPT, INTERSECT or UNION.

Those light queries will have a structure like:

SELECT Id FROM dbo.clients WHERE ... --> only one member predicate always "sargable" (avoid NOT, <> and OR...)

As an example for Query 1:

( SELECT Id FROM dbo.Clients WHERE ManagerId IN (N'd3cbce41-1db3-4b6d-8a14-1d5704090b3d') INTERSECT SELECT Id FROM dbo.Clients WHERE ManagerId IS NOT NULL UNION ALL SELECT Id FROM dbo.Clients AS C WHERE EXISTS (SELECT 1 FROM dbo.Partners AS P WHERE C.Id = P.ClientId AND P.PartnerId IN (N'd3cbce41-1db3-4b6d-8a14-1d5704090b3d')) ) EXCEPT SELECT Id FROM dbo.Clients WHERE Paid = 1;

Why?

When using non sargable predicate the cardinality estimation can be very bad. So replace:

<> by = with a EXCEPT set based operation

OR by an UNION set based operation

Then all statistics for the optimizer will be accurate and the final execution plan will be good enough...

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.