5

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...

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?

5

When you use 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

  • Glad to hear this is totally unrelated to OPNQRYF. – WarrenT Jan 17 '13 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.