Muchos programadores o DBA utilizan servidores vinculados en Microsoft SQL.
En este video tutorial te voy a mostrar cómo podes optimizar esas consultas y que cuidados debes tener.
Código de ejemplo
SQL
USE ADVENTUREWORKS2022
GO
SELECT * FROM [MAXI-PC].ADVENTUREWORKS2022.SALES.salesorderheader
option (recompile)
SELECT * FROM Sales.SalesOrderHeader option (recompile)
-- REMOTO
SELECT * FROM sales.Customer c
INNER JOIN [MAXI-PC].ADVENTUREWORKS2022.SALES.salesorderheader h
ON
C.CUSTOMERID = H.CUSTOMERID
option (recompile)
-- LOCAL
SELECT * FROM SALES.CUSTOMER C
INNER JOIN SALES.SALESORDERHEADER H
ON
C.CUSTOMERID = H.CUSTOMERID
option (recompile)
-- OPENQUERY
SELECT * FROM OPENQUERY ([MAXI-PC], 'SELECT * FROM ADVENTUREWORKS2022.SALES.salesorderheader')
option (recompile)
SELECT * FROM [MAXI-PC].ADVENTUREWORKS2022.SALES.salesorderheader option (recompile)
-- OPENQUERY WHERE poner profiler
SELECT * FROM OPENQUERY
([MAXI-PC], 'SELECT * FROM ADVENTUREWORKS2022.SALES.salesorderheader where salesorderid=43659') option (recompile)
SELECT * FROM [MAXI-PC].ADVENTUREWORKS2022.SALES.salesorderheader where salesorderid=43659 option (recompile)
--- joins
WITH h AS
(
SELECT * FROM OPENQUERY
([MAXI-PC], 'SELECT * FROM ADVENTUREWORKS2022.SALES.salesorderheader where customerid=11091')
)
SELECT * FROM h
INNER JOIN SALES.Customer c
ON
C.CUSTOMERID = H.CUSTOMERID
option (recompile)
SELECT * FROM [MAXI-PC].ADVENTUREWORKS2022.SALES.salesorderheader h
INNER JOIN
sales.Customer c
ON
C.CUSTOMERID = H.CUSTOMERID
where h.CustomerID = 11091
option (recompile)
Enlaces adicionales
OPENQUERY (Transact-SQL) – SQL Server | Microsoft Learn
Techniques to enhance Linked Server performance – Microsoft Community Hub
