Developer forum

Forum » CMS - Standard features » Performance of personalization

Performance of personalization

E-mail notifications
Scott Forsyth
Reply
Posted on 22/08/2016 17:42:27

We're running into a significient performance hit when using personalization on a site. When checking SQL Profiler we see a query like the following for each personalization profile.

SELECT * FROM AccessUser WHERE AccessUserID IN (SELECT AccessUser.AccessUserID FROM AccessUser WHERE AccessUserType IN (1,3,4,5,15) AND (AccessUser_church_keyboard_Piano = 1)) ORDER BY AccessUserActive Asc

The site has over 100K users, so even with an index on the AccessUser_church_keyboard_Piano boolean field, the query takes 3-6 seconds. So for 8 personalization profiles, it's taking about a minute for pages to load.

Shouldn't that query have a where clause filter for the logged in user? So it should be something like:

SELECT * FROM AccessUser WHERE AccessUserID IN (SELECT AccessUser.AccessUserID FROM AccessUser WHERE AccessUserType IN (1,3,4,5,15) AND (AccessUser_church_keyboard_Piano = 1)) AND AccessUserId = 12345 ORDER BY AccessUserActive Asc

That should make the query be almost instant. Does that sound right?

Thanks,

Scott


Replies

 
Nicolai Høeg Pedersen
Posted on 24/08/2016 11:56:29
Reply

Hi Scott

I've send this to QA for investigation. Will get back.

BR Nicolai

 
Oleg Rodionov
Posted on 25/08/2016 04:24:27
Reply

Hi Scott,

As per your explanations above, currently I assume the following scenario you use in the case:

 - there are several marketing profiles has rule based on various smart searches (e.g. search to get list of users belong to specific user group)

To optimize the rendering (to use your suggestion in SQL request) we have to extend(improve) smart search functionality, e.g. add new rule to point user as current one.

Please, comment and specify your scenario exactly if my one is not fully correct not to have misunderstanding..

 

BR, Oleg QA

 
Scott Forsyth
Posted on 26/08/2016 08:02:03
Reply

Hi Oleg,

Thanks for your reply. To be honest I don't know for sure because it was a profile that the customer setup and I didn't look at it myself. We've deleted them for now so that it doesn't hold up the site while we're doing other things. But what you described sounds right.

Is that enough information for you, or should I ask the customer to set it up again so that you can tell for sure?

Thanks,

Scott

 

You must be logged in to post in the forum