Hi Alexander,

Since you say it is still slow after the new indexes I will need your help to troubleshoot it further.

Did it improve any with the new indexes?

The stored procedure mp_ModuleSettings_SyncDefinitions runs a series of queries, we need to isolate the slow part to better understand it and come up with a strategy to solve it.

What I would like you to do is:

1. backup your copy of mp_ModuleSettings_SyncDefinitions
2. chop out all except for the first sql statment
3. execute the proc and see is it faster with just the first statement and no other statements
4. if so then 1 by 1 add the original statements back in and test again until you can identify which statement is the problem
5. or just put them in one at a time and measure the time for each one might be even more helpful
6. then email me the statement or post it here using the plain text toolbar

In the mean time I will also look at the query execution plan for each step and see if I can some up with some optimizations.

Best,

Joe

Hi Alexander,

Can you try modify the procedure as follows and then see if performance is better? Now it is all in a single statement.

ALTER PROCEDURE [dbo].[mp_ModuleSettings_SyncDefinitions]

AS

UPDATE ms
SET ms.RegexValidationExpression = mds.RegexValidationExpression,
ms.ControlSrc = mds.ControlSrc,
ms.ControlType = mds.ControlType,
ms.SortOrder = mds.SortOrder,
ms.HelpKey = mds.HelpKey
FROM mp_ModuleSettings ms
JOIN mp_Modules m
ON ms.ModuleID = m.ModuleID
JOIN mp_ModuleDefinitionSettings mds
ON ms.SettingName = mds.SettingName
AND m.ModuleDefId = mds.ModuleDefId

GO

It should be much faster I think.

Best,

Joe

Hi Alexander,

That is a good improvement. To improve it further, I will make the Setup page queue that on a background thread so it doesn't block the web ui waiting for it to complete.

Best,

Joe