Any way to make this query more efficient?
Posted: Mon Nov 05, 2012 8:15 pm
I have two tables, one holds customer information, and the other holds payment information. Removing superfluous columns, the tables look something like this, with * to show the fields that constitute the primary key:
I have a requirement where if a customer has not placed an order within the last five years, a "soft delete" will be carried out (set T1_STATUS_CODE = 'D'), as long as the customer table entry in question hasn't been updated in the last year.
The query I have written times out as there is a huge amount of data in both tables, but especially the order table - unfortunately the customer number is not indexed on the Order table.
Does anybody have any ideas as to how I could make this more efficient? This query will be invoked programatically, and I had thought of using a LIKE predicate on the rightmost 2 digits of the customer number (this should always be numeric despite the CHAR designation) to break the work down into 100 individual tasks.
Any ideas on how to make this more efficient? Or open to a better solution?
TBL_CUSTOMER
========
T1_YEAR* SMALLINT(2)
T1_CUSTOMER_NO* CHAR(9)
T1_CUSTOMER_TYPE* CHAR(1)
T1_STATUS_CODE CHAR(1)
T1_LAST_UPDATE_TIMESTAMP TIMESTAMP
TBL_ORDER
=======
T2_YEAR SMALLINT (2)
T2_CUSTOMER_NO CHAR(9)
T2_CUSTOMER_TYPE CHAR(1)
T2_PROCESSED_DATE* DATE
T2_REGION_CODE* CHAR(2)
T2_BATCH_SEQUENCE* SMALLINT(2)
T2_SEQUENCE_NUMBER* INT(4)
========
T1_YEAR* SMALLINT(2)
T1_CUSTOMER_NO* CHAR(9)
T1_CUSTOMER_TYPE* CHAR(1)
T1_STATUS_CODE CHAR(1)
T1_LAST_UPDATE_TIMESTAMP TIMESTAMP
TBL_ORDER
=======
T2_YEAR SMALLINT (2)
T2_CUSTOMER_NO CHAR(9)
T2_CUSTOMER_TYPE CHAR(1)
T2_PROCESSED_DATE* DATE
T2_REGION_CODE* CHAR(2)
T2_BATCH_SEQUENCE* SMALLINT(2)
T2_SEQUENCE_NUMBER* INT(4)
I have a requirement where if a customer has not placed an order within the last five years, a "soft delete" will be carried out (set T1_STATUS_CODE = 'D'), as long as the customer table entry in question hasn't been updated in the last year.
The query I have written times out as there is a huge amount of data in both tables, but especially the order table - unfortunately the customer number is not indexed on the Order table.
Does anybody have any ideas as to how I could make this more efficient? This query will be invoked programatically, and I had thought of using a LIKE predicate on the rightmost 2 digits of the customer number (this should always be numeric despite the CHAR designation) to break the work down into 100 individual tasks.
UPDATE TBL_CUSTOMER
SET T1_STATUS_CODE = 'D'
,T1_LAST_UPDATE_TIMESTAMP = CURRENT_TIMESTAMP
WHERE NOT EXISTS
(SELECT T2_CUSTOMER_NO
,T2_CUSTOMER_TYPE
FROM TBL_ORDER
WHERE T1_CUSTOMER_NO = T2_CUSTOMER_NO
AND T1_CUSTOMER_TYPE = T2_CUSTOMER_TYPE
AND T2_PROCESSED_DATE > DATE(CURRENT_TIMESTAMP - 5 YEARS)
FETCH FIRST 1 ROWS ONLY)
AND RIGHT(T2_CUSTOMER_NO,2) LIKE '%00' (etc...)
AND T1_LAST_UPDATE_TIMESTAMP < CURRENT_TIMESTAMP - 1 YEAR
SET T1_STATUS_CODE = 'D'
,T1_LAST_UPDATE_TIMESTAMP = CURRENT_TIMESTAMP
WHERE NOT EXISTS
(SELECT T2_CUSTOMER_NO
,T2_CUSTOMER_TYPE
FROM TBL_ORDER
WHERE T1_CUSTOMER_NO = T2_CUSTOMER_NO
AND T1_CUSTOMER_TYPE = T2_CUSTOMER_TYPE
AND T2_PROCESSED_DATE > DATE(CURRENT_TIMESTAMP - 5 YEARS)
FETCH FIRST 1 ROWS ONLY)
AND RIGHT(T2_CUSTOMER_NO,2) LIKE '%00' (etc...)
AND T1_LAST_UPDATE_TIMESTAMP < CURRENT_TIMESTAMP - 1 YEAR
Any ideas on how to make this more efficient? Or open to a better solution?