Skip to Content
0

Add popup menu item - very poor performance

Nov 30, 2016 at 04:29 PM

136

avatar image
Former Member

Hi,

I'm adding lot of menu items into Main Menu from my addon.

When I add "mt_POPUP" menu item with menu.AddEx or LoadBatchActions, it takes significantly more time then add "mt_STRING" menu item.

I've looked into SQL Server Profiler and I've seen some work with OCMN table (Why are my POPUP menu items stored in OCMN table?). And sometimes for one popup menu item is trigged permission check for all non-superusers in SAP B1 (why?). This permission check can take about 2 minutes with CPU core on 100%! It looks into USR3, OUPT, OUGR tables for each user.

Any hints how to deal with this?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

DIEGO LOTHER Nov 30, 2016 at 06:53 PM
0

Hi Tomas,

Could you share your code?

I add normally menus "mt_STRING" or "mt_POPUP" and they use a similar time to add the menu. Time 150 miliseconds.

Did you have tested in other machine or SBO instalation?

My SAP version is 9.1 PL 12.

Kind Regards,

Diego Lother

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 01, 2016 at 03:35 PM
0

Hi Diego,

The 2 minutes delay is on one customer installation with SAP 9.1 PL09, MS SQL Server version

From SQL Server Profiler

for each "mt_POPUP":

exec sp_executesql N'SELECT COUNT(''A'') FROM [dbo].[OCMN] T0 WHERE T0.[GUID] = (@P1) ',N'@P1 nvarchar(32)',N'Info21_STD_1'
exec sp_executesql N'SELECT T0.* FROM [dbo].[OCMN] T0 WHERE T0.[GUID] = (@P1) ORDER BY T0.[GUID]',N'@P1 nvarchar(32)',N'Info21_STD_1'
EXECUTE SBO_SP_TransactionNotification N'410000000',N'U',1,N'GUID',N'Info21_STD_1'
EXECUTE SBO_SP_PostTransactionNotice N'410000000',N'U',1,N'GUID',N'Info21_STD_1'

exec sp_executesql N'SELECT COUNT(''A'') FROM [dbo].[OCMN] T0 WHERE T0.[GUID] = (@P1) ',N'@P1 nvarchar(32)',N'Info21_STD_2'
exec sp_executesql N'SELECT T0.* FROM [dbo].[OCMN] T0 WHERE T0.[GUID] = (@P1) ORDER BY T0.[GUID]',N'@P1 nvarchar(32)',N'Info21_STD_2'
EXECUTE SBO_SP_TransactionNotification N'410000000',N'U',1,N'GUID',N'Info21_STD_2'
EXECUTE SBO_SP_PostTransactionNotice N'410000000',N'U',1,N'GUID',N'Info21_STD_2'


...
to this "mt_POPUP" with UID=Info21_C03_5

exec sp_executesql N'SELECT COUNT(''A'') FROM [dbo].[OCMN] T0 WHERE T0.[GUID] = (@P1) ',N'@P1 nvarchar(32)',N'Info21_C03_5'
exec sp_executesql N'SELECT T0.* FROM [dbo].[OCMN] T0 WHERE T0.[GUID] = (@P1) ORDER BY T0.[GUID]',N'@P1 nvarchar(32)',N'Info21_C03_5'
exec sp_executesql N'SELECT COUNT(''A'') FROM [dbo].[OCMN] T0 WHERE T0.[Father] = (@P1) ',N'@P1 nvarchar(32)',N''
exec sp_executesql N'SELECT COUNT(''A'') FROM [dbo].[OCMN] T0 WHERE T0.[Father] = (@P1) ',N'@P1 nvarchar(32)',N''
exec sp_executesql N'SELECT MAX(T0.[SortOrder]) FROM [dbo].[OCMN] T0 WHERE T0.[Father] = (@P1) ',N'@P1 nvarchar(32)',N''
exec sp_executesql N'SELECT COUNT(''A'') FROM [dbo].[OCMN] T0 WHERE T0.[GUID] = (@P1) AND (T0.[Father] IS NULL OR T0.[Father] = (@P2) ) ',N'@P1 nvarchar(32),@P2 nvarchar(32)',N'Info21_C03_5',N''

which starts

declare @p1 int
set @p1=137
exec sp_prepexec @p1 output,N'@P1 nvarchar(32)',N'SELECT T0.* FROM [dbo].[OCMN] T0 WHERE T0.[GUID] = (@P1) ',N'Info21_C03_5'
select @p1

declare @p1 int
set @p1=138
exec sp_prepexec @p1 output,N'@P1 int',N'SELECT T0.* FROM [dbo].[CDPM] T0 WHERE T0.[PermId] = (@P1) ',10312
select @p1

exec sp_executesql N'SELECT T0.* FROM [dbo].[OUSR] T0 WHERE T0.[SUPERUSER] = (@P1) AND T0.[GROUPS] <> (@P2) ',N'@P1 char(1),@P2 smallint','N',99

foreach user

{
exec sp_executesql N'SELECT T0.* FROM [dbo].[USR3] T0 WHERE T0.[UserLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',4
SELECT T0.[AbsId] FROM [dbo].[OUPT] T0 ORDER BY T0.[AbsId]
exec sp_executesql N'SELECT T0.* FROM [dbo].[USR3] T0 WHERE T0.[UserLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',4
SELECT T0.* FROM [dbo].[OUGR] T0
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',-4
SELECT T0.[AbsId] FROM [dbo].[OUPT] T0 ORDER BY T0.[AbsId]
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',-4
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',-3
SELECT T0.[AbsId] FROM [dbo].[OUPT] T0 ORDER BY T0.[AbsId]
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',-3
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',-2
SELECT T0.[AbsId] FROM [dbo].[OUPT] T0 ORDER BY T0.[AbsId]
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',-2
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',-1
SELECT T0.[AbsId] FROM [dbo].[OUPT] T0 ORDER BY T0.[AbsId]
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',-1
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',1
SELECT T0.[AbsId] FROM [dbo].[OUPT] T0 ORDER BY T0.[AbsId]
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',1
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',2
SELECT T0.[AbsId] FROM [dbo].[OUPT] T0 ORDER BY T0.[AbsId]
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',2
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',3
SELECT T0.[AbsId] FROM [dbo].[OUPT] T0 ORDER BY T0.[AbsId]
exec sp_executesql N'SELECT T0.* FROM [dbo].[UGR1] T0 WHERE T0.[GroupLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',3
exec sp_executesql N'SELECT T0.[GroupId], T0.[GroupName], T0.[GroupDec] FROM [dbo].[OUGR] T0 INNER JOIN [dbo].[USR7] T1 ON T1.[GroupId] = T0.[GroupId] WHERE T1.[UserId] = (@P1) ',N'@P1 smallint',4
exec sp_executesql N'SELECT T0.* FROM [dbo].[USR3] T0 WHERE T0.[UserLink] = (@P1) ORDER BY T0.[PermId]',N'@P1 smallint',4
exec sp_execute 123,4
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
exec sp_execute 128,4
exec sp_executesql N'SELECT COUNT(''A'') FROM [dbo].[OUDG] T0 WHERE T0.[Code] = (@P1) ',N'@P1 nvarchar(8)',N'STANDARD'
SELECT T0.[AbsId] FROM [dbo].[OUPT] T0
exec sp_execute 94,N'someusername'

}
endforeach

times for Menu Items from remote debug:

My code snippet:

public override void OnAddonStarted()
{
    var menuForm = this.SboController.SboApp.Forms.Cast<SAPbouiCOM.Form>().FirstOrDefault(item => item.TypeEx == "169");
    if (menuForm != null) menuForm.Freeze(true);

    try
    {
        this.AddMenus(String.Empty, SboAddonGraphicsHelper.GetComponentImage16(this.SboController, SboAddonGraphicsHelper.CompCommon));

        if (this.SboController.AppContext.IsLicensedInternalCode("MODULE_1"))
        {
            this.AddMenus("MODULE_1", SboAddonGraphicsHelper.GetComponentImage16(this.SboController, SboAddonGraphicsHelper.CompPackage21));
        }

        if (this.SboController.AppContext.IsLicensedInternalCode("MODULE_2"))
        {
            this.AddMenus("MODULE_2", SboAddonGraphicsHelper.GetComponentImage16(this.SboController, SboAddonGraphicsHelper.CompProd21));
        }

        if (this.SboController.AppContext.IsLicensedInternalCode("MODULE_3"))
        {
            this.AddMenus("MODULE_3", SboAddonGraphicsHelper.GetComponentImage16(this.SboController, SboAddonGraphicsHelper.CompProd21Lite));
        }

        if (this.SboController.AppContext.IsLicensedComponent(this.SboController.AppContext.CustomerCode))
        {
            this.AddMenus(this.SboController.AppContext.CustomerCode, SboAddonGraphicsHelper.GetComponentImage16(this.SboController, SboAddonGraphicsHelper.CompCustomer), this.SboController.AppContext.CustomerCode);
        }
    }
    finally
    {
        if (menuForm != null)
        {
            menuForm.Freeze(false);
            menuForm.Update();
        }
    }
}

#region Build Main menu

private void AddMenus(string project, string image, string rootTitle = null)
{
    MenuProvider menuProvider = new MenuProvider(this.SboController.AppContext);
    menuProvider.Init(project);
    if (menuProvider.Root.Items.Count > 0)
    {
        MenuItem root = menuProvider.Root.Items[0];
        if (rootTitle != null) root.Title = rootTitle;

        string sRootMenuItemId = CreateMenuUid(menuProvider.MenuItemPrefix, root);
        if (this.SboController.SboApp.Menus.Exists(sRootMenuItemId))
        {
            this.SboController.SboApp.Menus.RemoveEx(sRootMenuItemId);
        }

        this.AddMenuItem(menuProvider, root, this.SboController.SboApp.Menus.Item("43520").SubMenus, image);
    }
}

private static List<Tuple<MenuItem, TimeSpan>> _mi = new List<Tuple<MenuItem, TimeSpan>>();

private void AddMenuItem(MenuProvider menuProvider, MenuItem current, SAPbouiCOM.Menus menu, string image = null)
{
    SAPbouiCOM.MenuItem menuitem = null;
    if (current.MenuType == MenuItemType.Item)
    {
        var stopwatch = System.Diagnostics.Stopwatch.StartNew();

        SAPbouiCOM.MenuCreationParams param = this.SboController.SboApp.CreateObject(SAPbouiCOM.BoCreatableObjectType.cot_MenuCreationParams);
        param.UniqueID = CreateMenuUid(menuProvider.MenuItemPrefix, current);
        param.Enabled = true;
        param.Type = SAPbouiCOM.BoMenuType.mt_STRING;
        param.String = current.Title;
        if (!String.IsNullOrEmpty(image)) param.Image = image;
        param.Position = menu.Count;
        menuitem = menu.AddEx(param);

        stopwatch.Stop();
        _mi.Add(new Tuple<MenuItem, TimeSpan>(current, stopwatch.Elapsed));
    }
    else if (current.MenuType == MenuItemType.Report)
    {
        var stopwatch = System.Diagnostics.Stopwatch.StartNew();

        SAPbouiCOM.MenuCreationParams param = this.SboController.SboApp.CreateObject(SAPbouiCOM.BoCreatableObjectType.cot_MenuCreationParams);
        param.UniqueID = CreateMenuUid(menuProvider.MenuItemPrefix, current);
        param.Enabled = true;
        param.Type = SAPbouiCOM.BoMenuType.mt_STRING;
        param.String = current.Title;
        if (!String.IsNullOrEmpty(image)) param.Image = image;
        param.Position = menu.Count;
        menuitem = menu.AddEx(param);

        stopwatch.Stop();
        _mi.Add(new Tuple<MenuItem, TimeSpan>(current, stopwatch.Elapsed));
    }
    else if (current.MenuType == MenuItemType.Submenu)
    {
        var stopwatch = System.Diagnostics.Stopwatch.StartNew();

        SAPbouiCOM.MenuCreationParams param = this.SboController.SboApp.CreateObject(SAPbouiCOM.BoCreatableObjectType.cot_MenuCreationParams);
        param.UniqueID = CreateMenuUid(menuProvider.MenuItemPrefix, current);
        param.Enabled = true;
        param.Type = SAPbouiCOM.BoMenuType.mt_POPUP;
        param.String = current.Title;
        if (!String.IsNullOrEmpty(image)) param.Image = image;
        param.Position = menu.Count;
        menuitem = menu.AddEx(param);

        stopwatch.Stop();
        _mi.Add(new Tuple<MenuItem, TimeSpan>(current, stopwatch.Elapsed));

        foreach (var item in current.Items)
        {
            this.AddMenuItem(menuProvider, item, menuitem.SubMenus);
        }
    }
}

private static string CreateMenuUid(string prefix, MenuItem item)
{
    return prefix + item.Id.ToString(System.Globalization.CultureInfo.InvariantCulture);
}

#endregion

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Tomas,

Did you try run your code in a demo database?

I believe that is a specific situation in your customer SBO. But it is necessary to have certain. Maybe your customer has a lot of user permission rules. SAP Business One allow us to create permissions(Authorizations) for menus and forms from Add-ons, so, it is normal SBO verifies this information, but it is not normal to take long time.

Kind Regards,

Diego Lother

0
avatar image
Former Member Dec 05, 2016 at 09:34 AM
0

Hi Diego,

We don't use these permissions. But there are entries that not valid anymore. Is there any chance to reset settings in OCMN, USR3,

UGR1 tables?

I've find that the trouble makes invalid value in OCMN table. There is Father=NULL instead of expected Father=N''. I've changed the value to N'' and now it's ok.

Tom


Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Tomas,

Unfortunately I don't know how to manipulate OCMN. About OUGR, USR3 and OUPT, you can manipulate by client SAP B1 and DI API. About reset information, I don't know if it is possible.

Maybe someone else in this board have a better information.

Kind Regards,

Diego Lother

0