Project SimpleProject Type: software

Project Simple

Uploaded image for project: 'Project Simple'
  1. Project Simple
  2. ST-176

Query performance - GetSearchCompanyDetails()

    Details

    • Type: Enhancement
    • Status: Production Complete
    • Priority: High
    • Resolution: Done
    • Component/s: Performance
    • Labels:
      None
    • Module:
      Platform - Performance
    • Reported by:
      Support
    • Item State:
      Production Complete - Closed

      Description

        Attachments

          Issue Links

          relates to

          Enhancement - An improvement or enhancement to an existing feature or task. ST-175 Query Optimization for performance

          • High - Serious problem that could block progress.
          • New Request

          Enhancement - An improvement or enhancement to an existing feature or task. ST-240 Query performance - Search Company Screen taking time.

          • High - Serious problem that could block progress.
          • Closed

            Activity

            Ascending order - Click to sort in descending order
            saurabh.sablaka Saurabh Sablaka (Inactive) created issue -
            saurabh.sablaka Saurabh Sablaka (Inactive) made changes -
            Field Original Value New Value
            Link This issue relates to ST-175 [ ST-175 ]
            prasanna Prasanna Karlekar (Inactive) made changes -
            Assignee Prasanna Karlekar [ prasanna ]
            prasanna Prasanna Karlekar (Inactive) made changes -
            Description Call stack:
            WORKTERRA.CollectionManager.GetSearchCompanyDetails(:0)
            WORKTERRA.CollectionManager.GetCollection(:0)
            WORKTERRA.CollectionManager.GetCollection(:0)
            WORKTERRA.Shared.WORKTERRAControllerAppTier.GetCollection(:0)
            		 Call stack:
            WORKTERRA.CollectionManager.GetSearchCompanyDetails(:0)
            WORKTERRA.CollectionManager.GetCollection(:0)
            WORKTERRA.CollectionManager.GetCollection(:0)
            WORKTERRA.Shared.WORKTERRAControllerAppTier.GetCollection(:0)


            *FYI*
            https://rpm.newrelic.com/accounts/1155082/applications/13257194_h18260974/transactions#id=5b225765625472616e73616374696f6e2f5743462f574f524b54455252412e49436f6e74726f6c6c6572417070546965722e476574436f6c6c656374696f6e222c22225d&app_trace_id=116b603-8cbf50ff-305e-11e6-a1f2-f8bc12425d50&tab-detail_116b603-8cbf50ff-305e-11e6-a1f2-f8bc12425d50=database_queries
            prasanna Prasanna Karlekar (Inactive) made changes -
            Description Call stack:
            WORKTERRA.CollectionManager.GetSearchCompanyDetails(:0)
            WORKTERRA.CollectionManager.GetCollection(:0)
            WORKTERRA.CollectionManager.GetCollection(:0)
            WORKTERRA.Shared.WORKTERRAControllerAppTier.GetCollection(:0)


            *FYI*
            https://rpm.newrelic.com/accounts/1155082/applications/13257194_h18260974/transactions#id=5b225765625472616e73616374696f6e2f5743462f574f524b54455252412e49436f6e74726f6c6c6572417070546965722e476574436f6c6c656374696f6e222c22225d&app_trace_id=116b603-8cbf50ff-305e-11e6-a1f2-f8bc12425d50&tab-detail_116b603-8cbf50ff-305e-11e6-a1f2-f8bc12425d50=database_queries
            		 *Call stack*
            WORKTERRA.CollectionManager.GetSearchCompanyDetails(:0)
            WORKTERRA.CollectionManager.GetCollection(:0)
            WORKTERRA.CollectionManager.GetCollection(:0)
            WORKTERRA.Shared.WORKTERRAControllerAppTier.GetCollection(:0)


            *FYI*
            https://rpm.newrelic.com/accounts/1155082/applications/13257194_h18260974/transactions#id=5b225765625472616e73616374696f6e2f5743462f574f524b54455252412e49436f6e74726f6c6c6572417070546965722e476574436f6c6c656374696f6e222c22225d&app_trace_id=116b603-8cbf50ff-305e-11e6-a1f2-f8bc12425d50&tab-detail_116b603-8cbf50ff-305e-11e6-a1f2-f8bc12425d50=database_queries
            Hide
            Permalink
            prasanna Prasanna Karlekar (Inactive) added a comment - - edited

            Suhas Joshi,Saurabh Sablaka,Samir,Vikas Pawar

            Concern:
            GetSearchCompanyDetails() routine taking time.
            As per New Relic logs traced during load test, for 1 call count the query took 42 seconds execute.
            Locally it took 41 seconds and 11 seconds at different times.

            Cause:

            • GetSearchCompanyDetails business logic code has query to fetch HomePage Counters and Company Specific Image and Default Image.
            • Image related data resides on respective Company Database. There is cursor to scan all Active companies and has EXEC() to fetch image details from each company database. This is what is taking the time.

            Correction:
            Fetching of Image related information can be avoided, as we do not show company image for Search Company Widget on Home Page.
            We will keep the code block to fetch Image data conditional.
            Following is the approach:

            • If company data is going to get populated for list view, image data will not be fetched.
            • If company data is going to get populated for grid view, where we need to show Company Logo, image data will be fetched.
              After doing these changes, query executes within a second on local machine. We need to track this once again during load test.

            Files Changed:

            • WT\trunk\WORKTERRAweb\App\BusinessLogic\CollectionManager\CollectionManager.vb
            • WT\trunk\WORKTERRAweb\Web\SharedFunctionWebTier\SharedFunctionWebTier\Models\WTHomePage\
              WORKTERRAShared\SearchCompanyPVModel.cs
            • WT\trunk\WORKTERRAweb\Web\WebProjects\WORKTERRA\Controllers\HomePage\SearchCompany\
              SearchCompanyController.cs
            • WT\trunk\WORKTERRAweb\Web\Web Projects\WORKTERRA\Models\HomePage\SearchCompany\CompanyModel.cs

            Impacted Areas:

            • Search Company Widget on SA / Partner / Broker/ Company Admin Home Page.
            • Search Company screen via Menu / Quick link
            Show
            prasanna Prasanna Karlekar (Inactive) added a comment - - edited Suhas Joshi , Saurabh Sablaka , Samir , Vikas Pawar Concern: GetSearchCompanyDetails() routine taking time. As per New Relic logs traced during load test, for 1 call count the query took 42 seconds execute. Locally it took 41 seconds and 11 seconds at different times. Cause: GetSearchCompanyDetails business logic code has query to fetch HomePage Counters and Company Specific Image and Default Image. Image related data resides on respective Company Database. There is cursor to scan all Active companies and has EXEC() to fetch image details from each company database. This is what is taking the time. Correction: Fetching of Image related information can be avoided, as we do not show company image for Search Company Widget on Home Page. We will keep the code block to fetch Image data conditional. Following is the approach: If company data is going to get populated for list view, image data will not be fetched. If company data is going to get populated for grid view, where we need to show Company Logo, image data will be fetched. After doing these changes, query executes within a second on local machine. We need to track this once again during load test. Files Changed: WT\trunk\WORKTERRAweb\App\BusinessLogic\CollectionManager\CollectionManager.vb WT\trunk\WORKTERRAweb\Web\SharedFunctionWebTier\SharedFunctionWebTier\Models\WTHomePage\ WORKTERRAShared\SearchCompanyPVModel.cs WT\trunk\WORKTERRAweb\Web\WebProjects\WORKTERRA\Controllers\HomePage\SearchCompany\ SearchCompanyController.cs WT\trunk\WORKTERRAweb\Web\Web Projects\WORKTERRA\Models\HomePage\SearchCompany\CompanyModel.cs Impacted Areas: Search Company Widget on SA / Partner / Broker/ Company Admin Home Page. Search Company screen via Menu / Quick link
            prasanna Prasanna Karlekar (Inactive) made changes -
            Assignee Prasanna Karlekar [ prasanna ] Rakesh Roy [ rakeshr ]
            prasanna Prasanna Karlekar (Inactive) made changes -
            Item State Parent values: Development(10200)Level 1 values: Development Backlog(10205) Parent values: LB QA(10201)Level 1 values: LB Deployed(11600)
            rakeshr Rakesh Roy (Inactive) made changes -
            Status New Request [ 10029 ] Pending for Approval [ 10002 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Status Pending for Approval [ 10002 ] Approved for Development [ 10003 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Status Approved for Development [ 10003 ] In Development [ 10007 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Status In Development [ 10007 ] Local Testing [ 10200 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Assignee Rakesh Roy [ rakeshr ] Deepali Tidke [ deepalit ]
            Hide
            Permalink
            deepalit Deepali Tidke (Inactive) added a comment -

            Kindly check home pages for every login specially company and EE search

            Show
            deepalit Deepali Tidke (Inactive) added a comment - Kindly check home pages for every login specially company and EE search
            deepalit Deepali Tidke (Inactive) made changes -
            Assignee Deepali Tidke [ deepalit ] Kunal Kedari [ kunal.kedari ]
            kunal.kedari Kunal Kedari (Inactive) made changes -
            Assignee Kunal Kedari [ kunal.kedari ] Venkatesh Pujari [ venkatesh.pujari ]
            Hide
            Permalink
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment -

            Tested this on LB through SA,Partner,Broker and Admin log in. Search company widget working fine with no issues.
            Also tested this via Menu and Quick links

            Ready for Stage

            Show
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment - Tested this on LB through SA,Partner,Broker and Admin log in. Search company widget working fine with no issues. Also tested this via Menu and Quick links Ready for Stage
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Item State Parent values: LB QA(10201)Level 1 values: LB Deployed(11600) Parent values: LB QA(10201)Level 1 values: Ready for Stage(10213)
            rakeshr Rakesh Roy (Inactive) made changes -
            Developer Saurabh Sablaka [ saurabh.sablaka ]
            Stage Due Date 22/Jun/16 [ 2016-06-22 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Developer Saurabh Sablaka [ saurabh.sablaka ] Prasanna Karlekar [ prasanna ]
            Hide
            Permalink
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment -

            Tested this on Stage through SA,Partner,Broker and Admin log in on hspl company.
            Search company widget working fine with no issues.
            Also tested this via Menu and Quick links

            Ready for Production

            Show
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment - Tested this on Stage through SA,Partner,Broker and Admin log in on hspl company. Search company widget working fine with no issues. Also tested this via Menu and Quick links Ready for Production
            rakeshr Rakesh Roy (Inactive) made changes -
            Item State Parent values: LB QA(10201)Level 1 values: Ready for Stage(10213) Parent values: Stage QA(10202)Level 1 values: Ready for Production(10217)
            rakeshr Rakesh Roy (Inactive) made changes -
            Status Local Testing [ 10200 ] Pending for Stage Approval [ 10300 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Status Pending for Stage Approval [ 10300 ] Approved for Stage [ 10030 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Status Approved for Stage [ 10030 ] Stage Testing [ 10201 ]
            Hide
            Permalink
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment - - edited

            Tested this on Production through SA,Partner,Broker and Admin log in on hspl company.

            Search company widget working fine with no issues.

            Also tested this via Menu and Quick links

            Show
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment - - edited Tested this on Production through SA,Partner,Broker and Admin log in on hspl company. Search company widget working fine with no issues. Also tested this via Menu and Quick links
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Item State Parent values: Stage QA(10202)Level 1 values: Ready for Production(10217) Parent values: Production Complete(10222)Level 1 values: Closed(10223)
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Status Stage Testing [ 10201 ] Pending for Production Approval [ 10301 ]
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Status Pending for Production Approval [ 10301 ] Approved for production [ 10034 ]
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Status Approved for production [ 10034 ] Production Testing [ 10202 ]
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Resolution Done [ 10000 ]
            Status Production Testing [ 10202 ] Production Complete [ 10028 ]
            prasanna Prasanna Karlekar (Inactive) made changes -
            Link This issue relates to ST-240 [ ST-240 ]
            Transition Time In Source Status Execution Times
            Rakesh Roy (Inactive) made transition -
            New Request Pending for Approval
            		23d 6h 23m 1
            Rakesh Roy (Inactive) made transition -
            Pending for Approval Approved for Development
            		2s 1
            Rakesh Roy (Inactive) made transition -
            Approved for Development In Development
            		2s 1
            Rakesh Roy (Inactive) made transition -
            In Development In LB Testing
            		2s 1
            Rakesh Roy (Inactive) made transition -
            In LB Testing Pending for Stage Approval
            		7d 11h 41m 1
            Rakesh Roy (Inactive) made transition -
            Pending for Stage Approval Approved for Stage
            		2s 1
            Rakesh Roy (Inactive) made transition -
            Approved for Stage Stage Testing
            		2s 1
            Venkatesh Pujari (Inactive) made transition -
            Stage Testing Pending for Production Approval
            		7h 23m 1
            Venkatesh Pujari (Inactive) made transition -
            Pending for Production Approval Approved for production
            		2s 1
            Venkatesh Pujari (Inactive) made transition -
            Approved for production In Production Testing
            		2s 1
            Venkatesh Pujari (Inactive) made transition -
            In Production Testing Production Complete
            		7s 1

              People

              Assignee:
              venkatesh.pujari Venkatesh Pujari (Inactive)
              Reporter:
              saurabh.sablaka Saurabh Sablaka (Inactive)
              Developer:
              Prasanna Karlekar (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Pre-Prod Due Date: