SQL ServerVideos
354
0

Optimizando consultas en Servidores Vinculados

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

Curso Performance SQL

You must be logged in to post a comment.