SQL-Server - Performance tuning - SELECT - ORDER BY

Question

Technical_Details.zip.txt
Hello colleagues, could you please help me understand the following situation? :

Why does the "ORDER BY" clause slow down the following SQL-query?:

SELECT TOP (@0) ISNULL("Function Ledger Entry"."timestamp", @2) AS "timestamp", ISNULL("Function Ledger Entry"."Entry No_", @3) AS "Entry No_", ISNULL("Function Ledger Entry"."Journal Batch Name", @4) AS "Journal Batch Name", ISNULL("Function Ledger Entry"."Contract Type", @5) AS "Contract Type", ISNULL("Function Ledger Entry"."Contract No_", @4) AS "Contract No_", ISNULL("Function Ledger Entry"."Contract Line No_", @6) AS "Contract Line No_", ISNULL("Function Ledger Entry"."Service Item No_", @4) AS "Service Item No_", ISNULL("Function Ledger Entry"."Function Code", @4) AS "Function Code", ISNULL("Function Ledger Entry"."Counter Code", @4) AS "Counter Code", ISNULL("Function Ledger Entry"."Entry Date", @7) AS "Entry Date", ISNULL("Function Ledger Entry"."Posting Date", @7) AS "Posting Date", ISNULL("Function Ledger Entry"."Too Many Prepaid Inv_ Units", @5) AS "Too Many Prepaid Inv_ Units", ISNULL("Function Ledger Entry"."Document No_", @4) AS "Document No_", ISNULL("Function Ledger Entry"."Document Type", @5) AS "Document Type", ISNULL("Function Ledger Entry"."Entry Type", @5) AS "Entry Type", ISNULL("Function Ledger Entry"."Description", @8) AS "Description", ISNULL("Function Ledger Entry"."Description 2", @8) AS "Description 2", ISNULL("Function Ledger Entry"."Open", @9) AS "Open", ISNULL("Function Ledger Entry"."External Document No_", @4) AS "External Document No_", ISNULL("Function Ledger Entry"."Counter Ledger Entry No_", @3) AS "Counter Ledger Entry No_", ISNULL("Function Ledger Entry"."Source Code", @4) AS "Source Code", ISNULL("Function Ledger Entry"."Reason Code", @4) AS "Reason Code", ISNULL("Function Ledger Entry"."Forecast", @9) AS "Forecast", ISNULL("Function Ledger Entry"."Closed at Date", @7) AS "Closed at Date", ISNULL("Function Ledger Entry"."Closed by Entry No_", @3) AS "Closed by Entry No_", ISNULL("Function Ledger Entry"."User ID", @4) AS "User ID", ISNULL("Function Ledger Entry"."Original Qty", @10) AS "Original Qty", ISNULL("Function Ledger Entry"."Used Lifetime per Unit", @6) AS "Used Lifetime per Unit", ISNULL("Function Ledger Entry"."Closed by Units", @10) AS "Closed by Units", ISNULL("Function Ledger Entry"."Applies-to ID", @4) AS "Applies-to ID", ISNULL("Function Ledger Entry"."Units to Apply", @10) AS "Units to Apply", ISNULL("Function Ledger Entry"."Applies-to Doc_ Type", @5) AS "Applies-to Doc_ Type", ISNULL("Function Ledger Entry"."Applies-to Doc_ No_", @4) AS "Applies-to Doc_ No_", ISNULL("Function Ledger Entry"."Applying Entry", @9) AS "Applying Entry", ISNULL("Function Ledger Entry"."Posted Doc_ Function Entry ID", @11) AS "Posted Doc_ Function Entry ID", ISNULL("Function Ledger Entry"."Unit Price (LCY)", @10) AS "Unit Price (LCY)", ISNULL("Function Ledger Entry"."Amount (LCY)", @10) AS "Amount (LCY)", ISNULL("Function Ledger Entry"."Discount _", @10) AS "Discount _", ISNULL("Function Ledger Entry"."Discount Amount", @10) AS "Discount Amount", ISNULL("Function Ledger Entry"."Invoiceable", @9) AS "Invoiceable", ISNULL("Function Ledger Entry"."Counter Entry Type", @5) AS "Counter Entry Type", ISNULL("Function Ledger Entry"."Copy Kit Qty_", @6) AS "Copy Kit Qty_", ISNULL("Function Ledger Entry"."Copy Kit State", @10) AS "Copy Kit State", ISNULL("Function Ledger Entry"."Coverage _", @10) AS "Coverage _", ISNULL("Function Ledger Entry"."Amount", @10) AS "Amount", ISNULL("Function Ledger Entry"."Unit Price", @10) AS "Unit Price", ISNULL("Function Ledger Entry"."Currency Code", @4) AS "Currency Code", ISNULL("Function Ledger Entry"."Contract Currency Code", @4) AS "Contract Currency Code", ISNULL("Function Ledger Entry"."Contract Curr_ Factor Inv_Post", @10) AS "Contract Curr_ Factor Inv_Post", ISNULL("SUB$Reading Date"."Reading Date$Counter Ledger Entry$LOOKUP$Reading Date", @7) AS "Reading Date", ISNULL("SUB$Reading Time"."Reading Time$Counter Ledger Entry$LOOKUP$Reading Time", @14) AS "Reading Time", ISNULL("SUB$Counter State"."Counter State$Counter Ledger Entry$LOOKUP$Counter State", @10) AS "Counter State", ISNULL("SUB$Remaining Units"."Remaining Units$Detailed Function Ledg_ Entry$SUM$Units", @10) AS "Remaining Units", ISNULL("SUB$Original Units"."Original Units$Detailed Function Ledg_ Entry$SUM$Units", @10) AS "Original Units", ISNULL("SUB$Counter Source"."Counter Source$Counter Ledger Entry$LOOKUP$Counter Source", @5) AS "Counter Source", ISNULL("SUB$Counter Entry Jnl_ Batch Name"."Counter Entry Jnl_ Batch Name$Counter Ledger Entry$LOOKUP$Journal Batch Name", @4) AS "Counter Entry Jnl_ Batch Name", ISNULL("SUB$Counter Entry Change by ID"."Counter Entry Change by ID$Counter Ledger Entry$LOOKUP$Change by ID", @4) AS "Counter Entry Change by ID", ISNULL("SUB$Counter Entry Posted by ID"."Counter Entry Posted by ID$Counter Ledger Entry$LOOKUP$Posted by ID", @4) AS "Counter Entry Posted by ID"FROM [BQA].[dbo].[UAT$Function Ledger Entry] AS "Function Ledger Entry" WITH (READUNCOMMITTED)OUTER APPLY (	SELECT TOP (@12) ISNULL("Reading Date$Counter Ledger Entry"."Reading Date", @7) AS "Reading Date$Counter Ledger Entry$LOOKUP$Reading Date"	, ISNULL("Reading Date$Counter Ledger Entry"."Entry No_", @3) AS "Reading Date$Counter Ledger Entry$Entry No_"	FROM [BQA].[dbo].[UAT$Counter Ledger Entry] AS "Reading Date$Counter Ledger Entry" WITH (READUNCOMMITTED)	WHERE ("Reading Date$Counter Ledger Entry"."Entry No_" = "Function Ledger Entry"."Counter Ledger Entry No_")	ORDER BY "Reading Date$Counter Ledger Entry$Entry No_" ASC	) AS "SUB$Reading Date"OUTER APPLY (	SELECT TOP (@13) ISNULL("Reading Time$Counter Ledger Entry"."Reading Time", @14) AS "Reading Time$Counter Ledger Entry$LOOKUP$Reading Time"	, ISNULL("Reading Time$Counter Ledger Entry"."Entry No_", @3) AS "Reading Time$Counter Ledger Entry$Entry No_"	FROM [BQA].[dbo].[UAT$Counter Ledger Entry] AS "Reading Time$Counter Ledger Entry" WITH (READUNCOMMITTED)	WHERE ("Reading Time$Counter Ledger Entry"."Entry No_" = "Function Ledger Entry"."Counter Ledger Entry No_")	ORDER BY "Reading Time$Counter Ledger Entry$Entry No_" ASC	) AS "SUB$Reading Time"OUTER APPLY (	SELECT TOP (@15) ISNULL("Counter State$Counter Ledger Entry"."Counter State", @10) AS "Counter State$Counter Ledger Entry$LOOKUP$Counter State"	, ISNULL("Counter State$Counter Ledger Entry"."Entry No_", @3) AS "Counter State$Counter Ledger Entry$Entry No_"	FROM [BQA].[dbo].[UAT$Counter Ledger Entry] AS "Counter State$Counter Ledger Entry" WITH (READUNCOMMITTED)	WHERE ("Counter State$Counter Ledger Entry"."Entry No_" = "Function Ledger Entry"."Counter Ledger Entry No_")	ORDER BY "Counter State$Counter Ledger Entry$Entry No_" ASC	) AS "SUB$Counter State"OUTER APPLY (	SELECT TOP (@16) ISNULL(SUM("Remaining Units$Detailed Function Ledg_ Entry"."Units"), @10) AS "Remaining Units$Detailed Function Ledg_ Entry$SUM$Units"	FROM [BQA].[dbo].[UAT$Detailed Function Ledg_ Entry] AS "Remaining Units$Detailed Function Ledg_ Entry" WITH (READUNCOMMITTED)	WHERE ("Remaining Units$Detailed Function Ledg_ Entry"."Function Ledg_ Entry No_" = "Function Ledger Entry"."Entry No_")	) AS "SUB$Remaining Units"OUTER APPLY (	SELECT TOP (@17) ISNULL(SUM("Original Units$Detailed Function Ledg_ Entry"."Units"), @10) AS "Original Units$Detailed Function Ledg_ Entry$SUM$Units"	FROM [BQA].[dbo].[UAT$Detailed Function Ledg_ Entry] AS "Original Units$Detailed Function Ledg_ Entry" WITH (READUNCOMMITTED)	WHERE (			"Original Units$Detailed Function Ledg_ Entry"."Function Ledg_ Entry No_" = "Function Ledger Entry"."Entry No_"			AND ISNULL("Original Units$Detailed Function Ledg_ Entry"."Entry Type", @5) = @18			)	) AS "SUB$Original Units"OUTER APPLY (	SELECT TOP (@19) ISNULL("Counter Source$Counter Ledger Entry"."Counter Source", @5) AS "Counter Source$Counter Ledger Entry$LOOKUP$Counter Source"	, ISNULL("Counter Source$Counter Ledger Entry"."Entry No_", @3) AS "Counter Source$Counter Ledger Entry$Entry No_"	FROM [BQA].[dbo].[UAT$Counter Ledger Entry] AS "Counter Source$Counter Ledger Entry" WITH (READUNCOMMITTED)	WHERE ("Counter Source$Counter Ledger Entry"."Entry No_" = "Function Ledger Entry"."Counter Ledger Entry No_")	ORDER BY "Counter Source$Counter Ledger Entry$Entry No_" ASC	) AS "SUB$Counter Source"OUTER APPLY (	SELECT TOP (@20) ISNULL("Counter Entry Jnl_ Batch Name$Counter Ledger Entry"."Journal Batch Name", @4) AS "Counter Entry Jnl_ Batch Name$Counter Ledger Entry$LOOKUP$Journal Batch Name"	, ISNULL("Counter Entry Jnl_ Batch Name$Counter Ledger Entry"."Entry No_", @3) AS "Counter Entry Jnl_ Batch Name$Counter Ledger Entry$Entry No_"	FROM [BQA].[dbo].[UAT$Counter Ledger Entry] AS "Counter Entry Jnl_ Batch Name$Counter Ledger Entry" WITH (READUNCOMMITTED)	WHERE ("Counter Entry Jnl_ Batch Name$Counter Ledger Entry"."Entry No_" = "Function Ledger Entry"."Counter Ledger Entry No_")	ORDER BY "Counter Entry Jnl_ Batch Name$Counter Ledger Entry$Entry No_" ASC	) AS "SUB$Counter Entry Jnl_ Batch Name"OUTER APPLY (	SELECT TOP (@21) ISNULL("Counter Entry Change by ID$Counter Ledger Entry"."Change by ID", @4) AS "Counter Entry Change by ID$Counter Ledger Entry$LOOKUP$Change by ID"	, ISNULL("Counter Entry Change by ID$Counter Ledger Entry"."Entry No_", @3) AS "Counter Entry Change by ID$Counter Ledger Entry$Entry No_"	FROM [BQA].[dbo].[UAT$Counter Ledger Entry] AS "Counter Entry Change by ID$Counter Ledger Entry" WITH (READUNCOMMITTED)	WHERE ("Counter Entry Change by ID$Counter Ledger Entry"."Entry No_" = "Function Ledger Entry"."Counter Ledger Entry No_")	ORDER BY "Counter Entry Change by ID$Counter Ledger Entry$Entry No_" ASC	) AS "SUB$Counter Entry Change by ID"OUTER APPLY (	SELECT TOP (@22) ISNULL("Counter Entry Posted by ID$Counter Ledger Entry"."Posted by ID", @4) AS "Counter Entry Posted by ID$Counter Ledger Entry$LOOKUP$Posted by ID"	, ISNULL("Counter Entry Posted by ID$Counter Ledger Entry"."Entry No_", @3) AS "Counter Entry Posted by ID$Counter Ledger Entry$Entry No_"	FROM [BQA].[dbo].[UAT$Counter Ledger Entry] AS "Counter Entry Posted by ID$Counter Ledger Entry" WITH (READUNCOMMITTED)	WHERE ("Counter Entry Posted by ID$Counter Ledger Entry"."Entry No_" = "Function Ledger Entry"."Counter Ledger Entry No_")	ORDER BY "Counter Entry Posted by ID$Counter Ledger Entry$Entry No_" ASC	) AS "SUB$Counter Entry Posted by ID"WHERE ("Function Ledger Entry"."Applies-to ID" = @1)ORDER BY "Function Code" ASC, "Applies-to ID" ASC, "Open" ASC, "Entry No_" ASCOPTION (OPTIMIZE FOR UNKNOWN, FAST 50);

Because it returns only 3 rows (every time).
I am asking this question, because during one of my educations/trainings I have got information, that "ORDER BY" operation is the last operation in “Execution plan”. Is it true?

I ask this question because I learned (in one of my training courses) that the "ORDER BY" operation is the last operation in the "Execution plan". Is this true?

If you need more technical details, you can find it in attached file.

Answer 1

Let’s say you have a million rows. If you Select you select top(3) the server only needs to read three rows. But if you Order by, then the server needs to sort a million rows, to know which are the proper first three rows.

you can improve order by performance is the there is an index that covers the order by columns.

Answer 2

I am asking this question, because during one of my educations/trainings I have got information, that "ORDER BY" operation is the last operation in “Execution plan”. Is it true?

No, ORDER BY is one of the last operations in the logical processing order. TOP is the last. The logical order should not be confused with the actual order of the logical and physical execution plan operators.

I ask this question because I learned (in one of my training courses) that the "ORDER BY" operation is the last operation in the "Execution plan". Is this true?

An execution plan can implement the logical processing order in many different ways as long as the query semantics are preserved. When ORDER BY is specified, the plan may include an ordered scan of a suitable index (if one exists) or use a SORT physical operator. The optimizer will decide which is most efficient and choose the appropriate plan. Consequently, the order of execution plan operators may differ from the logical processing order.

Be aware that TOP without ORDER BY should be used with caution since the same query may return different results depending on factors like indexes, transaction isolation level, parallelism, and the overall plan shape.

Answer 3

Hi @David Kříž

The above answers have explained the problem in great detail.

I want to add that you can actually do a test, remove the order by, and see if the result is what you want. This may be more intuitive to understand.

Best regards,

Percy Tang