IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Query performance


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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

Moderator Emeritus


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

PostPosted: Fri Jan 14, 2011 2:36 am
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
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

Moderator Emeritus


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

PostPosted: Fri Jan 14, 2011 10:21 am
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts exploiting Z16 performance PL/I & Assembler 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
Search our Forums:

Back to Top