Project SimpleProject Type: software

Project Simple

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

Query Performance -Bulk Insert AddChangeTermReport

    Details

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

      Description

      Query Details
      BULK INSERT AddChangeTermReport FROM ? WITH ( FORMATFILE =?)

      Call Stack
      WORKTERRA.AddChangeTermReport.UpdateDatabase(:0)
      WORKTERRA.AddChangeTermReport.UpdateDatabase(:0)
      WORKTERRA.AddChangeTermReport.CreateDataForAddChangeTermReport(:0)
      WORKTERRA.AddChangeTermReport.Save(:0)
      WORKTERRA.AddChangeTermReport.SaveEnrollmentDataForConfirmationStatement(:0)
      WORKTERRA.BenAdmin.BenAdminControllerAppTier.SaveAndGetData(:0)
      WORKTERRA.BenAdmin.BenAdminControllerAppTier.SaveAndGetData(:0)
      WORKTERRA.ControllerAppTier.SaveAndGetData(:0)

      for more info

      https://rpm.newrelic.com/accounts/1155082/applications/13257194_h18260974/transactions#id=5b225765625472616e73616374696f6e2f5743462f574f524b54455252412e49436f6e74726f6c6c6572417070546965722e53617665416e6447657444617461222c22225d&tab-detail_116b603-295bb804-2536-11e6-a1f2-f8bc12425d50=database_queries&app_trace_id=116b603-295bb804-2536-11e6-a1f2-f8bc12425d50

        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

            Activity

            Ascending order - Click to sort in descending order
            vikas.pawar Vikas Pawar (Inactive) created issue -
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Field Original Value New Value
            Link This issue relates to ST-175 [ ST-175 ]
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Assignee Saurabh Sablaka [ saurabh.sablaka ]
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Description Query Details BULK INSERT AddChangeTermReport FROM ? WITH ( FORMATFILE =?)

            Call Stack
            WORKTERRA.AddChangeTermReport.UpdateDatabase(:0)
            WORKTERRA.AddChangeTermReport.UpdateDatabase(:0)
            WORKTERRA.AddChangeTermReport.CreateDataForAddChangeTermReport(:0)
            WORKTERRA.AddChangeTermReport.Save(:0)
            WORKTERRA.AddChangeTermReport.SaveEnrollmentDataForConfirmationStatement(:0)
            WORKTERRA.BenAdmin.BenAdminControllerAppTier.SaveAndGetData(:0)
            WORKTERRA.BenAdmin.BenAdminControllerAppTier.SaveAndGetData(:0)
            WORKTERRA.ControllerAppTier.SaveAndGetData(:0)


            for more info

            [https://rpm.newrelic.com/accounts/1155082/applications/13257194_h18260974/transactions#id=5b225765625472616e73616374696f6e2f5743462f574f524b54455252412e49436f6e74726f6c6c6572417070546965722e53617665416e6447657444617461222c22225d&tab-detail_116b603-295bb804-2536-11e6-a1f2-f8bc12425d50=database_queries&app_trace_id=116b603-295bb804-2536-11e6-a1f2-f8bc12425d50]             		*Query Details*
             BULK INSERT AddChangeTermReport FROM ? WITH ( FORMATFILE =?)

            *Call Stack*
            WORKTERRA.AddChangeTermReport.UpdateDatabase(:0)
            WORKTERRA.AddChangeTermReport.UpdateDatabase(:0)
            WORKTERRA.AddChangeTermReport.CreateDataForAddChangeTermReport(:0)
            WORKTERRA.AddChangeTermReport.Save(:0)
            WORKTERRA.AddChangeTermReport.SaveEnrollmentDataForConfirmationStatement(:0)
            WORKTERRA.BenAdmin.BenAdminControllerAppTier.SaveAndGetData(:0)
            WORKTERRA.BenAdmin.BenAdminControllerAppTier.SaveAndGetData(:0)
            WORKTERRA.ControllerAppTier.SaveAndGetData(:0)


            for more info

            [https://rpm.newrelic.com/accounts/1155082/applications/13257194_h18260974/transactions#id=5b225765625472616e73616374696f6e2f5743462f574f524b54455252412e49436f6e74726f6c6c6572417070546965722e53617665416e6447657444617461222c22225d&tab-detail_116b603-295bb804-2536-11e6-a1f2-f8bc12425d50=database_queries&app_trace_id=116b603-295bb804-2536-11e6-a1f2-f8bc12425d50]
            Hide
            Permalink
            vikas.pawar Vikas Pawar (Inactive) added a comment -

            Added Batch size of 1000 in the bulk insert query and calculated the time taken by the query before and after change

            For 45582 insert records, Previous time was near about 14 seconds and with batch size of 1000 its 10-11 seconds

            Show
            vikas.pawar Vikas Pawar (Inactive) added a comment - Added Batch size of 1000 in the bulk insert query and calculated the time taken by the query before and after change For 45582 insert records, Previous time was near about 14 seconds and with batch size of 1000 its 10-11 seconds
            Hide
            Permalink
            vikas.pawar Vikas Pawar (Inactive) added a comment -

            Modified the code and committed to performance environment

            Show
            vikas.pawar Vikas Pawar (Inactive) added a comment - Modified the code and committed to performance environment
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Assignee Saurabh Sablaka [ saurabh.sablaka ] Vikas Pawar [ vikas.pawar ]
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Status New Request [ 10029 ] Pending for Approval [ 10002 ]
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Status Pending for Approval [ 10002 ] Approved for Development [ 10003 ]
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Status Approved for Development [ 10003 ] In Development [ 10007 ]
            Hide
            Permalink
            vikas.pawar Vikas Pawar (Inactive) added a comment - - edited

            Committed the changes on LB. assgning to Rakesh Roy

            Show
            vikas.pawar Vikas Pawar (Inactive) added a comment - - edited Committed the changes on LB. assgning to Rakesh Roy
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Item State Parent values: LB QA(10201)
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Assignee Vikas Pawar [ vikas.pawar ] Rakesh Roy [ rakeshr ]
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Status In Development [ 10007 ] Local Testing [ 10200 ]
            Hide
            Permalink
            rakeshr Rakesh Roy (Inactive) added a comment -

            Is this deployed on LB?

            Show
            rakeshr Rakesh Roy (Inactive) added a comment - Is this deployed on LB?
            rakeshr Rakesh Roy (Inactive) made changes -
            Assignee Rakesh Roy [ rakeshr ] Vikas Pawar [ vikas.pawar ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Status Local Testing [ 10200 ] Reopen in Local [ 10018 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Status Reopen in Local [ 10018 ] In Development [ 10007 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Item State Parent values: LB QA(10201) Parent values: Development(10200)Level 1 values: Ready for Local Testing(10209)
            Hide
            Permalink
            vikas.pawar Vikas Pawar (Inactive) added a comment -

            Yes its already deployed.

            Show
            vikas.pawar Vikas Pawar (Inactive) added a comment - Yes its already deployed.
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Assignee Vikas Pawar [ vikas.pawar ] Rakesh Roy [ rakeshr ]
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Status In Development [ 10007 ] Local Testing [ 10200 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Assignee Rakesh Roy [ rakeshr ] Deepali Tidke [ deepalit ]
            deepalit Deepali Tidke (Inactive) made changes -
            Assignee Deepali Tidke [ deepalit ] Venkatesh Pujari [ venkatesh.pujari ]
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Priority Medium [ 3 ] Highest [ 1 ]
            Hide
            Permalink
            vikas.pawar Vikas Pawar (Inactive) added a comment -

            Impacted Area's

            • Confirmation Statement (only when Last Recent Activity is selected for Confirmation Statement)
            • View History including Enrollment Details
            • Add Change Term Report - Company Level
            Show
            vikas.pawar Vikas Pawar (Inactive) added a comment - Impacted Area's Confirmation Statement (only when Last Recent Activity is selected for Confirmation Statement) View History including Enrollment Details Add Change Term Report - Company Level
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Status Local Testing [ 10200 ] Reopen in Local [ 10018 ]
            Hide
            Permalink
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment -

            Hi Vikas,

            Tested this on LB Austin Company.

            For bulk Add Change Term Report Server Error is being displayed. Please find the below error log:-

            ErrorID : 0
            ErrorSource : ControllerAppTier.SaveAndGetData->BenAdminControllerAppTier.SaveAndGetData->StaticReports.SaveAndGetData->AddChangeTermReport.Save->AddChangeTermReport.CreateDataForAddChangeTermReport->AddChangeTermReport.UpdateDatabase
            ErrorMessage: Cannot bulk load because the file "\\10.0.2.160\WORKterra\Reports\BulkInsert_isfk40qre244z3pbef05u22x.txt" could not be opened. Operating system error code 1326(Logon failure: unknown user name or bad password.).

            Thanks,
            Venkatesh.

            Show
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment - Hi Vikas, Tested this on LB Austin Company. For bulk Add Change Term Report Server Error is being displayed. Please find the below error log:- ErrorID : 0 ErrorSource : ControllerAppTier.SaveAndGetData->BenAdminControllerAppTier.SaveAndGetData->StaticReports.SaveAndGetData->AddChangeTermReport.Save->AddChangeTermReport.CreateDataForAddChangeTermReport->AddChangeTermReport.UpdateDatabase ErrorMessage: Cannot bulk load because the file "\\10.0.2.160\WORKterra\Reports\BulkInsert_isfk40qre244z3pbef05u22x.txt" could not be opened. Operating system error code 1326(Logon failure: unknown user name or bad password.). Thanks, Venkatesh.
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Assignee Venkatesh Pujari [ venkatesh.pujari ] Vikas Pawar [ vikas.pawar ]
            Hide
            Permalink
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment -

            Hi Vikas,

            This is a generic issue so please ignore and this ticket is ready for stage.

            Thanks,
            Venkatesh

            Show
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment - Hi Vikas, This is a generic issue so please ignore and this ticket is ready for stage. Thanks, Venkatesh
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Item State Parent values: Development(10200)Level 1 values: Ready for Local Testing(10209) Parent values: LB QA(10201)Level 1 values: Ready for Stage(10213)
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Summary Query Performance -AddChangeTermReport Query Performance -Bulk Insert AddChangeTermReport
            rakeshr Rakesh Roy (Inactive) made changes -
            Developer Vikas Pawar [ vikas.pawar ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Status Reopen in Local [ 10018 ] In Development [ 10007 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Status In Development [ 10007 ] Local Testing [ 10200 ]
            ashwin.wankhede Ashwin Wankhede (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: Stage Deployed(11602)
            vikas.pawar Vikas Pawar (Inactive) made changes -
            Assignee Vikas Pawar [ vikas.pawar ] Venkatesh Pujari [ venkatesh.pujari ]
            Hide
            Permalink
            vikas.pawar Vikas Pawar (Inactive) added a comment -

            Hi Venkatesh Pujari,
            This patch is moved on stage in Stage build. Please test this on stage environment.

            Show
            vikas.pawar Vikas Pawar (Inactive) added a comment - Hi Venkatesh Pujari , This patch is moved on stage in Stage build. Please test this on stage environment.
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Status Local Testing [ 10200 ] Pending for Stage Approval [ 10300 ]
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Status Pending for Stage Approval [ 10300 ] Approved for Stage [ 10030 ]
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Status Approved for Stage [ 10030 ] Stage Testing [ 10201 ]
            Hide
            Permalink
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment -

            Tested this on Stage on City of Denton for hspl.

            Reports working fine with last recent activity also.

            Ready for Production.

            Show
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment - Tested this on Stage on City of Denton for hspl. Reports working fine with last recent activity also. Ready for Production.
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Item State Parent values: Stage QA(10202)Level 1 values: Stage Deployed(11602) Parent values: Stage QA(10202)Level 1 values: Ready for Production(10217)
            ashwin.wankhede Ashwin Wankhede (Inactive) made changes -
            Item State Parent values: Stage QA(10202)Level 1 values: Ready for Production(10217) Parent values: Production QA(10203)Level 1 values: Production Deployed(10221)
            rakeshr Rakesh Roy (Inactive) made changes -
            Status Stage Testing [ 10201 ] Pending for Production Approval [ 10301 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Status Pending for Production Approval [ 10301 ] Approved for production [ 10034 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Status Approved for production [ 10034 ] Production Testing [ 10202 ]
            Hide
            Permalink
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment -

            Tested this on Production on MHNGS For HSPL company.

            Reports working fine with last recent activity also.

            Show
            venkatesh.pujari Venkatesh Pujari (Inactive) added a comment - Tested this on Production on MHNGS For HSPL company. Reports working fine with last recent activity also.
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Resolution Done [ 10000 ]
            Status Production Testing [ 10202 ] Production Complete [ 10028 ]
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Item State Parent values: Production QA(10203)Level 1 values: Production Deployed(10221) Parent values: Production Complete(10222)Level 1 values: Closed(10223)
            venkatesh.pujari Venkatesh Pujari (Inactive) made changes -
            Resolution Done [ 10000 ] Fixed [ 1 ]
            Status Production Complete [ 10028 ] Closed [ 6 ]
            Transition Time In Source Status Execution Times
            Vikas Pawar (Inactive) made transition -
            New Request Pending for Approval
            		29d 3h 51m 1
            Vikas Pawar (Inactive) made transition -
            Pending for Approval Approved for Development
            		3s 1
            Vikas Pawar (Inactive) made transition -
            Approved for Development In Development
            		2s 1
            Venkatesh Pujari (Inactive) made transition -
            In LB Testing Reopen in Local
            		3d 2h 32m 2
            Rakesh Roy (Inactive) made transition -
            Reopen in Local In Development
            		4d 20h 39m 2
            Rakesh Roy (Inactive) made transition -
            In Development In LB Testing
            		4d 15h 52m 3
            Venkatesh Pujari (Inactive) made transition -
            In LB Testing Pending for Stage Approval
            		14d 8h 20m 1
            Venkatesh Pujari (Inactive) made transition -
            Pending for Stage Approval Approved for Stage
            		2s 1
            Venkatesh Pujari (Inactive) made transition -
            Approved for Stage Stage Testing
            		2s 1
            Rakesh Roy (Inactive) made transition -
            Stage Testing Pending for Production Approval
            		10d 18h 33m 1
            Rakesh Roy (Inactive) made transition -
            Pending for Production Approval Approved for production
            		2s 1
            Rakesh Roy (Inactive) made transition -
            Approved for production In Production Testing
            		2s 1
            Venkatesh Pujari (Inactive) made transition -
            In Production Testing Production Complete
            		1d 55m 1
            Venkatesh Pujari (Inactive) made transition -
            Production Complete Closed
            		21s 1

              People

              Assignee:
              venkatesh.pujari Venkatesh Pujari (Inactive)
              Reporter:
              vikas.pawar Vikas Pawar (Inactive)
              Developer:
              Vikas Pawar (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: