Solve HASH MATCH in query plan

  • Hi,

    This is SQL Server 2016, by the way.

    I'm trying to optimize a query plan, especially attacking a HASH MATCH (Inner Join) with a high cost. Query is here: https://www.brentozar.com/pastetheplan/?id=HkJMYBALB.

    For this query, statistics indicate:

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    (127329 rows affected)

    Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'DBMENU'. Scan count 1, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'STDORDML'. Scan count 25, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'STDORD'. Scan count 0, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'DBKUNGRP'. Scan count 0, logical reads 50, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'DBKUNDE'. Scan count 0, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'DBVARE'. Scan count 1, logical reads 135, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'STDORDRE'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row affected)

    SQL Server Execution Times:

    CPU time = 422 ms, elapsed time = 4347 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

     

    Beside that, I have another question. In the previous I have some commented lines, regarding some LEFT JOINs. If I add those lines to the query, now statistics change to:

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    (127329 rows affected)

    Table 'DBVAREKT'. Scan count 1, logical reads 254659, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'DBMENU'. Scan count 1, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'STDORDML'. Scan count 25, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'STDORD'. Scan count 0, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'DBKUNGRP'. Scan count 0, logical reads 50, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'DBKUNDE'. Scan count 0, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'DBVARE'. Scan count 1, logical reads 135, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'STDORDRE'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row affected)

    SQL Server Execution Times:

    CPU time = 891 ms, elapsed time = 3381 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    As you can see, the amount of logical reads for the table DBVAREKT is huge, even when table has only 20 records in this database. I've tried to reduce that number but I couldn't find a way. DBVAREKT is used only to join with the other 2 tables (to be a more clear, what that table has are replacements for some products based on food type).

    Query and plan are here: https://www.brentozar.com/pastetheplan/?id=rJH4aSCLr

    Is there any other way to write this query and make it more efficient?

    Thanks in advance.

     

    Mauricio

     

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply