Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Optimization of Query (Urgent)

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Aditya Bhardwaj
Warnings : 1

New User


Joined: 02 Nov 2004
Posts: 18
Location: Noida, India

PostPosted: Thu Dec 09, 2004 5:04 pm    Post subject: Optimization of Query (Urgent)
Reply with quote

1. i have two tables TABLEA and TABLEB and joining them with inner join.

2. TABLEA and TABLEB continas 250000 and 4750000 records respectively.

3. Query i want to execute is :-
Select count(*)
from TABLEA A, TABLEB B
WHERE
(A.INVOICE_DATE >= '2004-09-01' AND
A.INVOICE_DATE <= '2004-10-31') AND
A.CUST_TYPE_CODE IN ('01', '02') AND
(A.SO_ORDR_TYPE_CODE NOT IN ('5', '6', '0') AND
A.SHPMENT_TYPE_CODE <> 'D') AND
(A.SALES_OFFICE = B.SALES_OFFICE AND
A.SALES_ORDR_NO = B.SALES_ORDR_NO AND
A.INVOICE_NO = B.INVOICE_NO);

4. The following query with just the needed Join condition only is taking a real hard time.
Select count(*)
from TABLEA A, TABLEB B
WHERE
(A.SALES_OFFICE = B.SALES_OFFICE AND
A.SALES_ORDR_NO = B.SALES_ORDR_NO AND
A.INVOICE_NO = B.INVOICE_NO);

5. If i include the rest of the condition(s) i.e. like step 3, it is all gone in sleep state.

6. Can anyone help me in optimizing the query.

7. it will be appreciated if u could please mail it on my personal id also which is there in my signature. thanks
Back to top
View user's profile Send private message

ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Fri Dec 10, 2004 2:41 pm    Post subject: This is one way...
Reply with quote

Hi Bhardhwaj,

I dont know much but i will give you some suggestions for optimization. If it works well thanks GOD. ok.

1. Index The Tables using SALES_OFFICE,SALES_ORDER_NO and INVOICE_NO

2. Change Join Condition as ...

....WHERE (A.SALES_OFFICE,A.SALES_ORDER_NO,A.INVOICE_NO) IN (B.SALES_OFFICE,B.SALES_ORDER_NO,B.INVOICE_NO)

3. Other Condition YOU can write as.......

(i) A.INVOICE_DATE BETWEEN TO_DATE('2004-09-01') AND
TO_DATE('2004-10-31')
(ii) Avoid using NOT IN change it to possitive using IN if possible
(iii) Change "<>" to "IN" and give remaining values.

I am not sure but it will reduce the Stage 2 operations and improves performance.

Bye,
Reddy.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Urgent Mainframe Requirments (2-10 yr... Shankar779 Mainframe Jobs 0 Sun Nov 27, 2016 1:48 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us