Base solution for your next web application
Open Closed

Improvement in AbpNotifications delete query #10427

User avatar
0
ajayak created
  • What is your product version? 10.2
  • What is your product type (Angular or MVC)? Angular
  • What is product framework type (.net framework or .net core)? .net core

From Azure db analytics, the top resource consuming query for our DB is:

(@p0 uniqueidentifier)DELETE FROM [AbpNotifications]
WHERE [Id] = @p0

Looks like notifications are deleted 1 by 1. Is it possible to modify this query to use WHERE Id IN (1,2,3,...)

Go to accepted answer
6 Answer(s)
  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi @ajayak

    A notification is deleted when it is sent to a user. But, Id field should be a primary key and shouldn't cause a performance problem. Could you check if it is a PK or not on your DB ?

  • User Avatar
    0
    ajayak created

    Hi @ismcagdas,

    Yes, it is the PK but in our case, we are probably sending a lot of notifications. And deleting hundreds of notifications by sending a seperate query for each delete operation is the problem. Instead of sending 100 queries, this can be optimized to send 1 query

  • User Avatar
    0
    ajayak created

    Query 1:

    (@UpperBound uniqueidentifier)SELECT   [Id],
         [CreationTime],
         [CreatorUserId],
         DATALENGTH([Data]),
         [Data],
         [DataTypeName],
         [EntityId],
         [EntityTypeAssemblyQualifiedName],
         [EntityTypeName],
         [NotificationName],
         [Severity],
         [TenantId]
FROM     [dbo].[AbpTenantNotifications]
WHERE    [Id] < @UpperBound
ORDER BY [Id] ASC

    Query 2:

    (@p0 uniqueidentifier)DELETE FROM [AbpNotifications]
WHERE [Id] = @p0

    Query 3:

    (@__ef_filter__p_0 bit,@__ef_filter__CurrentTenantId_1 int,@__utcNow_0 datetime2(7))SELECT [a].[Id], [a].[ExpireDate], [a].[LoginProvider], [a].[Name], [a].[TenantId], [a].[UserId], [a].[Value]
FROM [AbpUserTokens] AS [a]
WHERE ((@__ef_filter__p_0 = CAST(1 AS bit)) OR ([a].[TenantId] = @__ef_filter__CurrentTenantId_1)) AND ([a].[ExpireDate] <= @__utcNow_0)
  • User Avatar
    0
    musa.demir created

    Hi @ajayak Thank you for detailed explanation. I created an issue about it. We will investigate it and try to find better way if it causes performance problem. You can follow the progress here https://github.com/aspnetzero/aspnet-zero-core/issues/3978

  • User Avatar
    1
    ajayak created

    @musa.demir can you also check the first query?

    (@UpperBound uniqueidentifier)SELECT   [Id],
         [CreationTime],
         [CreatorUserId],
         DATALENGTH([Data]),
         [Data],
         [DataTypeName],
         [EntityId],
         [EntityTypeAssemblyQualifiedName],
         [EntityTypeName],
         [NotificationName],
         [Severity],
         [TenantId]
FROM     [dbo].[AbpTenantNotifications]
WHERE    [Id] < @UpperBound
ORDER BY [Id] ASC

    2 executions of this query took almost 13 minutes as shown by Azure graph. May be this query is fetching a huge chunk of data. We have 31384 records in AbpTenantNotifications table.

  • User Avatar
    0
    musa.demir created

    Thanks @ajayak We will check it, too.

Assignee
No one
Labels
None yet