Perf improvements for user picker

Scott Forsyth

Posted on 16/11/2015 04:23:20

We have some sites with hundreds of thousands of users. We're finding that the user picker is extremly slow. In particular, one that we run into often is in email marketing when selecting users. For a copule sites it takes longer than 30 seconds to be able to interact with the list of users. This seems like it should be easy to address.

I see from SQL Profiler that the query is: "exec sp_executesql N'SELECT * FROM [AccessUser] WHERE ( [AccessUser].[AccessUserType] IN (@p0,@p1,@p2,@p3,@p4) ) order by accessuserid',N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 int',@p0=1,@p1=3,@p2=4,@p3=5,@p4=15"

I do notice that if you page through the pages that it's much faster, so the entire user list is loaded into memory and paging occurs with that. However, for really large sets of users, that's very slow.

Would it be possible in a future version to do the paging in the SQL Server calls (e.g. SELECT TOP X) rather than pulling everything across and paging in code?

Thanks,

Scott

Replies

Scott Forsyth

Posted on 16/11/2015 04:30:25

I'll add a second request here to the same thread.

Another situation is in the user picker in the main Users section in the backend. If you search by some text then it searches through the key fields using a LIKE filter. This is also very slow and can take many seconds to return this result.

Ideally this could use a lucene search, however I realize there could be challenges with it keeping it up to date immediately after changes.

There is one quick win though. I see from using SQL Profiler that there are two queries that occur. One is a long query that checks for a text match in multiple fields. That makes enough sense. However, after that query completes, there is another "SELECT COUNT(ProductID) ..." command. That query takes just as long as the first one which essentially doubles the time that it takes to return any results. That's fine for a small number of users, but it doesn't perform well for large user sets. To get the count, can you count the records in the recordset from the first query rather than going back to the database to do another long running query?

Thanks,

Scott

Christian Rud Skovgaard

Posted on 18/11/2015 15:28:42

This post has been marked as an answer

This idea is now implemented in Dynamicweb 8.8!

Kind regards

Christian

Votes for this answer: 1

Scott Forsyth

Posted on 20/11/2015 17:49:46

Hi Christian,

That's great news. Thanks!

Scott

Nuno Aguiar

Posted on 25/11/2015 13:09:05

Hi Christian,

Do the fixes account for Smart Searches? Scott was explicit about selecting users but we have the same problem with a client that has about 20 smart searches and takes a long time viewing/setting a new one and selecting it from email marketing.

Best Regards,

Nuno Aguiar

Kristian Kirkholt

Posted on 01/02/2016 14:21:57

This post has been marked as an answer

Hi Scott

The Performance improvements for user picker has now been implementet in Dynamicweb version 8.8.0.0

You are able to find this build in the download section:

http://developer.dynamicweb-cms.com/downloads/dynamicweb-8.aspx

Please contact Dynamicweb Support if you need any additional help regarding this.

Kind Regards
Dynamicweb Support
Kristian Kirkholt

Votes for this answer: 1

Scott Forsyth

Posted on 01/02/2016 15:36:34

Thanks Kristian,

I noticed that in the release notes. Thanks! I'll check it out.

Scott

Developer forum

Perf improvements for user picker

Replies