How to improve SQL Server performance Select a query?

The following T-SQL query takes 54 seconds:

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

While the same query, sorted in descending order, is executed 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 query performance for both 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)

Link to the implementation plan in xml format:

+3
source share
2 answers

table2 has 32,582,700 lines.

The estimated plan is as follows.

enter image description here

non-cover RecvdDate, , source = '8R'.

SQL Server , 22 -23 , TOP 3 .

, , 8R, . , . 19 928 681 ( STATISTICS PROFILE , ), 20.

- source, RecvdDate. , , . .

source asc, RecvdDate asc
source asc, RecvdDate desc
source desc, RecvdDate asc
source desc, RecvdDate desc

- source , RecvdDate ASC RecvdDate DESC.

+2

? ,

source, RecvdDate DESC

, RecvdDate .

source RecvdDate, ( , ) RecvdDate .

0

All Articles