By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,558 Members | 1,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,558 IT Pros & Developers. It's quick & easy.

Sql performance Tuning

P: 17
Sql performance tuning in DB2

Table Trans has multiplle records for each employee. I am updating id into lookup from trans .
Currently, it takes 3 minutes to update lookup table. Both lookup and Trans has around 1.5 million test data.
Table Trans can increase upto 1 billion. Is there any better way to increase the performance. I am using db2 udb 8.2 partitoned, aix .

Table TRANS (multiple records for each combination)

id emp_name emp_cd ac_num

a01 joe cd a890
a02 john cd b345
a01 joe cd a890
a02 john cd b345
a03 john bq b345
a04 sara cd n234
a03 john bq b345

Table LOOKUP (unique record for each combination)

id emp_name emp_cd ac_num
john cd b345
joe cd a890
john bq b345
sara cd n234


Result of lookup

id emp_name emp_cd ac_num
a02 john cd b345
a01 joe cd a890
a03 john bq b345
a04 sara cd n234

USING THIS SQL IN A STORED PROCEDURE:

update lookup a set a.id = (select ltrim(rtrim(b.id)) from trans b
where a.emp_name = b.emp_name
a.emp_cd = b.emp_cd
a.ac_num = b. ac_num
fetch first 1 rows only )
where exists(select 1 from trans b
a.emp_name = b.emp_name
a.emp_cd = b.emp_cd
a.ac_num = b. ac_num );
Aug 8 '07 #1
Share this Question
Share on Google+
2 Replies


P: 20
Sql performance tuning in DB2

Table Trans has multiplle records for each employee. I am updating id into lookup from trans .
Currently, it takes 3 minutes to update lookup table. Both lookup and Trans has around 1.5 million test data.
Table Trans can increase upto 1 billion. Is there any better way to increase the performance. I am using db2 udb 8.2 partitoned, aix .

Table TRANS (multiple records for each combination)

id emp_name emp_cd ac_num

a01 joe cd a890
a02 john cd b345
a01 joe cd a890
a02 john cd b345
a03 john bq b345
a04 sara cd n234
a03 john bq b345

Table LOOKUP (unique record for each combination)

id emp_name emp_cd ac_num
john cd b345
joe cd a890
john bq b345
sara cd n234


Result of lookup

id emp_name emp_cd ac_num
a02 john cd b345
a01 joe cd a890
a03 john bq b345
a04 sara cd n234

USING THIS SQL IN A STORED PROCEDURE:

update lookup a set a.id = (select ltrim(rtrim(b.id)) from trans b
where a.emp_name = b.emp_name
a.emp_cd = b.emp_cd
a.ac_num = b. ac_num
fetch first 1 rows only )
where exists(select 1 from trans b
a.emp_name = b.emp_name
a.emp_cd = b.emp_cd
a.ac_num = b. ac_num );
from a quick look, it seems you can use the merge statement. I am not sure if it is in your version of db2 through, but it is worth a try.
Aug 8 '07 #2

P: 17
Thanks a lot Kalexin. Merge function works perferctly.
Aug 9 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.