August 23, 2012 at 6:37 am
Hi
I understand merge joins as far as why they are quick.
what i dont understand is how to sort the joined fields on the joining tables.
i feel like a bit of a thicko mind
can anyone help
thanks
August 23, 2012 at 7:04 am
If you have a clustered index in the joining fields then you should find the optimiser can omit the sort phase of the execution plan as it can rely on the data being sorted when it reads it using the clustered index.
Having said that this is only one aspect opf many that the optimiser considers in its strategy for choosing the best join method, and there are lots of other factors that may affect your choice of clustered index.
Can you post more on what you are trying to do?
Mike
August 23, 2012 at 7:20 am
well for instance i have a booking table and a attendant table
there is a bookingid in the booking table and a bookingid in the attendant table
i know it is a virtual 1 to one mapping but the attendant table has a primary key on the attendantid and not the bookingid
if i could sort the two tables by bookingid and then merge join i assume that the query would be much faster?
i have no idea how to write the sql that will sort both tables by booking id prior to the join
thanks for the reply and your time
August 23, 2012 at 7:28 am
No problem. Whether it would make much difference to the speed really depnds on how many bookings and how many attendants.
Effectively if you create a clustered index on attenant table on the bookingidf column you will end up with all the bookings for one attendant phsically close to each other, thus reducing disk IO.
But - that could result in lots of index updates and page splits going on when new bookings are taken.
If possible I would get a test copy of the database and experiment - looking at execution plansd and performance stats to make sure the change was worth it.
Mike
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply