Developer forum

Forum » CMS - Standard features » Many DB queries at login when the user has Impersonation users / secondary users

Many DB queries at login when the user has Impersonation users / secondary users

E-mail notifications
Peter Krusell
Reply
Posted on 19/04/2023 12:36:17

When a user has "I can impersonate" users selected there is one DB query for each individual Impersonation user selected. See screenshot of selected "I can impersonate" users and the corresponding amount of "SELECT * FROM [AccessUserAddress] WHERE ( [AccessUserAddress].[AccessUserAddressUserID] = @p0 AND [AccessUserAddressDefaultAddressCustomFields] = 1)" shown in the debug=true log.

We have verifed this behavior in several installations running 9.14 and above.
This gererally gives bad performance at login for users with many Impersonation relations, but it has not been problematic enough for anyone to notice it other than a slow login.

Recently one client added a lot of users/adresses in their PROD solution and now has about 20 000 adresses in the database. And some users that can impersonate about 1500 Impersonation users. In that case the login takes about 40 sec(1500/40sec = about 37 extra queries per second) and it causes severe problems in the SQL database server so that some tabels get locked. And if there is ocationally a Data import activity schduled to happen at the same time as someone logs in then the application hangs / crashes. So in this case its a very business critical problem.

The screenshot is from their STAGE solution where there are fewer users/adresses than PROD - but you see the behavior that there are 376 queries "SELECT * FROM [AccessUserAddress...." and the corresponding amout of active Impersonation users in backoffice. If the Impersonation users are deselected in the backoffice the extra 376 queries disappear from the debug=true log.

Hope you can help. Thanks
/Peter

Blinkfyrar_problem.jpg

Replies

 
Peter Krusell
Posted on 21/04/2023 07:56:57
Reply

Digging deeper it happens in PageViewModel based templates when Model.SecondaryUsers.Count is used in the template code.

 
Peter Krusell
Posted on 27/04/2023 10:55:12
Reply

One possible workaround is to use Pageview.User.CanImpersonate() instead to check if a user shoud get the impersonation bar rendered or not.

 
Nuno Aguiar Dynamicweb Employee
Nuno Aguiar
Posted on 27/04/2023 11:05:20
Reply

Hi Peter,

 

We've solved it differently when having performance issues. It's a bigger fix, but ensured everything correctly, to account for when a user manages user group and other users.

 

What you basically need is to know if the list of users to impersonate that uses the Query Publisher will return more than 0 results. That's the only way to ensure that "can impersonate" boolean honors everything.

 

So what we did was to use the API to get the SecondaryUsers query and check the amount of results and if any, then we considered the user can impersonate someone. In the template it was simple enough because we simply replace DW's CanImpersonate() method with our own (an extension for example). But it does require a small dll to do that. Or you can also put that code directly in the template.

 

Obvisouly we had to assume/hardcode the name of the Repository and Query to do this, so there's a strong binding with it, but then again, so is there with pageNavitagionTags and other things. If you want to make it a bit more dynamic, you can get the Impersonation page through navigation tag, get the paragraph with the Query Publisher app, get the module settings, get the query setting and from there also get the repository name. All doable :)

 

Hope this helps,

Nuno Aguiar

 
Morten Snedker Dynamicweb Employee
Morten Snedker
Posted on 27/04/2023 11:08:45
Reply

Hi Peter,

A good finding. It is a bit hidden as most solutions only have few persons to impersonate, so it is not discovered. But as in your case, having a relative high amount of impersonatees, the problem shows with the repetition of SQL calls. 

This misbehavior in the PageViewModel has been added to the list of areas of the PageViewModel that requires updating in terms of performance. So thank you for bringing it to our attention.

It is good that you found a work-around that works. In addition to that, and Nuno's approach, I can point to the GetLoop("DWExtranetSecondaryUsers").Count that is accessible from the user template, which will also perform properly.

BR
Snedker

 

 

You must be logged in to post in the forum