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
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:
<Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="1328" WaitCount="403"/> <QueryTimeStats CpuTime="353" ElapsedTime="1853"/>
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 >= 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) <> 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;