[ST-205] Query Performance -Bulk Insert AddChangeTermReport - Workterra Jira

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

ST-175 Query Optimization for performance

New Request

Activity

Ascending order - Click to sort in descending order

Vikas Pawar (Inactive) created issue - 01/Jun/16 07:11 AM

Vikas Pawar (Inactive) made changes - 01/Jun/16 07:12 AM

Field	Original Value	New Value
Link		This issue relates to ST-175 [ ST-175 ]

Vikas Pawar (Inactive) made changes - 01/Jun/16 07:12 AM

Assignee

Saurabh Sablaka [ saurabh.sablaka ]

Vikas Pawar (Inactive) made changes - 01/Jun/16 07:13 AM

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 (Inactive) added a comment - 30/Jun/16 08:35 AM

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 (Inactive) added a comment - 30/Jun/16 08:35 AM 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 (Inactive) added a comment - 30/Jun/16 08:36 AM

Modified the code and committed to performance environment

Show

Vikas Pawar (Inactive) added a comment - 30/Jun/16 08:36 AM Modified the code and committed to performance environment

Vikas Pawar (Inactive) made changes - 30/Jun/16 08:36 AM

Assignee

Saurabh Sablaka [ saurabh.sablaka ]

Vikas Pawar [ vikas.pawar ]

Vikas Pawar (Inactive) made changes - 30/Jun/16 11:03 AM

Status

New Request [ 10029 ]

Pending for Approval [ 10002 ]

Vikas Pawar (Inactive) made changes - 30/Jun/16 11:03 AM

Status

Pending for Approval [ 10002 ]

Approved for Development [ 10003 ]

Vikas Pawar (Inactive) made changes - 30/Jun/16 11:03 AM

Status

Approved for Development [ 10003 ]

In Development [ 10007 ]

Hide

Permalink

Vikas Pawar (Inactive) added a comment - 04/Jul/16 12:15 PM - edited

Committed the changes on LB. assgning to Rakesh Roy

Show

Vikas Pawar (Inactive) added a comment - 04/Jul/16 12:15 PM - edited Committed the changes on LB. assgning to Rakesh Roy

Vikas Pawar (Inactive) made changes - 04/Jul/16 12:16 PM

Item State

Parent values: LB QA(10201)

Vikas Pawar (Inactive) made changes - 04/Jul/16 12:16 PM

Assignee

Vikas Pawar [ vikas.pawar ]

Rakesh Roy [ rakeshr ]

Vikas Pawar (Inactive) made changes - 04/Jul/16 12:16 PM

Status

In Development [ 10007 ]

Local Testing [ 10200 ]

Hide

Permalink

Rakesh Roy (Inactive) added a comment - 04/Jul/16 12:27 PM

Is this deployed on LB?

Show

Rakesh Roy (Inactive) added a comment - 04/Jul/16 12:27 PM Is this deployed on LB?

Rakesh Roy (Inactive) made changes - 04/Jul/16 12:27 PM

Assignee

Rakesh Roy [ rakeshr ]

Vikas Pawar [ vikas.pawar ]

Rakesh Roy (Inactive) made changes - 04/Jul/16 01:34 PM

Status

Local Testing [ 10200 ]

Reopen in Local [ 10018 ]

Rakesh Roy (Inactive) made changes - 04/Jul/16 01:34 PM

Status

Reopen in Local [ 10018 ]

In Development [ 10007 ]

Rakesh Roy (Inactive) made changes - 04/Jul/16 01:34 PM

Item State

Parent values: LB QA(10201)

Parent values: Development(10200)Level 1 values: Ready for Local Testing(10209)

Hide

Permalink

Vikas Pawar (Inactive) added a comment - 05/Jul/16 04:13 AM

Yes its already deployed.

Show

Vikas Pawar (Inactive) added a comment - 05/Jul/16 04:13 AM Yes its already deployed.

Vikas Pawar (Inactive) made changes - 05/Jul/16 04:14 AM

Assignee

Vikas Pawar [ vikas.pawar ]

Rakesh Roy [ rakeshr ]

Vikas Pawar (Inactive) made changes - 05/Jul/16 04:14 AM

Status

In Development [ 10007 ]

Local Testing [ 10200 ]

Rakesh Roy (Inactive) made changes - 05/Jul/16 04:42 AM

Assignee

Rakesh Roy [ rakeshr ]

Deepali Tidke [ deepalit ]

Deepali Tidke (Inactive) made changes - 07/Jul/16 10:11 AM

Assignee

Deepali Tidke [ deepalit ]

Venkatesh Pujari [ venkatesh.pujari ]

Vikas Pawar (Inactive) made changes - 07/Jul/16 10:23 AM

Priority

Medium [ 3 ]

Highest [ 1 ]

Hide

Permalink

Vikas Pawar (Inactive) added a comment - 07/Jul/16 11:51 AM

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 (Inactive) added a comment - 07/Jul/16 11:51 AM 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 (Inactive) made changes - 08/Jul/16 05:28 AM

Status

Local Testing [ 10200 ]

Reopen in Local [ 10018 ]

Hide

Permalink

Venkatesh Pujari (Inactive) added a comment - 08/Jul/16 05:28 AM

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 (Inactive) added a comment - 08/Jul/16 05:28 AM 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 (Inactive) made changes - 08/Jul/16 05:28 AM

Assignee

Venkatesh Pujari [ venkatesh.pujari ]

Vikas Pawar [ vikas.pawar ]

Hide

Permalink

Venkatesh Pujari (Inactive) added a comment - 11/Jul/16 05:44 AM

Hi Vikas,

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

Thanks,
Venkatesh

Show

Venkatesh Pujari (Inactive) added a comment - 11/Jul/16 05:44 AM Hi Vikas, This is a generic issue so please ignore and this ticket is ready for stage. Thanks, Venkatesh

Venkatesh Pujari (Inactive) made changes - 11/Jul/16 05:44 AM

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 (Inactive) made changes - 12/Jul/16 08:47 AM

Summary

Query Performance -AddChangeTermReport

Query Performance -Bulk Insert AddChangeTermReport

Rakesh Roy (Inactive) made changes - 13/Jul/16 02:07 AM

Developer

Vikas Pawar [ vikas.pawar ]

Rakesh Roy (Inactive) made changes - 13/Jul/16 02:07 AM

Status

Reopen in Local [ 10018 ]

In Development [ 10007 ]

Rakesh Roy (Inactive) made changes - 13/Jul/16 02:07 AM

Status

In Development [ 10007 ]

Local Testing [ 10200 ]

Ashwin Wankhede (Inactive) made changes - 13/Jul/16 10:21 AM

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 (Inactive) made changes - 27/Jul/16 09:45 AM

Assignee

Vikas Pawar [ vikas.pawar ]

Venkatesh Pujari [ venkatesh.pujari ]

Hide

Permalink

Vikas Pawar (Inactive) added a comment - 27/Jul/16 09:46 AM

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

Show

Vikas Pawar (Inactive) added a comment - 27/Jul/16 09:46 AM Hi Venkatesh Pujari , This patch is moved on stage in Stage build. Please test this on stage environment.

Venkatesh Pujari (Inactive) made changes - 27/Jul/16 10:28 AM

Status

Local Testing [ 10200 ]

Pending for Stage Approval [ 10300 ]

Venkatesh Pujari (Inactive) made changes - 27/Jul/16 10:28 AM

Status

Pending for Stage Approval [ 10300 ]

Approved for Stage [ 10030 ]

Venkatesh Pujari (Inactive) made changes - 27/Jul/16 10:28 AM

Status

Approved for Stage [ 10030 ]

Stage Testing [ 10201 ]

Hide

Permalink

Venkatesh Pujari (Inactive) added a comment - 27/Jul/16 10:29 AM

Tested this on Stage on City of Denton for hspl.

Reports working fine with last recent activity also.

Ready for Production.

Show

Venkatesh Pujari (Inactive) added a comment - 27/Jul/16 10:29 AM Tested this on Stage on City of Denton for hspl. Reports working fine with last recent activity also. Ready for Production.

Venkatesh Pujari (Inactive) made changes - 27/Jul/16 10:29 AM

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 (Inactive) made changes - 02/Aug/16 08:42 AM

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)

Rakesh Roy (Inactive) made changes - 07/Aug/16 05:01 AM

Status

Stage Testing [ 10201 ]

Pending for Production Approval [ 10301 ]

Rakesh Roy (Inactive) made changes - 07/Aug/16 05:01 AM

Status

Pending for Production Approval [ 10301 ]

Approved for production [ 10034 ]

Rakesh Roy (Inactive) made changes - 07/Aug/16 05:02 AM

Status

Approved for production [ 10034 ]

Production Testing [ 10202 ]

Hide

Permalink

Venkatesh Pujari (Inactive) added a comment - 08/Aug/16 05:57 AM

Tested this on Production on MHNGS For HSPL company.

Reports working fine with last recent activity also.

Show

Venkatesh Pujari (Inactive) added a comment - 08/Aug/16 05:57 AM Tested this on Production on MHNGS For HSPL company. Reports working fine with last recent activity also.

Venkatesh Pujari (Inactive) made changes - 08/Aug/16 05:57 AM

Resolution		Done [ 10000 ]
Status	Production Testing [ 10202 ]	Production Complete [ 10028 ]

Venkatesh Pujari (Inactive) made changes - 08/Aug/16 05:58 AM

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 (Inactive) made changes - 08/Aug/16 05:58 AM

Resolution	Done [ 10000 ]	Fixed [ 1 ]
Status	Production Complete [ 10028 ]	Closed [ 6 ]

Transition	Time In Source Status	Execution Times

Vikas Pawar (Inactive) made transition - 30/Jun/16 11:03 AM

New Request

Pending for Approval

29d 3h 51m

Vikas Pawar (Inactive) made transition - 30/Jun/16 11:03 AM

Pending for Approval

Approved for Development

Vikas Pawar (Inactive) made transition - 30/Jun/16 11:03 AM

Approved for Development

In Development

Venkatesh Pujari (Inactive) made transition - 08/Jul/16 05:28 AM

In LB Testing

Reopen in Local

3d 2h 32m

Rakesh Roy (Inactive) made transition - 13/Jul/16 02:07 AM

Reopen in Local

In Development

4d 20h 39m

Rakesh Roy (Inactive) made transition - 13/Jul/16 02:07 AM

In Development

In LB Testing

4d 15h 52m

Venkatesh Pujari (Inactive) made transition - 27/Jul/16 10:28 AM

In LB Testing

Pending for Stage Approval

14d 8h 20m

Venkatesh Pujari (Inactive) made transition - 27/Jul/16 10:28 AM

Pending for Stage Approval

Approved for Stage

Venkatesh Pujari (Inactive) made transition - 27/Jul/16 10:28 AM

Approved for Stage

Stage Testing

Rakesh Roy (Inactive) made transition - 07/Aug/16 05:01 AM

Stage Testing

Pending for Production Approval

10d 18h 33m

Rakesh Roy (Inactive) made transition - 07/Aug/16 05:01 AM

Pending for Production Approval

Approved for production

Rakesh Roy (Inactive) made transition - 07/Aug/16 05:02 AM

Approved for production

In Production Testing

Venkatesh Pujari (Inactive) made transition - 08/Aug/16 05:57 AM

In Production Testing

Production Complete

1d 55m

Venkatesh Pujari (Inactive) made transition - 08/Aug/16 05:58 AM

Production Complete

Closed

21s

People

Assignee:: Venkatesh Pujari (Inactive)

Reporter:: Vikas Pawar (Inactive)

Developer:: Vikas Pawar (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 01/Jun/16 07:11 AM

Updated:: 08/Aug/16 05:58 AM

Resolved:: 08/Aug/16 05:58 AM