Uploaded image for project: 'CFA MX '
  1. CFA MX
  2. CFAMX-10076

N hibernate Query Hits DB too many times unnecessarily

    Details

      Description

      A simple NHibernate query in orderings for a slowness issue. Here is the Nhibernate query :
       

      InventoryEntityItem entityItem = null;            var results = GetQuery()
                      .JoinAlias(x => x.InventoryEntityItem, () => entityItem, JoinType.InnerJoin)
                      .Where(x => x.EntityId == entityId)
                      .Where(x => x.VendorID == vendorId)
                      .List()
                      .Where(x => IsActiveInDateRange(x.IntroductionDate, x.DiscontinueDate, x.InventoryEntityItem.InUse, localTime));
      

      this query takes really long to get results. And after further investigation we have used NHibernate profiler, It looks like under the cover it makes 2792 calls to the DB.
       

       
       
      and looks like all the references in the mapping files getting calls for per record and every ref has sub ref too. After adding some lazy loads to the tbEntityItem and tbVendorEntityItem mapping file's refs and we saw the number of calls is reduced. That does not mean we should always use LazyLoads because I am not too sure what the impact will be. (edited) 
       
      looking to a single repo only VendorEntityItemRepository and seeing the same NHibernate joins everywhere.
       
      actions can be taken. 
      1) Identify the areas in MxC have the same nhibernate issue. 
      As part of this feature, we should search for the mapping files in mobile and looks at the references.  And investigate the nhibernate query outputs with nhibernate profiler. 
       
      2) Set MaximumFetchDepth value to 2 instead of 3. 
      In our application, NHibernate Fetch Mode is set to eager by default. We may have a huge gain after making this change. we shall monitor the same API call if it gets reduced any after this change. 
       
      Here is the API call simple tested. 
      Api/Entity/875/Vendors/GetVendorEntityItemsByVendorByEntity?vendorId=11
       
       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                caner.saritac Caner Saritac
                Reporter:
                caner.saritac Caner Saritac
              • Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  PagerDuty

                  Error rendering 'com.pagerduty.jira-server-plugin:PagerDuty'. Please contact your Jira administrators.