3

I have a simple query that joins two tables on the field PRODID. For some reason, SQL Server opts to use a hash match to join this, but I believe it should choose a merge join, as that field is part of the index, and the preceding fields of both indexes are already used to filter out most of the data. So the next field in both indexes is PRODID, which should be sorted.

The query:

    select JOURNAL.PRODID, JOURNAL.JOURNALID
      from PRODJOURNALTABLE JOURNAL
           inner join PRODROUTEJOB JOB on JOB.PRODID = JOURNAL.PRODID and JOB.DATAAREAID = JOURNAL.DATAAREAID and JOB.PARTITION = JOURNAL.PARTITION
     where JOURNAL.POSTEDDATETIME between '2021/05/01' and '2021/05/10'
       and JOURNAL.POSTED = 1
       and JOURNAL.JOURNALTYPE = 1
       and JOURNAL.DATAAREAID = N'LAN'
       and JOURNAL.PARTITION = 5637144576
       and JOB.WRKCTRID = N'TF1'

The query plan https://www.brentozar.com/pastetheplan/?id=B1oyc8qBh

Query plan

The used indexes

    CREATE NONCLUSTERED INDEX [I_243ROLLERRORVIEWIDX] ON [dbo].[PRODJOURNALTABLE]
    (
        [PARTITION] ASC,
        [DATAAREAID] ASC,
        [JOURNALTYPE] ASC,
        [POSTED] ASC,
        [POSTEDDATETIME] ASC,
        [PRODID] ASC
    )
    INCLUDE([JOURNALID])

    CREATE NONCLUSTERED INDEX [I_258ROLLERRORVIEWIDX] ON [dbo].[PRODROUTEJOB]
    (
        [PARTITION] ASC,
        [DATAAREAID] ASC,
        [WRKCTRID] ASC,
        [PRODID] ASC,
        [OPRNUM] ASC
    )
    INCLUDE([OPRPRIORITY])

EDIT:

Adding the JOIN HINT merge join to the query reveals that it tries to sort the data from the PRODJOURNALTABLE on the field PRODID

    select JOURNAL.PRODID, JOURNAL.JOURNALID
      from PRODJOURNALTABLE JOURNAL
           inner merge join PRODROUTEJOB JOB on JOB.PRODID = JOURNAL.PRODID and JOB.DATAAREAID = JOURNAL.DATAAREAID and JOB.PARTITION = JOURNAL.PARTITION
     where JOURNAL.POSTEDDATETIME between '2021/05/01' and '2021/05/10'
       and JOURNAL.POSTED = 1
       and JOURNAL.JOURNALTYPE = 1
       and JOURNAL.DATAAREAID = N'LAN'
       and JOURNAL.PARTITION = 5637144576
       and JOB.WRKCTRID = N'TF1'

Query plan: https://www.brentozar.com/pastetheplan/?id=rJh1GPqBn

Query plan join hint

But it should be sorted according to the index, no?

1 Answer 1

4

sorta kinda

Index order is maintained for equality predicates, but you have an inequality (range) predicate:

JOURNAL.POSTEDDATETIME between '2021/05/01' and '2021/05/10'

Which results in the sort operator needing to put PRODID in correct order for the merge join:

NUTS

In order to get a merge join plan without a sort, you would need to change your index to this:

CREATE NONCLUSTERED INDEX [I_243ROLLERRORVIEWIDX] 
ON [dbo].[PRODJOURNALTABLE]
(
    [PARTITION] ASC,
    [DATAAREAID] ASC,
    [JOURNALTYPE] ASC,
    [POSTED] ASC,
    [PRODID] ASC,
    [POSTEDDATETIME] ASC
)
INCLUDE([JOURNALID]);

You would no longer get a seek predicate for POSTEDDATETIME, but that may not be of much consequence since you have a number of other predicates at play.

I talk about this here:

2
  • Ah, I actually wondered earlier if the output list in the query plan is in the actual order that it is sorted (or unsorted, but in this case sorted) but could not find any actual info on this.
    – KHP
    May 23, 2023 at 16:46
  • @KHP The output list does get sorted, but the sort order is defined by the order by elements. May 23, 2023 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.