Cleaning up userpreference table, windows to web

Hello,

We are currently in the process of migrating a large customer (350 users) from 8.0 SR4 Windows to 8.5 R7 Web.

They have been an long time customer and currently have 155k records in the userpreference table. We noticed during testing that the initial load times of the web & admin client, especially the preferences section, takes a very long time. The slow query log shows that the userpreferences query takes a long time. (it loads all 155k records).

After cleaning up all userpreferences of associates that have been long retired, we have 49k records left. The initial load times of the web & admin client are a lot faster now.

Of those 49k records, 15k are 'liveupdate' prefkeys. Is it safe to delete these prefkeys? As far as I know these are windows client specific, which they don't use anymore.

Thanks,

David

RE: Cleaning up userpreference table, windows to web

Yes, safe to delete.
I think in fact that with a little investigation you'd probably be able to delete 98% of all user preference to get a fresh start.
That said, Win stores 1 liveUpdate key per archive per user. So, seems to me that the system here is loading the entire db...
The Win preference cache is per logged in user and takes typically 10ms to load:

13:12:35:546 -- -- -- -- -- -- -- 0.001370 class SOPreference
13:12:35:548 -- -- -- -- -- -- -- 0.108861 SOPreference::Impl::LoadCache
13:12:35:673 -- -- -- -- -- -- -- 0.053860 class SSysEventAlerter

/conrad

Von: Conrad Weyns 26. Nov 2019

RE: Cleaning up userpreference table, windows to web

Hello Conrad,

Thanks for the info, will delete these prefkeys then after the migration is done.

RE: loading the preferences, from the slow query log it looks like netserver loads the whole userpreference table;

Level:   Information
At:      18:30:57

Element:
Message: Long-running SQL Command
Type:    SuperOffice.Data.SoCommand
Details:
Milliseconds: 3
Command:	/* Default | eugrdavid (1726) | MDOGetSelectableList | SuperOfficeCRMRowsUserPreferenceRowsCustomSearch */SELECT T0."userpreference_id", T0."deflevel", T0."maxlevel", T0."owner_id", T0."prefsection", T0."prefkey", T0."prefvalue", T0."registered", T0."registered_associate_id", T0."updated", T0."updated_associate_id", T0."updatedCount" FROM  crm."USERPREFERENCE" T0 
Von: David Hollegien 26. Nov 2019

RE: Cleaning up userpreference table, windows to web

If I look at my userpreferences in my local test database with:
SELECT * FROM crm7.USERPREFERENCE WHERE owner_id=106
I can't find 1 single record that could not be deleted - to get a fresh start.

However, in case you are worried, here are some that are perfectly safe to delete:

prefsection like:
'%win:%'
'%WIN_%'
'LookAndFeel'
'ViewHeights'
'ViewWidths'
'SubModes'
'Tasks'
'MiniPanelView'
'BulkUpdate%'
'Splitter%'
'Archives'
'Cripple%'


prefkey like:
'ProviderFactoryCacheEnabled'
'CriteriaViewHeightFor%'

 

Other tables:
usagestat
webappusage where viewstate like 'win.%'

 


/conrad

Von: Conrad Weyns 27. Nov 2019

RE: Cleaning up userpreference table, windows to web

Hi guys, was the conclusion for prefkey='LiveUpdate' that those could be deleted as well?

Seems like we should be able to compile together a good SQL script for cleaning up when customer switches over to the Web client.

Also, remember to include deflevel=5 when checking owner_id, to only delete for users, not usergroups.

 

Von: Frode Lillerud 27. Nov 2019

RE: Cleaning up userpreference table, windows to web

Yes, I said so in my first answer.
But where prefsection like 'win:%' and 'win_%' will take along all of those.
None of these that I have listet are group dependent but adding deflevel=5 sounds reasonable.
/conrad

 

Seems to me that once a client is migrating - thus no more Win client ever - there should be a db step to clean up everything that is win only....

Von: Conrad Weyns 27. Nov 2019

RE: Cleaning up userpreference table, windows to web

I had a lot of 'LiveUpdate' rows that didn't start with 'win' in prefsection. They had prefsection like 'PersonArchive' etc.

Agreed, when migrating to Online it makes sense to remove all Win-specific userpreferences. When an Onsite customer decides to start using the Web client instead we could run these queries ourselves (unless there was a DBSetup.exe task for "Remove Windows-client specific settings", or something like that.

-- Clean up all Windows client related rows
delete from crm8.USERPREFERENCE where prefsection like '%win:%' and deflevel = 5
delete from crm8.USERPREFERENCE where prefsection like '%WIN_%' and deflevel = 5
delete from crm8.USERPREFERENCE where prefsection like 'LookAndFeel' and deflevel = 5
delete from crm8.USERPREFERENCE where prefsection like 'ViewHeights' and deflevel = 5
delete from crm8.USERPREFERENCE where prefsection like 'ViewWidths' and deflevel = 5
delete from crm8.USERPREFERENCE where prefsection like 'SubModes' and deflevel = 5
delete from crm8.USERPREFERENCE where prefsection like 'Tasks' and deflevel = 5
delete from crm8.USERPREFERENCE where prefsection like 'MiniPanelView' and deflevel = 5
delete from crm8.USERPREFERENCE where prefsection like 'BulkUpdate%' and deflevel = 5
delete from crm8.USERPREFERENCE where prefsection like 'Splitter%' and deflevel = 5
delete from crm8.USERPREFERENCE where prefsection like 'Archives' and deflevel = 5
delete from crm8.USERPREFERENCE where prefsection like 'Cripple%' and deflevel = 5

delete from CRM8.USERPREFERENCE where prefkey like 'LiveUpdate' and deflevel = 5
delete from crm8.USERPREFERENCE where prefkey like 'CriteriaViewHeightFor%' and deflevel = 5
delete from crm8.USERPREFERENCE where prefkey like 'ProviderFactoryCacheEnabled' and deflevel = 5
Von: Frode Lillerud 27. Nov 2019

RE: Cleaning up userpreference table, windows to web

Could one perhaps investigate this the other way round?
Looking at your userpreferences, what are good candidates to keep?

Looking at prefsection:

Defaults
Filter
Functions
Sale
Sorting
System

And offcourse whatever third party intergrations have been added for a per user pref.

All the rest can, i.m.o. be safely deleted.

I just run this query on my local test db:
DELETE FROM crm7.USERPREFERENCE WHERE owner_id=106
Then started the web client. No problem. Ok, so I will need to reconfigure a few things like the activity Filter setting a.o...

/conrad

Other tables: 
winpossize
taborder where tabName like 'WIN_%'
superlistcolumnsize

 

Von: Conrad Weyns 27. Nov 2019

RE: Cleaning up userpreference table, windows to web

Hello,

We ultimately came up with the following SQL script to safely clean up the userpreferences table without removing any user specific setting for web users:

DELETE FROM crm.USERPREFERENCE

WHERE 

-- windows specific settings
(
prefsection IN ('LookAndFeel', 'ViewHeights', 'ViewWidths', 'SubModes', 'Tasks', 'Archives')
OR prefkey IN ( 'ProviderFactoryCacheEnabled', 'LiveUpdate')
)

-- defined on associate level
AND deflevel = 5

-- from non-existing associate
OR owner_id NOT IN (SELECT crm.ASSOCIATE.associate_id FROM crm.ASSOCIATE)

-- defined on associate level
AND deflevel = 5

Note: Conrad also mentions the 'Splitter' userpreferences, but it seems like these are also used in the web version.

Von: David Hollegien 6. Dez 2019