Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2015

    Unanswered: Joins SIGNIFICANTLY slower than individual queries

    Hi all, I just registered here and I hope I'm posting this in the proper forum.

    I am trying to pull about 100 000 rows from two HUGE tables (hundreds of millions of rows, db2). When I query each table individually the queries finish in less than 20 seconds. However, when I left/inner join on the same (indexed)columns, the query doesn't finish in 2000 seconds.

    What could be causing this? Some kind of issue with the engine? Bad DB design? Im not db savvy at all so any pointers would be appreciated.

  2. #2
    Join Date
    Apr 2012
    Provided Answers: 25
    Can be several reasons for this. Not enough facts in your post to give an opinion.

    Datatypes+lengths of the join columns, nature of statistics collected, whether statistical-views are present, registry variables, nature of the predicates and query, DB2-server version and fixpack etc. etc.

    Find the detailed access-plan for the join-query that shows the optimized query and all the costs etc.

    Use either a GUI or a command-line tool for this purpose (such as db2exfmt, which gives pure text output that you can attach to a post). The docs tell you how to create the explain tables.

  3. #3
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    One more item that should be checked, are you joining on the entire key between the tables or do you have some type of partial Cartesian product.

  4. #4
    Join Date
    May 2012
    Canberra, Australia
    Provided Answers: 6
    Run the join query through db2advisor to get recommendations on the indexing etc.

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

Posting Permissions

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