Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    7

    Smile Unanswered: Use of outer join / performance

    Hello,

    I have a view created using outer join. I have created the same view on oracle as well as sqlserver. When I

    select from the view on DB2, the performance is much slower than on oracle or sqlserver i.e. when I select a value based on index match on DB2 it takes 1767 msecs whereas the same select on oracle takes 16 msecs. So, could someone help me with whether my view definition is the right way to go on DB2 or whether there is a better way.

    Here is my DB2 view definition.

    create view z_workorder as
    select
    W.WONUM,
    COALESCE(wl.description, w.description) description
    from workorder w left outer join (select wonum, lang, description from wo_lang where lang = get_lang() ) as wl

    on w.wonum = wl.wonum
    @

    The get_lang() function returns a language value. I have tried by eliminating the get_lang() function and

    hard coding a value, which did not make much difference, just a few msecs.

    Here is the oracle definition

    create view Z_workorder as
    select
    W.WONUM,
    nvl(wl.description, w.description) description
    from workorder w left outer join wo_lang wl
    on w.wonum = wl.wonum AND wl.lang = SYS_CONTEXT('MXLANG', 'language');

    Could someone help me with the outer join so I get a good performance out of it.

    Thanks in advance.

    Manju

  2. #2
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    I'm not experienced with Oracle, but I'm sure you can have the same performance (or almost the same) with DB2. The first thing you must check, is your configuration. Are you comparing apples with apples? Do both databases reside on machines with same processor, same memory allocation (not in the machine, I'm talking from bufferpool allocation for DB2), same OS? Bonnie Baker (www.bonniebaker.com) uses to say "if you by a pet and you don't by food for your pet, your pet won't be happy"...

    For the query:
    Did you run Explain on it? Which access path did DB2 choose?
    Do you have the same indexes defined on both databases?

    And, by the way, you can code your query as you did in Oracle:

    create view z_workorder as
    select
    W.WONUM,
    COALESCE(wl.description, w.description) description
    from workorder w left outer join wo_lang wl
    on w.wonum = wl.wonum
    where wl.lang = get_lang()

    If this query runs in a programm, I would first make a call for get_lang(), put the value on a host variable and change the where clause to "where wl.lang = :host_variable".

    HTH.
    Rodney Krick

Posting Permissions

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