Hello, I was looking at my Azure SQL usage and this query popped up as very frequent. In the past 24 hours it has been executed 21295 times. Any ideas where it's coming from and why it's so frequent?
(@__ef_filter__p_3 bit,@__ef_filter__p_4 bit,@__ef_filter__CurrentTenantId_5 int,@__ef_filter__p_6 bit,@__ef_filter__p_7 bit,@__ef_filter__CurrentTenantId_8 int,@__ef_filter__p_0 bit,@__ef_filter__p_1 bit,@__ef_filter__CurrentTenantId_2 int,@__user_Id_0 bigint)SELECT [t0].[Name] FROM [AbpUserOrganizationUnits] AS [a] INNER JOIN ( SELECT [a0].[Id], [a0].[CreationTime], [a0].[CreatorUserId], [a0].[IsDeleted], [a0].[OrganizationUnitId], [a0].[RoleId], [a0].[TenantId] FROM [AbpOrganizationUnitRoles] AS [a0] WHERE ((@__ef_filter__p_3 = CAST(1 AS bit)) OR ([a0].[IsDeleted] <> CAST(1 AS bit))) AND ((@__ef_filter__p_4 = CAST(1 AS bit)) OR ((([a0].[TenantId] = @__ef_filter__CurrentTenantId_5) AND ([a0].[TenantId] IS NOT NULL AND @__ef_filter__CurrentTenantId_5 IS NOT NULL)) OR ([a0].[TenantId] IS NULL AND @__ef_filter__CurrentTenantId_5 IS NULL))) ) AS [t] ON [a].[OrganizationUnitId] = [t].[OrganizationUnitId] INNER JOIN ( SELECT [a1].[Id], [a1].[ConcurrencyStamp], [a1].[CreationTime], [a1].[CreatorUserId], [a1].[DeleterUserId], [a1].[DeletionTime], [a1].[DisplayName], [a1].[IsDefault], [a1].[IsDeleted], [a1].[IsStatic], [a1].[LastModificationTime], [a1].[LastModifierUserId], [a1].[Name], [a1].[NormalizedName], [a1].[TenantId] FROM [AbpRoles] AS [a1] WHERE ((@__ef_filter__p_6 = CAST(1 AS bit)) OR ([a1].[IsDeleted] <> CAST(1 AS bit))) AND ((@__ef_filter__p_7 = CAST(1 AS bit)) OR ((([a1].[TenantId] = @__ef_filter__CurrentTenantId_8) AND ([a1].[TenantId] IS NOT NULL AND @__ef_filter__CurrentTenantId_8 IS NOT NULL)) OR ([a1].[TenantId] IS NULL AND @__ef_filter__CurrentTenantId_8 IS NULL))) ) AS [t0] ON [t].[RoleId] = [t0].[Id] WHERE (((@__ef_filter__p_0 = CAST(1 AS bit)) OR ([a].[IsDeleted] <> CAST(1 AS bit))) AND ((@__ef_filter__p_1 = CAST(1 AS bit)) OR ((([a].[TenantId] = @__ef_filter__CurrentTenantId_2) AND ([a].[TenantId] IS NOT NULL AND @__ef_filter__CurrentTenantId_2 IS NOT NULL)) OR ([a].[TenantId] IS NULL AND @__ef_filter__CurrentTenantId_2 IS NULL)))) AND (([a].[UserId] = @__user_Id_0) AND @__user_Id_0 IS NOT NULL)
15 Answer(s)
-
0
It appears to be from
GetAllRoleNamesOfUsersOrganizationUnits, which is called inGetUserForEdit.Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
Intersting. I don't see how that is possible. That is a manual/interactive administrator action, so I don't see how it's being executed 14 times per minute. Could there be another place? It looks like it's happening on page loads.
Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
Oh, there is an identical query in ASP<span></span>.NET Boilerplate's
GetRoles, which is called inGetUserPermissionCacheItemto populate the cache forIsGranted.Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
I see. I have very simple permissioning that doesn't change. Is there any way to increase this cache's ttl so as to minimize these queries?
Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
Yes, you can configure the cache's
DefaultSlidingExpirationTime.https://aspnetboilerplate.com/Pages/Documents/Caching#configuration
Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
Thank you very much!
Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
Reopening. This did not actually fix the issue with numerous queries. I can't tell if it's Abp making them or Zero. Below is a screenshot with Azure with the most numerous queries (sorted descending). The one on the bottom is a query from my application. I don't have multi-tenantcy enabled, my permissions are very straightforward, I have two roles, one user and one admin and that's about it. The amount of database requests is much too high. What can I do about this?
The tables being queries are: AbpUsers AbpRoles The original query above is #3 in the list A join on AbpUserRoles and AbpRoles AbpRoleClaims AbpUserClaims
Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
How many users do you have? The application needs to make 2 DB calls (
joinqueries) per active user.You can override
AbpUserStore.GetRolesto aggressively cache eachRoleandOrganizationUnitRolein memory. This would not reduce the number of queries, since the application still needs to queryUserRoleandUserOrganizationUnit(assuming you have a sizeable number of users), but at least it would be single-table queries.Do you use
UserOrganizationUnit? If you don't, then aggressively caching justRoleis sufficient — reducing 2joinqueries to 1 simple query.Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
I have about 800 users right now. Which is why the execution counts look very high to me, because it's not a lot of users.
I don't use UserOrganizationUnit. I will try the caching you mentioned, thanks!
Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
Wow, there are so many calls to UserStore.FindByIdAsync and UserStore.GetRolesAsync in a single page load. Time to do some caching!
Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
Where does the application query UserRole and UserOrganizationUnit? Why can't I cache those too?
Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
In the
joinqueries mentioned earlier.You can cache those too; the effect is probably less than the caching of non-user-specific
Role.Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
Understood.
One more thing: Caching of User result in UserStore.FindByIdAsync will have a high impact, because that is #1 by count. It's easy enough to cache it there, but I am afraid that it will have unforeseen consequences if I forget to update the cache when a given user is updated. Besides AccountController and AccountAppService, is there any other place where I should invalidate the userid from the cache?
Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
I recommend that you invalidate via Domain Event handlers, rather than in certain API. You can take ASP<span></span>.NET Boilerplate's
AbpUserPermissionCacheItemInvalidatoras a reference.Domain Events: https://aspnetboilerplate.com/Pages/Documents/EventBus-Domain-Events
Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image) -
0
I didn't know about that, thanks! Closing again.
Markdown is supportedCopy & paste or drag & drop images (max 30 MB per image)
