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
 

 

Tuning a SQL query

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

Active User


Joined: 13 Jul 2005
Posts: 136

PostPosted: Thu Oct 08, 2009 5:29 pm    Post subject: Tuning a SQL query
Reply with quote

We are going to tune this below query

MERGE INTO S_USERS s
USING
(
SELECT COL_GEID,N_EMP,COL_SSN,COL_SOEID,COL_RITS,COL_EMP_STATUS,
D_HIRED,COL_EXPENSE_CODE,COL_COUNTRY,COL_WORK_PHONE,T_EMAIL,
T_MGR_LAYER,COL_MGR_GEID,COL_MGMT_CODE,D_TERM,
COUNT(S_uid) cnt_users,COUNT(temp_S_uid) cnt_tmp_use
FROM
( SELECT temp_S_users.*, 1 temp_S_uid,
to_number(null) S_uid
FROM temp_S_users
UNION ALL
SELECT S_users.*, to_number(null) temp_S_uid,
2 S_uid
FROM S_users
)
GROUP BY COL_GEID,N_EMP,COL_SSN,COL_SOEID,COL_RITS,COL_EMP_STATUS,
D_HIRED,COL_EXPENSE_CODE,COL_COUNTRY,COL_WORK_PHONE,T_EMAIL,
T_MGR_LAYER,COL_MGR_GEID,COL_MGMT_CODE,D_TERM
HAVING COUNT(S_UID) <> COUNT(TEMP_S_UID) AND
COUNT(temp_S_uid) = 1
) t
ON(s.COL_geid = t.COL_geid)

WHEN MATCHED THEN UPDATE SET
s.N_EMP = t.N_EMP,
s.COL_SSN = t.COL_SSN,
s.COL_SOEID = t.COL_SOEID,
s.COL_RITS = t.COL_RITS,
s.COL_EMP_STATUS = t.COL_EMP_STATUS,
s.D_HIRED = t.D_HIRED,
s.COL_EXPENSE_CODE = t.COL_EXPENSE_CODE,
s.COL_COUNTRY = t.COL_COUNTRY,
s.COL_WORK_PHONE = t.COL_WORK_PHONE,
s.T_EMAIL = t.T_EMAIL,
s.T_MGR_LAYER = t.T_MGR_LAYER,
s.COL_MGR_GEID = t.COL_MGR_GEID,
s.COL_MGMT_CODE = t.COL_MGMT_CODE,
s.D_TERM = t.D_TERM

WHEN NOT MATCHED THEN INSERT
(
COL_GEID,N_EMP,COL_SSN,COL_SOEID,COL_RITS,COL_EMP_STATUS,
D_HIRED,COL_EXPENSE_CODE,COL_COUNTRY,COL_WORK_PHONE,T_EMAIL,
T_MGR_LAYER,COL_MGR_GEID,COL_MGMT_CODE,D_TERM
)
VALUES
(
t.COL_GEID,t.N_EMP,t.COL_SSN,t.COL_SOEID,t.COL_RITS,
t.COL_EMP_STATUS,t.D_HIRED,t.COL_EXPENSE_CODE,
t.COL_COUNTRY,t.COL_WORK_PHONE,t.T_EMAIL,t.T_MGR_LAYER,
t.COL_MGR_GEID,t.COL_MGMT_CODE,t.D_TERM
);

Any idea for this.... Thanks in advance...
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Oct 08, 2009 6:59 pm    Post subject:
Reply with quote

well, think what this query is supposed to do:

Code:

temp_user S_user  all columns     COUNT(TEMP_S_UID) COUNT(S_UID)   Result
 exists    exists   equal           

      Y       Y        Y       =>           1              1       => Discarded because COUNT(TEMP_S_UID) = COUNT(S_UID)
      Y       Y        N       =>           1              0       => Merge => update
                                      &     0              1       => Discarded because COUNT(TEMP_S_UID) = 0
      N       Y        -       =>           0              1       => Discarded because COUNT(TEMP_S_UID) = 0
      Y       N        -       =>           0              1       => Merge : Insert


all that having and counting is just bullshit
maybe you could just

MERGE INTO S_USERS s
USING (select * from temp_S_users T1 where not exists ( select from s_users S1 where all S1.columns are equal to T1.columns)...
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Oct 08, 2009 7:28 pm    Post subject:
Reply with quote

if you don't care that you do an unnecesary update when all columns are equal:

MERGE INTO S_USERS s
USING (select * from temp_S_users T1 )
...

PS. Next time use a more significant topic Title than "DB2"
Back to top
View user's profile Send private message
vicky10001
Warnings : 1

Active User


Joined: 13 Jul 2005
Posts: 136

PostPosted: Mon Oct 19, 2009 5:43 pm    Post subject:
Reply with quote

Thanks for good confirmation .. Thanks a lot GuyC.
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
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am


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