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
 

 

Query performance

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Anjani K Jha

New User


Joined: 20 Dec 2007
Posts: 2
Location: Jacksonville, FL

PostPosted: Fri Jan 14, 2011 1:08 am    Post subject: Query performance
Reply with quote

Hi, I have a SQL query that performs abysmally poorly. Earlier it was taking 7 hours to execute no it is running much longer than that. I am posting the query to see if I can get any expert advice to straighten this query to improve its performance. I know I am not providing much detail, but I can supply more details if needed.

Thanks in advance for your attention!



Code:
SELECT dfv.datasource_id,
pp.party_id,
pe.email_ad,
min(pe.sys_load_dt),
min(current timestamp)
FROM personemail pe,
person p LEFT OUTER JOIN personparty pp
ON p.prsn_id = pp.prsn_id,
datafileversion dfv
WHERE p.sys_asof_dt BETWEEN GETEXTRACTSTART('ooSyncBmEmail') and GETEXTRACTEND('ooSyncBmEmail')
AND p.dir_mkt_blk_eml_in = 0.0
AND p.prsn_id = pe.prsn_id
AND pe.load_datafileversionid = dfv.datafileversionid
AND not exists ( select 1
from bm_nonbm_email b1 where b1.email_address = pe.email_ad and
b1.source_id = dfv.datasource_id and (pp.party_id is null or pp.party_id = b1.party_id) )
GROUP BY
dfv.datasource_id,
pp.party_id,
pe.email_ad with ur
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 14, 2011 2:36 am    Post subject:
Reply with quote

Hello and welcome to the forum,

Suggest you post a bit of sample data from the tables involved and the results you want from that sample data when your query is processed.

Mention the keys for the tables and the number of rows in each table. How many rows do you expect to be referenced in this query?

When posting data or code, use the "Code" tag to improve readability and preserve alignment.
Back to top
View user's profile Send private message
Anjani K Jha

New User


Joined: 20 Dec 2007
Posts: 2
Location: Jacksonville, FL

PostPosted: Fri Jan 14, 2011 8:41 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello and welcome to the forum,

Suggest you post a bit of sample data from the tables involved and the results you want from that sample data when your query is processed.

Mention the keys for the tables and the number of rows in each table. How many rows do you expect to be referenced in this query?

When posting data or code, use the "Code" tag to improve readability and preserve alignment.


Hi Dick, As per your instructions I am attaching the file with code, table structure and other relevant info. Please let me know if I can provide further information. Looking for your insights to straighten this query.
Thanks in advance

attachment inlined ( with the code tags ) and deleted
Code:

Table #1

personemail
no. of rows ==> more than 3million rows
PRSN_ID+EMAIL_AD --> primary key

sample data:

(PRSN_ID, EMAIL_AD, SYS_LOAD_DT, LOAD_DATAFILEVERSIONID) VALUES
 
('3159731', 'bbccdd@hotmail.com', '9/16/1998 10:09:41 AM', '239'),

('3166167', 'kexmona@msn.com', '9/23/1998 1:09:14 AM', '242'),

('2527483', 'jklmno@aol.com', '9/23/1998 1:09:16 AM', '242'),

('3156472', 'effgh@rocketmail.com', '9/23/1998 1:09:31 AM', '242'),

('2581476', 'abcdeff.morant@nationsbank.com', '9/30/1998 2:09:00 AM', '247')

Table #2

person

no. of rows ==> around 92Million

PRSN_ID --> primary key

sample data:

(PRSN_ID, PARTY_ID, ACC_TAXID_NO, ACC_TAXID_TYPE, DIR_MKT_BLK_ML_IN, DIR_MKT_BLK_EML_IN, DIR_MKT_BLK_OPH_IN, DIR_MKT_BLK_SS_IN, AFFL_SHARE_IN, DECS_IN, FTR_PRVCY_BLK_1, FTR_PRVCY_BLK_2, FTR_PRVCY_BLK_3, FTR_PRVCY_BLK_4, SYS_LOAD_DT, SYS_ASOF_DT, LOAD_DATAFILEVERSIONID, ASOF_DATAFILEVERSIONID, TRANS_TYPE, SSN_TIN, AFFL_MARKET_IN, AFFL_MARKET_ASOF_DT, SB1_IN, SB1_ASOF_DT, SB1_EXPIRY_DT, JOINT_MARKET_IN, JOINT_MARKET_ASOF_DT, JOINT_MARKET_EXPIRY_DT, AFFL_SHARE_EXPIRY_DT, AFFL_MARKET_EXPIRY_DT, EMAIL_ASOF_DT, PHONE_ASOF_DT, SS_ASOF_DT, MAIL_CAPTURE_DT, EMAIL_CAPTURE_DT, PHONE_CAPTURE_DT, SS_CAPTURE_DT, AFFL_SHARE_CAPTURE_DT, AFFL_MARKET_CAPTURE_DT, MAIL_REVERSAL_REQ_DT, EMAIL_REVERSAL_REQ_DT, PHONE_REVERSAL_REQ_DT, AFFL_SHARE_REVERSAL_REQ_DT, AFFL_MARKET_REVERSAL_REQ_DT, SB1_CAPTURE_DT, JOINT_MARKET_CAPTURE_DT, AFFL_SHARE_ASOF_DT, MAIL_ASOF_DT, DEC_CAPTURE_DT, DEC_REVERSAL_DT, FTR_PRVCY_BLK_1_CAPT_DT, FTR_PRVCY_BLK_1_ASOF_DT) VALUES

('1', NULL, NULL, 'S', '1', '1', '0', '1', '1', '0', '1', '1', '1', '1', '8/10/1997 6:08:11 PM', '11/13/2010 3:15:57 AM', '1', '17091', 'C', '503661431', '1', NULL, '1', NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '2/15/2005 12:00:00 AM', NULL, NULL, NULL, '2/15/2005 12:00:00 AM', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),

('2', NULL, NULL, NULL, '0', '0', '0', '1', '0', '1', '1', '1', '1', '1', '8/10/1997 6:08:11 PM', '5/16/2010 9:03:22 AM', '1', '1', 'B', NULL, '0', '5/30/2008 12:00:00 AM', '0', '4/4/2009 12:00:00 AM', NULL, '0', '4/4/2009 12:00:00 AM', NULL, NULL, NULL, '9/22/1997 12:00:00 AM', '5/16/2010 12:00:00 AM', NULL, '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', NULL, '9/22/1997 12:00:00 AM', '5/30/2008 12:00:00 AM', NULL, NULL, NULL, NULL, NULL, '4/4/2009 12:00:00 AM', '4/4/2009 12:00:00 AM', '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', NULL, NULL, NULL, NULL),

('3', NULL, NULL, NULL, '0', '0', '0', '1', '0', '1', '1', '1', '1', '1', '8/10/1997 6:08:11 PM', '9/22/1997 5:09:47 PM', '1', '1', 'B', NULL, '0', '5/30/2008 12:00:00 AM', '0', '4/4/2009 12:00:00 AM', NULL, '0', '4/4/2009 12:00:00 AM', NULL, NULL, NULL, '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', NULL, '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', NULL, '9/22/1997 12:00:00 AM', '5/30/2008 12:00:00 AM', NULL, NULL, NULL, NULL, NULL, '4/4/2009 12:00:00 AM', '4/4/2009 12:00:00 AM', '9/22/1997 12:00:00 AM', '9/22/1997 12:00:00 AM', NULL, NULL, NULL, NULL),

('4', NULL, NULL, 'S', '1', '1', '0', '1', '1', '0', '1', '1', '1', '1', '8/10/1997 6:08:12 PM', '9/22/1997 5:09:47 PM', '1', '689', 'C', '241729008', '1', NULL, '1', NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '9/22/1997 12:00:00 AM', NULL, NULL, NULL, '9/22/1997 12:00:00 AM', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),

('6', NULL, NULL, 'S', '1', '1', '0', '1', '1', '0', '1', '1', '1', '1', '8/10/1997 6:08:12 PM', '9/22/1997 5:09:47 PM', '1', '2761', 'C', '246666478', '1', NULL, '1', NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, '9/22/1997 12:00:00 AM', NULL, NULL, NULL, '9/22/1997 12:00:00 AM', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)


Table #3

datafileversion

no. of rows ==> approx. 17K

DATAFILEVERSIONID --> primary key

sample data:

(DATAFILEVERSIONID, FILESIZE, FILEDATETIME, DATASOURCE_ID) VALUES

('4381', '27106512', '2/12/2005 1:58:27 AM', '104'),

('4382', '51165539', '2/10/2005 12:45:01 PM', '102'),

('4383', '60340525', '2/14/2005 3:27:12 AM', '13'),

('4384', '216805', '2/14/2005 8:00:22 AM', '107'),

('4385', '285653', '2/14/2005 11:59:33 AM', '26')


Table #4

bm_nonbm_email

no. of rows ==> approx. 34 million

no primary key  --> primary key

sample data:

(SOURCE_ID, PARTY_ID, EMAIL_ADDRESS, CAPTURE_DT, SYS_LOAD_DT) VALUES

('6', '00000001119', 'XXXXdbm@aol.com', '6/7/2003 5:06:38 AM', '3/4/2004 11:03:24 AM'),

('6', '00000006441', 'yyyylltd@n-jcenter.com', '5/29/2003 2:05:17 PM', '3/4/2004 11:03:24 AM'),

('6', '00000011496', 'xyz2001cld@aol.com', '1/23/2004 1:01:20 PM', '3/4/2004 11:03:24 AM'),

('6', '00000012749', 'abcdtee@yahoo.com', '11/28/2008 10:17:36 AM', '12/4/2008 5:00:03 AM'),

('6', '00000015721', 'aaaa@XXX.com', '9/16/2008 11:50:34 PM', '9/18/2008 5:00:03 AM')


Result is expected as below:

('6', '00000001119', 'XXXXdbm@aol.com', '6/7/2003 5:06:38 AM', '3/4/2004 11:03:24 AM')

Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 14, 2011 10:21 am    Post subject:
Reply with quote

Hello,

Many of our contributors are not permitted to download attachment by their organizations. Please repost inline using the "Code" tag to preserve alignment and improve readability.

Please do not post the delimited "stuff" that is in the attachment - just the data lined up properly. And only the data that applies to the operation of the query (not all of the columns).

Suggest you consider discarding this query and unload the needed data and process sequentially.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jan 14, 2011 2:32 pm    Post subject:
Reply with quote

Really : first thing to check/supply is EXPLAIN-info
my guesses : query starts from Person and not from Personemail:
change this :
1)
WHERE (p.sys_asof_dt BETWEEN GETEXTRACTSTART('ooSyncBmEmail') and GETEXTRACTEND('ooSyncBmEmail') or 0=1)
AND (p.dir_mkt_blk_eml_in = 0.0 or 0=1)
2)
you should have an index on bm_nonbm_email beginning with email_address.
3)
Why do you select min(current timestamp) ? just current timestamp should do just fine.
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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