principlesqlMinor
SQL Server OPENQUERY vs Regular Query against AS400
Viewed 0 times
sqlqueryregularas400openqueryagainstserver
Problem
I log into a SQL Server database which has an AS400 server as a linked server.
I've discovered that running queries against this AS400 server is MUCH faster if I run the select in an OPENQUERY...
Execution time: 46 seconds
Execution time: 1 second
Can someone explain or link me to why/how this is happening?
I've discovered that running queries against this AS400 server is MUCH faster if I run the select in an OPENQUERY...
SELECT EMEQP#
FROM _
WHERE EMALOC = '467' AND EMDLCD = 'A' AND EMSTAT NOT IN ('S', 'J', 'V')Execution time: 46 seconds
SELECT EMEQP#
FROM
OPENQUERY(_, '
SELECT EMEQP#
FROM _
WHERE EMALOC = ''467'' AND EMDLCD = ''A'' AND EMSTAT NOT IN (''S'', ''J'', ''V'')
')Execution time: 1 second
Can someone explain or link me to why/how this is happening?
Solution
When you use
http://social.msdn.microsoft.com/Forums/eu/transactsql/thread/0e68f512-1e19-4c50-b343-219085d70076
openquery the query itself executed on remote server and you receive only results. In case of linked server, you local server does all the job. You may want to check http://social.msdn.microsoft.com/Forums/eu/transactsql/thread/0e68f512-1e19-4c50-b343-219085d70076
Context
StackExchange Database Administrators Q#33091, answer score: 5
Revisions (0)
No revisions yet.