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
            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
            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
            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?
            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.
            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
            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.
            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
            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.
            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.
            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.

              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: