Results 1 to 6 of 6
  1. #1
    Join Date
    May 2005
    Posts
    25

    Unanswered: Performance of two sql files

    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);

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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

  3. #3
    Join Date
    May 2005
    Posts
    25
    Thanks buddy.

    Any other suggestions?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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".
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    May 2005
    Posts
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •