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?