Optimize a query reducing logical reads I have a SQL Server 2016 query that returns 127K rows. You can find the query and query plan here. Let me know if you also need tables structure.

I need to join with a table that has only 20 rows, which acts as a replacement for one of the products. In other words, I query products from a main table but, under certain conditions, some of them can be replaced by others.

Problem is that, for that simple table, I have 254K logical reads. I've tried LEFT JOIN and OUTER APPLY.

Any suggestion about how to replace this to avoid that amount of logical reads? Just to mention, only 1 product has a replacement.
Taking a closer look at the execution plan XML, notice these problematic statistics:

&lt;Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="1328" WaitCount="403"/&gt; 

&lt;QueryTimeStats CpuTime="353" ElapsedTime="1853"/&gt;


The query spent 1.3 seconds waiting on the results to be consumed by the application.  The query only ran for 1.8 seconds total.  So the main problem here is that the application is consuming these 127k results row-by-row.  The query itself runs fairly quickly.

Forrest McDaniel has a good blog post that demonstrates this problem: Two Easy ASYNC_NETWORK_IO Demos

The remainder of the answer addresses the "logical reads" portion of your question.



The reason for all of those logical reads on the OUTER APPLY'd table (DBVAREKT) is here:



The "Index Seek" there is executed once for each row on the upper input to the NESTED LOOPS join.  So there are 127,329 seeks into that index (ID1), even though in the end only 302 matching rows are returned.

The optimizer wouldn't normally choose to do that many seeks into the index, but it only thought there would be 82 rows on the upper input.  Doing 82 seeks is definitely more reasonable.



The general approach to solving this problem would be to avoid doing a NESTED LOOPS join on that particular table, since that is the source of the problem.  To that end, you could use join hints, but it's a little hard for me to tell where the hints should be applied.

Randi mentioned a possible rewrite of the query that would place some of the data into a temp table, essentially breaking the query up into smaller chunks that the SQL Server optimizer can do a better job with.  You could break this up at the OUTER APPLY as follows:

SELECT 
       1756,
       L.MADTYPE,
       DBM.VNR1
INTO #results
FROM dbo.STDORDRE S
INNER JOIN dbo.STDORD STO ON sto.DATO = s.DATO
                           AND sto.KUNDE = s.KUNDE
LEFT JOIN dbo.STDORDML L ON L.ONR = s.ONR
CROSS APPLY (SELECT dbo.MCS_ClarionDateToSQL(D.DATO) SQL_DATO,
                    DATEPART(dw, dbo.MCS_ClarionDateToSQL(D.DATO)) DP,  D.VNR1, D.DATO, D.LINE, D.KATALOGNR, D.VFAKTOR, D.MADTYPE FROM dbo.DBMENU D WHERE D.SNR = s.VARENR AND D.LINE = L.MENULINE) DBM
INNER JOIN dbo.DBKUNDE kun ON kun.NR = s.KUNDE
INNER JOIN dbo.DBKUNGRP dbk ON dbk.NR = kun.GRP
INNER JOIN dbo.DBVARE varm ON varm.NR = s.VARENR
INNER JOIN dbo.DBVARE var ON var.NR = DBM.VNR1
LEFT OUTER JOIN dbo.MENORDRE MEN ON MEN.KUNDE = s.KUNDE
                                AND MEN.DATO  = DBM.DATO
                                AND MEN.LINIE = DBM.LINE
                                AND MEN.NR    = s.MNR
WHERE 1 = 1
  AND ( kun.UDSKREVET = 0
                     OR ( kun.UDSKREVET = 1
                          AND kun.UDSDATO &gt;= 79627 ))
                   AND varm.TYPE = 9
                   AND varm.KPFIX = 0
                   AND s.ML = 1
                   AND DBM.DATO BETWEEN 79627 AND 79777
                   AND sto.TYPE = 1;

SELECT 
       1756,
       L.MADTYPE,
       DBM.VNR1
FROM #results RES  
OUTER APPLY (SELECT MTY.PREC MTY_PREC, 
                    MTY.NR   MTY_NR ,
                    VAR1.PREC VAR1_PREC, 
                    var1.VAR_PKG_ID VAR1_VAR_PKG_ID,
                    VAR1.NR VAR1_NR ,
                    VAR1.TYPE VAR1_TYPE,
                    VAR1.GRP VAR1_GRP
             FROM dbo.DBVAREKT VKT
              LEFT JOIN dbo.DBVARE VAR1 ON VAR1.PREC = VKT.TO_VARE_PREC
                                               AND (ISNULL(VKT.MADTYPE,0) &lt;&gt; 0  )
              LEFT JOIN dbo.DBMTYPE MTY ON MTY.PREC = VKT.TO_MADTYPE_PREC
            WHERE 1 = 1
              AND VKT.MADTYPE = RES.MADTYPE
              AND VKT.VARENR  = RES.VNR1
                      ) OA;