Results 1 to 4 of 4

Thread: Using Joins

  1. #1
    Join Date
    Mar 2003

    Unanswered: Using Joins

    How about using joins in db2 running on OS/390 v 6.1. Will it increase the performance?.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    compared to what?

    compared to selecting records from one table into your code, then looping over the results and calling the database repetitively to select from the other table for each record, yes


  3. #3
    Join Date
    Apr 2003
    We are doing this every day on OS/390 DB2 V6..... Not a performance problem as long as indexing is adequate (PK / FK relations are indexed) and SQL is appropriate.

    Using EXPLAIN before executing the query will save you much expense as it spots tablespace scans, extra sorts etc......

    Proper indexes are key.

    Bottom line - JOINs can really hurt you if not done properly and can Truly benefit you after some calculated setup.

    I'm not sure I helped you any..... lol@me

  4. #4
    Join Date
    Apr 2003
    Trier, Germany

    Re: Using Joins

    It is always better to use joins than :
    - unions
    - self-programmed stuff
    since unions use at least twice the time, and self programmed stuff only uses nested loop technology, or merge scan technology. DB2 can use both, depending on statistics. And, DB2 can change the table order and perhaps use the star join, too. The last reason is, that you have to close and reopen cursors very often, while DB2 can handle this internally much faster.
    Use Explain with the STATEMENT-Table created and see, how much service units the join would use. Then, calculate the service units consumed, when opening the inner cursor as often, as the outer cursor will give results. I cannot belive, that this would be faster....

    But still, keep statistics up to date and read the explain-table wisely.

    Originally posted by antodomnic
    How about using joins in db2 running on OS/390 v 6.1. Will it increase the performance?.

Posting Permissions

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