CODEWITHSUNDEEP
sqlsql-serversql-server-2005openquery
Dip
Dip

Reputation: 363

Does the query from the linked server still continue even though you have obtained your result in your SQL Server?

Let's say you have a link server X.

If you were to get 1 row from X in a table (apples) from a schema (fruit). Assuming that the table has a billion rows. 

Select TOP 1 * from openquery(X , 'SELECT * FROM fruit.apples')

Does the query still continue within the openquery statement in the background? or does it terminate once you get that result (SELECT TOP 1)?

Upvotes: 1

Views: 494

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

This is my test query and observations.

select 
--top 1 
*
from openquery(
DW,
'
select *
from visit_fact
where visit_type is not null
'
);

DW points to a redbrick database. It does not handle is null or is not null very well. Those queries run slow. I also have the ability to see the last line of any query currently executing.

The visit_fact table has about 6.5 million rows.

When I run the above query, results start returning in SSMS instantly, but the information at the bottom shows the query to still be running. I can also see where visit_type is not null when I use something else to see what's running. It eventually completed in 5 minutes or so.

When I uncomment top 1, SSMS instantly shows that the query executed successfully and returned one row. When I use the other method to see what's running, I see nothing.

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28920

No,SQLSERVER quits processing more rows as soon as first row is returned..

below is simple test query to demonstrate this

select top 1* from
openquery(testserver,'select * from performancev3.dbo.orders')

Execution plan shows only one row is scanned and returned

enter image description here

SQLSERVER uses a iterative processing model .So query execution starts from root(Select operator) and it asks top operator to return one row and finally top operator asks the statement below it for one row

Upvotes: 1

Related Questions