SQL server chooses hash match over merge join but the input fields to join should be sorted

3 votes

1 answer

315 views

sql-server performance-tuning microsoft-dynamics

                          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 




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 



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

                        

Asked by KHP (65 rep)

May 23, 2023, 03:37 PM
Last activity: May 23, 2023, 04:49 PM

SQL server chooses hash match over merge join but the input fields to join should be sorted

Related Questions