473,322 Members | 1,287 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Sql performance Tuning

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
2 2288
kalexin
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
Hevan
17
Thanks a lot Kalexin. Merge function works perferctly.
Aug 9 '07 #3

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

Similar topics

5
by: nmac | last post by:
Hi all, hopefully someone can offer some sagely advice regarding Production use of Jakarta's Tomcat. First, some brief background. My company have a servlet application that connects to a MySQL...
0
by: Tober | last post by:
Anyone happen to know of any good books on performance tuning for MS-SQL 2000? I am currently building a Cognos datawarehouse and would like to ensure that the db is tuned for peak performance. ...
9
by: pheonix1t | last post by:
hello, I've been assigned to do performance tuning on an SQL2000 database (around 10GB in size, several instances). So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM. I see the...
1
by: Fusheng Wang | last post by:
Hi, I have an insert intensive database, and it seems that the performance is a problem. Any suggestions on performance tuning on insert performance? Thanks a lot! Frank
35
by: sacha.prins | last post by:
Hi, I read a lot about DB2 INSERT performance here. I have a nice story as well. The thing is, I work on 2 installations of DB2 (on completely different locations) which run on different (but...
2
by: Jeff S | last post by:
I'm looking for guidance (tutorials, backgrounders, tips, or otherwise) on measuring the performance of ASP.NET applications. I'm specifically interested in acquiring the capability of generating...
6
by: Marc Hoeijmans | last post by:
Can any one tell me if the performance in the 2.0 framework is improved? An 1.1 ASP.NET application performce bad once a moth we are thinking of upgrading 2.0, should this help ore is it more...
3
by: hpw | last post by:
Hi all, i'm looking for a good book about .net Performance Tuning. Things that should be covered by this book: - Glean information about your program's behavior from profiling tools -...
13
by: atlaste | last post by:
Hi, I'm currently developing an application that uses a lot of computational power, disk access and memory caching (to be more exact: an information retrieval platform). In these kind of...
4
by: 73k5blazer | last post by:
Hello again all.. We have a giant application from a giant software vendor that has very poor SQL. It's a PLM CAD application, that makes a call to the db for every cad node in the assembly. So...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.