Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Post Unanswered: Merge Join = 99%

    I have got a query in which a merge join is 99% of the cost .... and I am confused ... is not merge join supposed to be the fastest ??? Anyone seen this before ???

    Any ideas why this could be happening ... and sorry ... do not ask me to post the code coz I will not be able to ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    just because it is 99% of the cost that does not indicate that it was excessive. it just means that out of 100% of total cost required to run the query, (parsing resolving compiling (serial and parallel plans) and execution)
    that the execution was 99% of the total operation. this indicates that figuring it out isnt taking a long time but doing it is.

    now I believe that when you say 99% of the cost you mean in the Graphical execution plan, then that is still justifiable considering the amount of work that sql has to do to perform a dual sorted join.

    sometimes if a merge join is warranted, sql server will sort unsorted columns because the optimizer feels that the merge join is more beneficial to the query than the cost of not performing a merge join.

    if one of the columns involved is a foriegn key and it is not indexed you might want to consider a nonclustered index on this column.
    this will presort the data in the column and speed up the merge.
    the Graphic EX will probably still indicate the 99% (of the process time)but your IO and or execution time might decrease.

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    There is a non clustered index on the columns ... but the optimizer is choosing the clustered index ... and the merge join is taking 5 minutes ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Is the optimizer scanning the clustered index, or doing a seek? Also, how is the where clause on this pig? If you can eliminate "OR"s or "(NOT) IN"s, you will probably do better.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    The query is on a big fat b#$%%rd table of about 11 million rows ...

    Should i take the table with all its where clauses into another temp table and then do a join ... currently I am using a derived table ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about the DDL and the DML...skip the sample data...

    Don't forget to script the indexes
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Just read up on "Understanding Merge Joins" in BOL. Sounds like in your case, the data is not sorted on the merge keys going into the query. Also, the fact that you are dealing with millions of rows. I have only seen the merge join a few times, but every time has been with 1 million rows or more.

    Just for grins (because I doubt it will work), what is the result of throwing an order by on the subquery? The order by should be the same order as the join, and ideally there should be an index on the table you are joining to with that same order.

    And this is just the thing I would need to have someone else look at myself, because you never know what is going to jump out at a different pair of eyes.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would avoid the ORDER BY

    It's more overhead...until you get the final result (which is how big again?)

    It's either going to use the index or it's not.

    Where's the driver data coming from, since your doing a merge...

    And it will have to scan the driver.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Another pair of eyes .. i am not gonna get .... am working at client's place all alone on my laptop ... away from my office ... and as I said before in the YAK Corral... that Shark tank story looks a lot like mine ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Enigma
    Another pair of eyes .. i am not gonna get
    Dude we are your eyes....

    Can't you post it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Just my luck ... am under a strict NDA
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    So am I...just under a completely unrelated project ;-).

    As for the sort in the subquery, it is just a guess based on the description of how a merge jin works. It may not work, but it sounds like this is getting down to the "Hail Mary" point.

  13. #13
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Dude we are your eyes....
    Anyway ... Thanks for the nice words ...

    What do you think would give better performance under the conditions ...
    a) Temp table
    b) Table variable
    c) Derived table

    The table contains data for an year ... the selection is for a month ... and then have to make a join on a different col from another big fat table containing about 80,000 records ....
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's ALL about the indexes...

    Get the result set as small as possible....

    how many rows in a month of data..if it's not too big you could use a table datatype...

    I still like derived tables

    In the predicate...is there an index for every column..are they in the right order?


    How many columns in the orw?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    How many rows in option 1, 2, or 3?

Posting Permissions

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