The following T-SQL query is taking 54 seconds to execute:
SELECT top (3)
a.c1, b.c2, c.c3, d.c4
FROM
table1 as a WITH (NOLOCK)
JOIN
table2 as b WITH (NOLOCK) ON a.c1 = b.c4
LEFT JOIN
table3 as c WITH (NOLOCK) ON a.c1 = c.c4
LEFT JOIN
table4 as d WITH (NOLOCK) ON b.c3 = d.c1
WHERE
b.source = '8R'
ORDER BY
b.RecvdDate ASC
Whereas the same query sorted in descending order execute within a second
SELECT top (3)
a.c1, b.c2, c.c3, d.c4
FROM
table1 as a WITH (NOLOCK)
JOIN
table2 as b WITH (NOLOCK) ON a.c1 = b.c4
LEFT JOIN
table3 as c WITH (NOLOCK) ON a.c1 = c.c4
LEFT JOIN
table4 as d WITH (NOLOCK) ON b.c3 = d.c1
WHERE
b.source = '8R'
ORDER BY
b.RecvdDate DESC
So how can I improve the performance of the query for both the sort orders?
I am using Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: )
Execution Plan using SET STATISTICS PROFILE ON as follows:
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ---------------------------------------------------------------- -------- ------------------
3 1 SELECT top 3 a.c1, b.c2, c.c3, d.c4
FROM table1 as a WITH (NOLOCK) JOIN table2 as b WITH (NOLOCK) ON a.c1 = b.c1 LEFT JOIN table3 as c WITH (NOLOCK) ON 1 1 0 NULL NULL NULL NULL 3 NULL NULL NULL 0.1093194 NULL NULL SELECT 0 NULL
3 1 |--Top(TOP EXPRESSION:((3))) 1 2 1 Top Top TOP EXPRESSION:((3)) NULL 3 0 3E-07 93 0.1093194 [a].[c1], [table2].[c2], [c].[c3], [d].[c4] NULL PLAN_ROW 0 1
3 1 |--Nested Loops(Left Outer Join, WHERE:([DB].[dbo].[table3].[c3] as [table3].[c3]=[DB].[dbo].[table4].[c1] as [d].[c1])) 1 3 2 Nested Loops Left Outer Join WHERE:([DB].[dbo].[table2].[c3] as [table2].[c3]=[DB].[dbo].[table4].[id] as [d].[id]) NULL 3 0 221.8893 101 0.1093191 [a].[c1], [table2].[c2], [table2].[RecvdDate], [c].[c3], [d].[c4] NULL PLAN_ROW 0 1
3 1 |--Nested Loops(Left Outer Join, OUTER REFERENCES:([a].[c1], [Expr1012]) WITH ORDERED PREFETCH) 1 4 3 Nested Loops Left Outer Join OUTER REFERENCES:([a].[c1], [Expr1012]) WITH ORDERED PREFETCH NULL 3 0 18.49077 99 0.1057252 [a].[c1], [table2].[c2], [table2].[RecvdDate], [table2].[C3], [c].[c3] NULL PLAN_ROW 0 1
3 1 | |--Nested Loops(Inner Join, OUTER REFERENCES:([table2].[c4], [Expr1011]) WITH ORDERED PREFETCH) 1 6 4 Nested Loops Inner Join OUTER REFERENCES:([table2].[c4], [Expr1011]) WITH ORDERED PREFETCH NULL 3 0 18.78465 31 0.09258068 [a].[c1], [table2].[c2], [table2].[RecvdDate], [table2].[C3] NULL PLAN_ROW 0 1
27 1 | | |--Nested Loops(Inner Join, OUTER REFERENCES:([table2].[c1], [Expr1010]) WITH ORDERED PREFETCH) 1 8 6 Nested Loops Inner Join OUTER REFERENCES:([table2].[c1], [Expr1010]) WITH ORDERED PREFETCH NULL 22.09678 0 136.1955 41 0.07926979 [table2].[c4], [table2].[c2], [table2].[RecvdDate], [table2].[C3] NULL PLAN_ROW 0 1
19928681 1 | | | |--Index Scan(OBJECT:([DB].[dbo].[table2].[IX_datereceived] AS [table2]), ORDERED BACKWARD) 1 10 8 Index Scan Index Scan OBJECT:([DB].[dbo].[table2].[IX_datereceived] AS [table2]), ORDERED BACKWARD [table2].[c1], [table2].[RecvdDate] 22.09678 66.78609 35.84109 19 0.003349548 [table2].[c1], [table2].[RecvdDate] NULL PLAN_ROW 0 1
27 19928681 | | | |--Clustered Index Seek(OBJECT:([DB].[dbo].[table2].[PK_c1] AS [table2]), SEEK:([table2].[c1]=[DB].[dbo].[table2].[c1] as [table2].[c1]), 1 12 8 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[table2].[PK_c1] AS [table2]), SEEK:([table2].[c1]=[DB].[dbo].[table2].[c1] as [table2].[c1]), WHERE:([DB].[dbo].[table2].[Source] [table2].[c4], [table2].[c2], [table2].[C3] 3.047679 0.003125 0.0001581 77 0.07582787 [table2].[c4], [table2].[c2], [table2].[C3] NULL PLAN_ROW 0 23.09678
3 3 | | |--Index Seek(OBJECT:([DB].[dbo].[table1].[PK_c1] AS [a]), SEEK:([a].[c1]=[DB].[dbo].[table2].[c4] as [table2].[c4]) ORDERED FORWARD) 1 20 6 Index Seek Index Seek OBJECT:([DB].[dbo].[table1].[PK_c1] AS [a]), SEEK:([a].[c1]=[DB].[dbo].[table2].[c4] as [table2].[c4]) ORDERED FORWARD [a].[c1] 1 0.003125 0.0001581 11 0.02297952 [a].[c1] NULL PLAN_ROW 0 7.000085
3 3 | |--Clustered Index Seek(OBJECT:([DB].[dbo].[table3].[table3_PK] AS [c]), SEEK:([c].[c4]=[DB].[dbo].[table1].[c1] as [a].[c1]) ORDERED FORWARD) 1 21 4 Clustered Index Seek Clustered Index Seek OBJECT:([DB].[dbo].[table3].[table3_PK] AS [c]), SEEK:([c].[c4]=[DB].[dbo].[table1].[c1] as [a].[c1]) ORDERED FORWARD [c].[c3] 1 0.003125 0.0001581 75 0.01313197 [c].[c3] NULL PLAN_ROW 0 4
36 3 |--Table Scan(OBJECT:([DB].[dbo].[table4] AS [d])) 1 22 3 Table Scan Table Scan OBJECT:([DB].[dbo].[table4] AS [d]) [d].[id], [d].[c4] 12 0.0032035 9.17E-05 17 0.0035703 [d].[id], [d].[c4] NO STATS:([table4].[c1]) PLAN_ROW 0 3.999999
(11 row(s) affected)
