Performance of two sql files

shahnazurs · #1 (**permalink**) 07-08-09, 03:43

hi,

I used the following statement in two different files and tries to execute two SQL files, which one would be faster and why?

1 ->

Code:

merge into abc.emp1 a
using abc.emp b
on
(
	a.empno=b.empno
)
when matched then
update set 
	ename=b.ename,
	sal=b.sal
when not matched then
	insert (empno,ename,sal)
	values(b.empno,b.ename,b.sal);

2->

merge into abc.emp1 a
using abc.emp b
on
(
a.empno=b.empno
)
when matched then
update set
ename=b.ename,
sal=b.sal;

insert into abc.emp1 a
select empno,ename,sal
from
abc.emp
where not exists(select 1 from abc.emp where empno=a.empno);

dr_te_z · #2 (**permalink**) 07-08-09, 04:41

1- you make 1 trip to the database engine
2- you make 2 trips to the database engine

That alone makes me vote for #1

shahnazurs · #3 (**permalink**) 07-08-09, 07:38

Thanks buddy.

Any other suggestions?

n_i · #4 (**permalink**) 07-08-09, 07:40

Quote:

Originally Posted by shahnazurs

hi,

I used the following statement in two different files and tries to execute two SQL files, which one would be faster and why?

Why don't you run a test and tell us? As to why, this question is easily answered by observing the statement execution plans.

PS. Please don't call me "buddy".

shahnazurs · #5 (**permalink**) 07-08-09, 08:25

Hi,

I would have said easily if it is Oracle DB i.e. taking execution plan or trace. Since I am novice in DB2 database,I feel difficult in doing that. pls. don't mind about calling "buddy"

Thanks,
Shahnaz.

n_i · #6 (**permalink**) 07-08-09, 09:34

Quote:

Originally Posted by shahnazurs

Hi,

I would have said easily if it is Oracle DB i.e. taking execution plan or trace. Since I am novice in DB2 database,I feel difficult in doing that.

Oracle and DB2 are not that different (finally) when it comes to the query optimization. The easiest way to see a query execution plan is to use the db2expln utility, which is described in the manuals.

Quote:

Originally Posted by shahnazurs

pls. don't mind about calling "buddy"

Actually, I do mind when strangers call me "buddy". I hope it's not too much of a trouble for you.