Any way to make this query more efficient?



IBM's flagship relational database management system

Any way to make this query more efficient?

Postby Nuadha » 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:

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)


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


Any ideas on how to make this more efficient? Or open to a better solution?
Nuadha
 
Posts: 18
Joined: Tue Nov 01, 2011 10:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Any way to make this query more efficient?

Postby Pandora-Box » Mon Nov 05, 2012 9:01 pm

Your query seems to be over complicated by having NOT EXISTS

Can you explain your requirement with SAMPLE table data and expected output?

Please rephrase your requirement better ( I am sorry atleast I didnt understand reading your need for multiple times)
User avatar
Pandora-Box
 
Posts: 65
Joined: Fri Feb 10, 2012 8:30 pm
Location: Mars
Has thanked: 3 times
Been thanked: 6 times

Re: Any way to make this query more efficient?

Postby Nuadha » Mon Nov 05, 2012 9:07 pm

The requirements are as I stated above. In short, that for a CUSTOMER table entry
- if there is no entry in the ORDER table with matching CUSTOMER ID/TYPE within the last 5 years AND
- if the CUSTOMER entry hasn't been updated in the last year THEN:

SET T1_STATUS_CD = 'D' and update the T1_LAST_UPDATE_TIMESTAMP value.

The datatypes are given above, but I mentioned that the T1_CUSTOMER_NO is declared as CHAR, but is always numeric.

Thanks.
Nuadha
 
Posts: 18
Joined: Tue Nov 01, 2011 10:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Any way to make this query more efficient?

Postby dick scherrer » Mon Nov 05, 2012 9:32 pm

Hello,

T2_CUSTOMER_NO
T2_CUSTOMER_TYPE
For starters, i'd suggest creating an index on these 2 columns.

For queries of this kind, i'm not a big fan of NOT EXISTS.

As premkrishnan mentioned, some clarification would help someone help you.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Any way to make this query more efficient?

Postby Nuadha » Mon Nov 05, 2012 10:04 pm

Thanks for your reply. That's a good suggestion, but unfortunately I can't get those fields indexed.Maybe this (my attempt at) pseudocode might help clarify:

for each row in TBL_CUSTOMER
   if T1_LAST_UPDATE_TIMESTAMP is older than 1 year then
      Get this customer's newest entry from the ORDER table
         if no orders found OR the newest order is older than 5 years then
            update T1_LAST_UPDATE_TIMESTAMP to now
            set T1_STATUS_CD to 'D' (this is the soft delete)
         end if
   end if
next row


e.g. If I find customer 123456789 has not had their record updated since 2010, and last placed an order 10 years ago, then I would 'soft delete' their entry by setting the STATUS to 'D', and I would set their "last updated" timestamp to now.

I'm kind of leaning towards doing a standalone SELECT and then iterate through with an UPDATE. After the first run, this should prove quicker.
Nuadha
 
Posts: 18
Joined: Tue Nov 01, 2011 10:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Any way to make this query more efficient?

Postby dick scherrer » Mon Nov 05, 2012 10:16 pm

Hello,

but unfortunately I can't get those fields indexed.
What business or technical reason that this index cannot be added?

I suspect that there lots of processes that would join Customer info with Order info and without this index, the cost to run the queries would be very high. Suggest you run an explain and see how the query is analyzed.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Any way to make this query more efficient?

Postby Pandora-Box » Mon Nov 05, 2012 11:02 pm

As suggested please run the EXPLAIN and share the results so someone or yourself can help others to intepret the problem themselves
User avatar
Pandora-Box
 
Posts: 65
Joined: Fri Feb 10, 2012 8:30 pm
Location: Mars
Has thanked: 3 times
Been thanked: 6 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post