4 Replies Latest reply on Jun 18, 2015 8:11 AM by steph4n

    Additional Columns in System Tree causes long loading times since ePO 5.3

    steph4n

      Hello everyone,

      I hope this is the right place and way for asking this.

       

      I recently upgraded our ePO Server from 5.1.1 to 5.3.

      I immediately noticed very long loading times while navigating in the system tree. Listing all systems (475) at the top level (My Organization) takes about 15 seconds, the same goes for changing the sorting or searching a system. Before the upgrade the loading time rarely went longer than 1 second. After trying to optimize thy system by purging logs and cleaning the repository, I found out at the end that the additional columns I have activated are responsible for that behavior. Without the only two I added (Product Version (Agent) and Product Version (VirusScan Enterprise)) everything is quick as before, although i had them in there since 4.6.

      I contacted the McAfee support, but they basically told me that they aren't gonna help me, as long as the server doesn't meet the hardware requirements. I explained in great extend why either the CPU, the memory or free hard drive space can be the cause of this, but they ignored it.

       

      So, does anyone else encountered this behavior or may try to reproduce it with those additional columns?

        • 1. Re: Additional Columns in System Tree causes long loading times since ePO 5.3
          fitchsoccer342

          I'm trying to understand this, so you are saying that when adding the MA version & VSE version column, it takes an additional 15 seconds or so to load?

           

          Keep in mind, the more columns for various modules/pieces in ePO that you try to display in the gui, it has to query from various tables in the DB. Purging logs and cleaning the repository can help, but mainly, displaying things in the front end depend on your DB being able to quickly retrieve the data and display it. I am not advocating that your DB is not properly maintained, but I would look to make sure you are re-indexing, backing up, purging, etc.

           

          Now, I am not running 5.3 (5.1.2), but seeing that you only have 475 machines, it should not be taking that long to pull that. See screenshot, I have a good amount of columns and returned 50k+ machines from a group in the system tree and took about 10 seconds. However, I know 100% that our DB backend is designed for SQL efficiency and properly maintained.

           

          What I'm getting at is that either there is a fundamental issue with 5.3 in presenting the data from the DB or there is some issue in the retrieval process from your DB. Take a look at the attached, I put this together a while ago for getting the most out of your ePO DB.. might be a couple things in there to help you with.

           

          To really troubleshoot, you could recreate the query in queries & reports, view the SQL, then run it on the backend and see the query retrieval times and modify from there.

          • 2. Re: Additional Columns in System Tree causes long loading times since ePO 5.3
            steph4n

            Thanks for the response.

            fitchsoccer342 wrote:

             

            I'm trying to understand this, so you are saying that when adding the MA version & VSE version column, it takes an additional 15 seconds or so to load?

            Correct

             

            fitchsoccer342 wrote:

             

            Keep in mind, the more columns for various modules/pieces in ePO that you try to display in the gui, it has to query from various tables in the DB. Purging logs and cleaning the repository can help, but mainly, displaying things in the front end depend on your DB being able to quickly retrieve the data and display it. I am not advocating that your DB is not properly maintained, but I would look to make sure you are re-indexing, backing up, purging, etc.

            I get that, but I haven't had any issues before 5.3 when it comes to performance and, as you said yourself, it's only 475 computers. Also, I am running the build in SQL Express, how can I do those tasks without the SA login?

            • 3. Re: Re: Additional Columns in System Tree causes long loading times since ePO 5.3
              fitchsoccer342

              If your running SQL express you'll probably need to download SSMS (if you havent, its free, sql server management studio). It will provide the ability to access the ePO DB and be able to query, setup tasks, etc.

               

              If you already have it installed, it depends on how your admins set it up. If you can open it but cant login, have them add your windows logon within SSMS. You don't need the SA login, and if you are using SA as the un for the ePO front end to connect to the DB, you should change it, one of the first things hackers look for is default logons. Once in SSMS, you can query the DB.

               

              But yeah, 475 machines is nothing and should pull instantly.. I'm just providing some basic troubleshooting sequences I would run through before engaging support. Attached is the DB doc I forgot to attach in original reply.

              • 4. Re: Re: Re: Additional Columns in System Tree causes long loading times since ePO 5.3
                steph4n

                I already had SSMS installed, but was trying to login with the wrong user. It now works. When you say "our admins", it's just me , but I'm not that familiar with SQL tasks.

                I managed to to isolate a query, while working with the ePO front end. Changing the sorting took 13 seconds, running the query separately in SSMS took 7 seconds:

                SELECT * FROM (select [EPOComputerProperties].[ComputerName] as col1,  [EPOLeafNode].[ManagedState] as col2,  [EPOComputerProperties].[IPV6] as col3,  [EPOComputerProperties].[UserName] as col4,  [EPOLeafNode].[LastUpdate] as col5,  [EPOProdPropsView_EPOAGENT].[productversion] as col6,  [EPOProdPropsView_VIRUSCAN].[productversion] as col7,  [EPOProdPropsView_MCAFEE_EEPC].[productversion] as col8,  [EPOLeafNode].[AutoID] as col9 ,ROW_NUMBER()  OVER(  order by [EPOComputerProperties].[ComputerName] asc ) AS RowNum  from [EPOLeafNode] left join [EPOProdPropsView_EPOAGENT] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_EPOAGENT].[LeafNodeID] left join [EPOProdPropsView_VIRUSCAN] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID] left join [EPOComputerProperties] on [EPOLeafNode].[AutoID] = [EPOComputerProperties].[ParentID] left join [EPOProdPropsView_MCAFEE_EEPC] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_MCAFEE_EEPC].[LeafNodeID]  where  ( [EPOLeafNode].[ParentID] IN (SELECT [EndAutoID] FROM [EPOBranchNodeEnum] WHERE [StartAutoID] = 2  UNION SELECT 2 )) ) AS RowConstrainedResult WHERE RowNum >=1 AND RowNum <54 ORDER BY RowNum
                

                 

                The document you attached and the McAfee KB-Article linked there looks very useful if you don't have SQL Express, because of the lack of maintenance plans. There doesn't seem to exist build in tool for maintenance, Microsoft only explains how to rebuild an index for a single table and I would have a bad feeling using some SQL scripts from 3rd party sites without completely knowing what they do, and I'm not confident enough building something like that myself.