Optimization

From UG

Revision as of 21:11, 25 May 2012

Info

• parent mantis: 0000028: [Optimization] .... parent
• mantis category: Optimization

Requirements

Keep system speed/performance at the normal levels NOW and IN THE FUTURE.

It is assumed that to satisfy above we have to:

• Optimize existing code, DB, etc to
• Develop strategy for the future development

[Perry: 2/22/2012 comments/ideas regarding approach and strategy]

• Separate web/app server from database server.
• Introduce scheduled batch reporting features for processor/memory intensive reports
• Develop plan to proactively monitor server utilization (Evaluate available tools - I know BMC Software has tools to report on various data points across a specified period of time)
• Look for MySQL and Tomcat tools to monitor SQL statements and long running processes. Evaluate database explain plans - optimize SQL statements and introduce indexes where necessary.

SOW 0

• 0001357: (Misc) Create CT2 2015 DB (Ops/Acc modules) Test speed, optimize (requirements for how fast each CT2 table will grow attached)

	For every table:
a) record how many records are now
b) find out (interview BAs) - how many records will be there at the end of 2010
c) estimate rate of growth per year after 2010
d) post this info into http://ct.jaguarfreight.com/wiki/Intro_into_CT2_DB [^] [^]

Based on the info above CT2 Architect will:

a) create database similar in size to dec 2010 levels
b) estimate speed of application
c) compare above with target
d) optimize DB and algorithms to reach the target speed 

SOW 1

* 0002233: (CT2 Misc) Ongoing DB optimization (SOW 1)

Implementation Notes SOW 1

• P/l for mult cts report refactoring
• Billing required report refactoring
• P/l report for CT group and for Master - lines from one PI for different cts was grouped into one line per Marc's request

SOW 2

• 0003312: (CT2 Misc) Ongoing DB optimization (SOW 2)

Implementation Notes SOW 2

1. Refactored ChargeCodes to ShipmentInvoices relation table (direct access without using tblTableOfCharges table).
2. Reworked ChargeCode persistence methods to speed-up Invoices access. Was improved speed of reports generation 

report p&l for multiple cts "Special View: Charge Code Group" optimized.
this report now works 10-20 times faster

SOW 3

Implementation Notes SOW 3

• Refactor IsBasicApproval field (Boolean) to PlannerType field. Will simplify Queries logic.
• Migrate current Planner Shipment Authorization Status fields to tblGenericShipment table
  • Avoid joining pair of long tables tblGenericShipment and tblShipmentAuthorizationStatusHistory
  • improve performance of Planner/Shipper portal and Reporting
• Refactored Query class
  • removed quotation marks of Integer Parameter in Queries composition. As Vlad noted, comparing Integer to Character values has significant negative effect on performance.

SOW 4

Kostya: I need to add indexes and constraints to PDF dosc tables - for individual cts and masters. To implement this I should drop some "garbage" records from these several tables so I need time to review existing records and remove all that not have links to shipments that correnctly database has. Only after this I can add needed constraints to the tables. This indexes and constraints will improve database integrity and force speed.

SOW 5 Re-architect EDI interface

• 0003523: Re-architect the interface: connect problem between CT2 & FTP Server for TMS, Air Status & EUAN BP.(related: 0003479)

Main Objective: Prevent a single FTP thread from indefinitely blocking other FTP threads due to any error condition (either connection problem, transmission problem, etc..). This applies to all FTP interfaces including TMS, Trendset, etc..

Proposed Solution:

• Add timeout to all FTP processes to return control back to the application after a predetermined timeout value. Timeout value should not be hard-coded, but either config files or database driven.
• Modify existing FTP processes that delete and then copy a file from/to the same server to issue rename/move commands instead. Waste a lot of bandwidth by deleting and then copying the same file over the same server.
• FTP process currently attempts to create directory to store invalid files for each session. Modify process to only issue this command after initial environment build. After an environment is built, then do not issue this command for every FTP session.
• (Nice-to-have and will not need to implement in this SOW) Modify process to not open/close FTP connection for each and every file. Keep an FTP session open for a set time period to process all requests for that cycle, and then close the connection.

SOW 6

Parent Mantis: 003398: CT slow and processing; then randomly users are receiving Java.SQL Debug Error Messages. This task has 11 separate points Vlad makes for how to resolve these processing and Java.SQL messages.

• 0003584: CT slow and processing; then randomly users are receiving Java.SQL Debug Error Messages (SOW 6)

1. blPDFReports is missing index on ShipmentId This causes occasional full rescan of a huge table

SOW 7

• 0003585: CT slow and processing; then randomly users are receiving Java.SQL Debug Error Messages (SOW 7)

2.

1. Query_time: 6 Lock_time: 0 Rows_sent: 0 Rows_examined: 2075813

INSERT INTO tblTempShipment0 (Id,ShipmentGroupId) (SELECT gs.Id, gs.ShipmentGroupId FROM viewShipment gs LEFT JOIN tblShipmentGroup sg ON gs.ShipmentGroupId=sg.Id WHERE gs.IsDeleted=0 AND (gs.IsApprovedByPlanner IS NULL OR gs.IsApprovedByPlanner=1) AND gs.IsLossAuthorized=0 GROUP BY gs.Id)

The statement depends on more than 90% of shipments in the database. Even there are about 90K shipments in the database, the statement touches about 2M.

As database grows the statement will take more and more to execute.

SOW 8

• 0003586: CT slow and processing; then randomly users are receiving Java.SQL Debug Error Messages (SOW 8)

3.

1. Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 4465332

SELECT COUNT(gs.Id) AS C FROM viewShipment gs WHERE (gs.TransportModeId BETWEEN 2 AND 9) AND gs.ActualPickupDate='0000-00-00' AND ((NOT gs.EstimatedPickupDate='0000-00-00' AND gs.EstimatedPickupDate<'2012-01-23') OR

(gs.EstimatedPickupDate BETWEEN '2012-01-23' AND '2012-01-23' + INTERVAL 7 DAY)) AND gs.IsDeleted=0 AND (gs.IsApprovedByPlanner IS NULL OR gs.IsApprovedByPlanner=1) AND ((gs.TransportModeId='2' OR gs.TransportModeId

='3' OR gs.TransportModeId='4' OR gs.TransportModeId='5' OR gs.TransportModeId='6' OR gs.TransportModeId='8' OR gs.TransportModeId='9')) AND gs.Cancelled=0;

This query attempts to limit amount of data using 2012-01-23 cut off. However design of viewShipment prohibits that. Query touches 4.5M rows. The query will get slower as shipments are added to the database.