Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Question Unanswered: Db2 Query Optimization/fine Tuning

    Hi,

    I'm working on fine tuning a complex db2 query which consists of multiple select statments combined by UNIONs and i need to avoid UNIONs

    Below is an example of my tables and part of my query.

    Your help is appreciated and my advance thanks!!!


    TRANSACTION Table
    -------------------
    TRANS_NO
    TRANS_DATE
    TRANS_LOC
    TRANS_AMT
    TRANS_ENO
    TRANS_DEPT

    EMPLOYEE Table
    ---------------
    EMP_NO
    EMP_NAME
    EMP_DOB
    EMP_ADDRESS


    EMPLOYEE_TEMP Table
    ----------------------
    ETEMP_NO
    ETEMP_NAME
    ETEMP_DOB
    ETEMP_ADDRESS

    I want to combine the above 3 tables. TRANSACTION table is the main table which refers details from either EMPLOYEE or EMPLOYEE_TEMP table.

    TRANSACTION table first needs to do a equi-join (TRANS_ENO = EMP_NO) with EMPLOYEE table to get the employee name (EMP_NAME). If the match is not found in EMPLOYEE table, TRANSACTION table has to do another equi-join (TRANS_ENO = ETEMP_NO) with EMPLOYEE_TEMP table.

    And if the Employee name is not found in both the tables, "Employee Name not found" should be returned for that particular record/row.

    Pls. note that UNIONs should not be used.

    The final result should be sorted on "Employee name" column

    Sample result (result sorted by EMP_Name)
    ------------
    TRANS_NO TRANS_DATE TRANS_LOC TRANS_AMT EMP_NAME
    4444 1/1/2009 NJ $100 AAAA
    1111 5/1/2009 CA $900 BBBB
    2222 3/1/2009 PA $600 Emp Name not found
    3333 8/1/2009 NY $200 ZZZZ

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sathyanj1

    Pls. note that UNIONs should not be used.
    Can you explain why?

    You could achieve the desired result by using outer joins and the COALESCE function.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2009
    Posts
    2
    UNIONs degrated the performance of my query

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    actually unions do not degrade theperformance of the query it is how well the query is written and the existence of proper index structures that will decide the performance.
    Besides all of that I think this should be a very simple query like:
    Select value(emp.emp_no,temp.emp_no,'employee not found')
    from transaction tr
    left outer join employee emp
    on tr.TRANS_ENO = emp.EMP_NO
    left outer join employee_temp temp
    on tr.TRANS_ENO = temp.EMP_NO
    where......

    Dave Nance

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My question is if additional condition would be effective for performance.
    Code:
      FROM transaction   tr
      LEFT OUTER JOIN
           employee      emp
       ON  emp.emp_no   = tr.trans_eno
      LEFT OUTER JOIN
           employee_temp tmp
       ON  emp.emp_no IS NULL
       AND tmp.etemp_no = tr.trans_eno
     WHERE .....

Posting Permissions

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