Sql-server – SQL Server OPENQUERY vs Regular Query against AS400

iserieslinked-serversql server

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?

Best Answer

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

Related Solutions

Sql-server – SQL Server update query on linked server causing remote scan

A friend just had a similar problem and rewriting the query solved it.

I know this is an old question, and likely you have worked it out, but for anyone else you could try this and report back...

UPDATE kg
SET 
   IdGarment = IdGarment
   FROM Products p 
   INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID 
   INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID 
   INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID 
   INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment 
   INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID 
   WHERE log.ActionType = 'I' ;

I don't know why this helped, but using the UPDATE "alias" form of the statement meant that the whole UPDATE query was remoted, rather than pulling the data back to the local instance to perform the JOINs.

If anyone has any ideas why this behaviour was seen, I would love to know.

Sql-server – Linked Server Between SQL Server and iSeries is rounding decimals

Figured it out!!

Apparently we didn't have the latest service pack installed. We had to install the 64bit version of the service pack (32bit version failed to install), and when it finished the decimal numbers were coming across.

EDIT: In case anyone was wondering:
We're using "IBM iAccess for Windows 7.1 (07.01.0800) (64bit)" and using "Microsoft OLE DB Provider for ODBC Drivers" as the linked server sql driver.

EDIT 2017: Seeing as I just ran into this problem again and had to hunt down the service pack again, here is the link to the service pack download page, so that in a few years when I run into this problem again I will find it easier...

http://www-03.ibm.com/systems/power/software/i/access/windows_sp.html

Best Answer

Related Solutions

Sql-server – SQL Server update query on linked server causing remote scan

Sql-server – Linked Server Between SQL Server and iSeries is rounding decimals

Related Question