Skip to Content
avatar image
Former Member

Add popup menu item - very poor performance

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Nov 30, 2016 at 06:53 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 01, 2016 at 03:35 PM

    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
    
    Add comment
    10|10000 characters needed 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

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

    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

    Add comment
    10|10000 characters needed 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