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. -
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.
-
0
Oh, there is an identical query in ASP<span></span>.NET Boilerplate's
GetRoles, which is called inGetUserPermissionCacheItemto populate the cache forIsGranted. -
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?
-
0
Yes, you can configure the cache's
DefaultSlidingExpirationTime.https://aspnetboilerplate.com/Pages/Documents/Caching#configuration
-
0
Thank you very much!
-
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
-
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. -
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!
-
0
Wow, there are so many calls to UserStore.FindByIdAsync and UserStore.GetRolesAsync in a single page load. Time to do some caching!
-
0
Where does the application query UserRole and UserOrganizationUnit? Why can't I cache those too?
-
0
In the
joinqueries mentioned earlier.You can cache those too; the effect is probably less than the caching of non-user-specific
Role. -
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?
-
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
-
0
I didn't know about that, thanks! Closing again.
