467,114 Members | 1,367 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,114 developers. It's quick & easy.

Sql performance Tuning

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
  • viewed: 2055
Share:
2 Replies
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
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.

Similar topics

1 post views Thread by Fusheng Wang | last post: by
35 posts views Thread by sacha.prins@gmail.com | last post: by
2 posts views Thread by Jeff S | last post: by
6 posts views Thread by Marc Hoeijmans | last post: by
13 posts views Thread by atlaste | last post: by
4 posts views Thread by 73k5blazer@centurytel.net | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.