Company: Link Technologies
Case No: L12315. Project: 12.50: LinkSOFT Version 12.50
Logged By: Sanjay (Link Technologies) on 06 Jul 2021 04:48PM
Priority: High
Product: Framework
Group: Enhancement
Time Taken: 15.00 (Weight: 15.00)
Version: 12.50.0726
Assigned To: Sanjay (Link Technologies)
Circulation: Alvis, Rashna, Sanjay
Resolve By: Friday, 09 July 2021 11:59 PM [1234 days since logged date]
Status: Closed
Subject: Reports Performance Improvements affected by very large volumes - Stage 2
Summary:    

Review all reports and perform Performance checks with particular notes to:

  1. Reasonable date range for sales reports
  2. Reasonable data set for the report to render ~100,000 max
  3. Cleanup links using indexed columns only
  4. Use IN as opposed to NOT IN
  5. Filter all tables correctly

Complete the table below. Add one comment for each report updated

Table 1 - Reports changed for performance - Verify on Nayans HQ DB and site files on LinkQA4.

NoReport NameDetails (Expected time) for data generated between 01/01/2021 to 31/01/2021StatusReport Date 01/01/2021 to 01/01/2021
Report Date 01/01/2021 to 31/01/2021
1Sales by Week EndingProcedure execution: 42s
Report rendering: <3m
Pages: 75		 Pass
N/A

Time: 2 Mins 4 Seconds
Pages: 72

2Sales Discount/Price Change ReportProcedure execution: 2m 35s
Report rendering: 4m
Pages:3
 Pass
Time: 6 Seconds
Pages: No Data
Time: 12 Seconds
Pages: 3
3Sales Line Count HourProcedure execution: 30s
Report rendering: 1m
Pages:1		 Pass
Time: 8 Seconds
Pages: 1
Time: 16 Seconds
Pages: 1
4Sales Receipt Count HourProcedure execution: 8s
Report rendering: 10s
Pages:1		 Pass
Time: 6 Seconds
Pages: 1
Time: 10 Seconds
Pages: 1
5Sales Receipt Count WeekdayProcedure execution: 8s
Report rendering: 10s
Pages:1
 Pass
Time: 6 Seconds
Pages: No Data
Time: 10 Seconds
Pages: 1
6Sales Tax Detail Report
Procedure execution: 46s
Report rendering: 2m
Pages:1
 Pass
Time: 16 Seconds
Pages: 1
Time: 2 Mins 25 Seconds
Pages: 1
7Sales Tax on Payments ReceivedProcedure execution: 1m
Report rendering: 2m
Pages:2087
 Pass
Time: 17 Seconds
Pages: 92
Time: 3 Mins 28 Seconds
Pages: 2087
8Shift Close Report (80mm)
Procedure execution: 1s
Report rendering: 1s
Pages: no data when shift ID is not selected
 Pass

Shift: N2SH0000003
Time: 16 Seconds
Pages 1

N/A
9Shift Close Report (80mm) - Consolidated Summary

Shift Close Report (A4)

Procedure execution: 10s per shift. If a month has 600 shifts, this report will take 1h 40m to generate
Report rendering: 10s per shift
Pages: 1 page per shift

 Pass

Shift Close Report (80mm) - Consolidated

Time: 33 Seconds
Pages: 31

Shift Close Report A4

Time: 48 Seconds
Pages: 20


10Shift Sales Report (80mm)
Procedure execution: 3m
Report rendering: 4m
Pages:192
 Pass
Time: 10 Seconds
Pages: 51
Time: 33 Seconds
Pages: 192
11
Shift Status Detail
Procedure execution: 50s
Report rendering: 2m
Pages:22
Pass
Time: 13 Seconds
Pages: 1
Time: 57 Seconds
Pages: 22
12
Shift Summary Report
Procedure execution: 15s
Report rendering:30s
Pages:23
Pass
Time: 9 Seconds
Pages: 1
Time: 31 Seconds
Pages: 23
13
Staff Commission Report
Procedure execution: 30s
Report rendering:1m
Pages:604
Pass
Time: 8 Seconds
Pages: 1
Time: 37 Seconds
Pages: 1
14
Top N Sales Analysis
Procedure execution: 1m
Report rendering:1m
Pages:1
Pass
Time: 8 Seconds
Pages: 2
Time: 40 Seconds
Pages: 2
15
Transaction Report
Procedure execution: 15s. Recommend to run this report for a day and using filters to limit the number of transactions.
Report rendering:1m
Pages:20
For Review

Report Date: 01/01/2021 to 01/01/2021
Result - Thread is aborted

Report Date: 01/01/2021 to 01/01/2021
Report Filter: Transaction Type- Cash Sales
Result - Thread is aborted

Report Date: 01/01/2021 to 01/01/2021
Report Filters: Transaction Type - Cash Sales and Location - Nadera

Results
Time: 3 seccond
Pages: 350


Audit Notes:Edited by sanjay on 26/07/21 09:03. Edited by rashna on 14/07/21 12:51. Edited by rashna on 14/07/21 11:52. Edited by rashna on 14/07/21 11:12. Edited by rashna on 14/07/21 10:55. Edited by rashna on 14/07/21 10:27. Edited by sanjay on 12/07/21 16:42. Edited by alvis on 12/07/21 15:52. Edited by alvis on 12/07/21 15:19. Edited by alvis on 12/07/21 14:40. Edited by alvis on 12/07/21 13:40. Edited by alvis on 12/07/21 11:46. Edited by alvis on 12/07/21 11:28. Edited by alvis on 12/07/21 09:08. Edited by sanjay on 12/07/21 08:07. Edited by alvis on 09/07/21 16:15. Edited by alvis on 09/07/21 15:47. Edited by alvis on 09/07/21 15:41. Edited by alvis on 09/07/21 15:25. Edited by sanjay on 09/07/21 11:21. Edited by sanjay on 09/07/21 11:20. 
09 Jul 202111:21AM Comment 1 by Sanjay (Link Technologies) ETC was changed from 09/07/2021 to 09/07/2021
12 Jul 202111:31AM Comment 2 by Alvis (Link Technologies) Assigned To: Sanjay (Link Technologies) Followup Date: 12-07-2021 03:28 PM Time Taken: 4.00

Performance tuning done on the following reports. Refer to case header for details.

  • Sales Tax on Payments Received
  • Shift Close Report (80mm)
  • Shift Close Report (80mm) - Consolidated Summary
  • Shift Close Report (A4)
  • Shift Sales Report (80mm)
  • Shift Status Detail

12 Jul 202101:41PM Comment 3 by Alvis (Link Technologies) Assigned To: Sanjay (Link Technologies) Followup Date: 12-07-2021 05:40 PM Time Taken: 2.00
Performance tuning done on report: Shift Summary Report

12 Jul 202102:35PM Comment 4 by Alvis (Link Technologies) Assigned To: Sanjay (Link Technologies) Followup Date: 12-07-2021 05:41 PM Time Taken: 0.50
Performance tuning done on report: Staff commission report

12 Jul 202103:19PM Comment 5 by Alvis (Link Technologies) Assigned To: Sanjay (Link Technologies) Followup Date: 12-07-2021 07:19 PM Time Taken: 0.50
Performance tuning done on report: Top N Sales Analysis

12 Jul 202103:54PM Comment 6 by Sanjay (Link Technologies) Assigned To: Rashna (Edge Business Solutions) Followup Date: 12-07-2021 07:52 PM Time Taken: 1.00
PART A - Development work for this case has been completed.

1. The change will be available in version:12.44.0712

2. The following changes were made(Include Database object names, Program classes, and any other relevant information):

  1. Refactored reports data extraction for high-volume transactions. Refer to the case header for a list of reports.

5. Notes: 
6. Next Step (Review and System Test (Developer) -> UAT (Quality) -> Documentation): UAT


14 Jul 202112:54PM Comment 7 by Rashna (Edge Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 14-07-2021 04:52 PM Time Taken: 2.00

Hi Sanjay, 

  1. I have updated the case header with the results.
  2. Can you please review the results for item 15 in the table before closure. 

Test Environment

  1. URL - http://10.0.0.14/CLIENT-NAYANS-HQ-LINKSOFT
  2. Version - 12.44.0713GA

Thanks
Rashna


14 Jul 202102:28PM Comment 8 by Sanjay (Link Technologies) Assigned To: Rashna (Edge Business Solutions) Followup Date: 14-07-2021 06:23 PM Time Taken: 3.00
PART A - Development work for this case has been completed.

1. The change will be available in version:12.45.0714

2. The following changes were made(Include Database object names, Program classes, and any other relevant information):

  1. Improved report extraction for subreports which show Lot Bin, kit and Color size items 

3. Affected Areas:

  1. Transaction Reports

4. The issue was caused by:

  1. The message "THread was aborted" may have been caused by an IIS server restart during testing. THe report we ran took 2 mintres.

5. Notes: 
6. Next Step (Review and System Test (Developer) -> UAT (Quality) -> Documentation): Retest Transaction on 12.44


26 Jul 202109:03AM Comment 9 by Sanjay (Link Technologies) Case L12315 added to project 12.50
26 Jul 202102:16PM Comment 10 by Rashna (Edge Business Solutions) Assigned To: Sanjay (Link Technologies) Followup Date: 26-07-2021 05:48 PM Time Taken: 1.00
The following tests were performed:

Table 1 - Test Results
#Test Case DetailsPass/FailComments
 1 Generate the "Transaction Report" for dates 01/01/2021 to 01/01/2021 Pass

 Report Time: 2 Mins 56 Sec
 Pages:  1183

 2 Generate the "Transaction Report" for dates 01/01/2021 to 01/01/2021 with "Transaction Type = Cash" Pass Report Time: 2 Mins 42 Sec
 Pages:  1179

Environment Details

  1. OS version:
  2. Application version: 12.50.0720
  3. Setup on:
    1. Server: LinkQA4
    2. Database: CLIENT-NAYANS-HQ-LINKSOFT
    3. LinkSOFT URL: HTTP://10.0.0.14/CLIENT-NAYANS-HQ-LINKSOFT
  4. Login Details: Standard username and password for user "admin"

Next Step: Closure


26 Jul 202103:38PM Comment 11 by Sanjay (Link Technologies) Assigned To: Sanjay (Link Technologies) Followup Date: 26-07-2021 07:37 PM Time Taken: 1.00
Thanks Rashna

If you have any queries regarding this support incident, please email admin@linktechnologies.com.au and include the Case No: L12315 in the subject line of all emails regarding this issue.

Document size: 22.6 KB
For call complaints, please contact the Managing Director of the company using this form.