All geek questions in one place

SQL Server: Table reel when adding ORDER BY

I have a very simple query that I'm trying to optimize. issues_notes is a simple table, and meta_users is a table on a remote database server that I view with a view.

when I run a query without an order, it returns immediately, but when I add ORDER BY date , it takes about 4 seconds to return. I have SQL Server showing the execution plan, and it seems that slowness is introduced in the Table Spool operation, which occurs only when connected. Is there a way to prevent this “optimization”?

Query:

 SELECT [issues_notes].[date], [meta_users].[firstname], [meta_users].[lastname], [issues_notes].[note] FROM [issues_notes] LEFT JOIN [issues_issue] ON ([issues_notes].[issue_id] = [issues_issue].[id]) LEFT OUTER JOIN [meta_users] ON ([issues_notes].[author_id] = [meta_users].[userid]) WHERE ([issues_issue].[issue_hash] = '%s' )

Execution plan without order By:

enter image description here

Execution plan with order:

enter image description here

+4
source share
1 answer

The dining reel is the Lazy Spool. This means that he remembers the lines that he saw, but does not do any extra work. Since the lines to the left of the connection are now sorted, duplicate lines with the same value will be displayed immediately after each other. The satellite allows you to simply reuse them, rather than delete the remote server again.

Sorting itself is a blocking statement. This means that it will save all rows before sorting and return them only after sorting is complete.

How many lines are we talking here? What is the total execution time of both queries?

+2
source

Source: https://habr.com/ru/post/1438952/

More articles:


All Articles