Improvement in AbpNotifications delete query #10427

6 Answer(s)

• 

  0

  ismcagdas created 3 years ago

  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 ?

  Save Cancel
• 

  0

  ajayak created 3 years ago

  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

  Save Cancel
• 

  0

  ajayak created 3 years ago

  

  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)
  

  Save Cancel
• 

  0

  musa.demir created 3 years ago

  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

  Save Cancel
• 

  1

  ajayak created 3 years ago

  @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.

  Save Cancel
• 

  0

  musa.demir created 3 years ago

  Thanks @ajayak We will check it, too.

  Save Cancel