sql-server - 加入链接服务器表时提高查询性能

Question

不幸的是，作为我们修复数据中心的临时措施，我需要运行以下跨服务器查询，任何人都可以建议我如何提高性能...

因为必须在游标的开头运行此语句

SELECT t.SearchId, t.VisitSourceId, t.SiteDomainId, t.trpUTMid, t.FlightPlus, t.StartDate,
t.CountryId, t.ProvinceId, t.Locationid, t.PlaceId, t.EstabId, t.CheckInDate, 
t.CheckOutDate, t.Rooms, t.Room1Adults, t.Room1Children, t.Room2Adults, t.Room2Children, 
t.Room3Adults, t.Room3Children, tc.OutcomeDate, tc.OutcomeId, tc.HotelsFound, tc.Notes

FROM [MLT_VisitTracking].[dbo].TrackingAcomSearches_tbl t
INNER JOIN TrackingAcomSearchesOutcome_tbl tc
ON t.trpUTMid = tc.trpUTMid
LEFT JOIN [YAZOO].[MLT_VisitTracking].[dbo].TrackingAcomSearches_tbl tid
ON t.trpUTMid = tid.trpUTMid
WHERE tid.trpUTMid IS NULL

Answer 1

一个可能的选择是将远程表复制到本地服务器。这会将所有表放在同一台服务器上，使优化器能够使用统计信息来生成最佳计划。由于您仅使用 trpUTMid，因此您可以只复制该列。

另一种选择是使用临时表。同样的想法，在同一台服务器上获取所有表:

SELECT trpUTMid
INTO #Yazoo_TrackingAcomSearches_tbl
FROM [YAZOO].[MLT_VisitTracking].[dbo].TrackingAcomSearches_tbl


SELECT t.SearchId, t.VisitSourceId, t.SiteDomainId, t.trpUTMid, t.FlightPlus, t.StartDate,
t.CountryId, t.ProvinceId, t.Locationid, t.PlaceId, t.EstabId, t.CheckInDate, 
t.CheckOutDate, t.Rooms, t.Room1Adults, t.Room1Children, t.Room2Adults, t.Room2Children, 
t.Room3Adults, t.Room3Children, tc.OutcomeDate, tc.OutcomeId, tc.HotelsFound, tc.Notes

FROM [MLT_VisitTracking].[dbo].TrackingAcomSearches_tbl t
INNER JOIN TrackingAcomSearchesOutcome_tbl tc
ON t.trpUTMid = tc.trpUTMid
LEFT JOIN #Yazoo_TrackingAcomSearches_tbl tid
ON t.trpUTMid = tid.trpUTMid
WHERE tid.trpUTMid IS NULL

您还可以在临时表上创建索引以提高性能。

我还建议检查是否存在，而不是检查旧的 left join where null(阅读 this 了解更多信息):

SELECT trpUTMid
INTO #Yazoo_TrackingAcomSearches_tbl
FROM [YAZOO].[MLT_VisitTracking].[dbo].TrackingAcomSearches_tbl


SELECT t.SearchId, t.VisitSourceId, t.SiteDomainId, t.trpUTMid, t.FlightPlus, t.StartDate,
t.CountryId, t.ProvinceId, t.Locationid, t.PlaceId, t.EstabId, t.CheckInDate, 
t.CheckOutDate, t.Rooms, t.Room1Adults, t.Room1Children, t.Room2Adults, t.Room2Children, 
t.Room3Adults, t.Room3Children, tc.OutcomeDate, tc.OutcomeId, tc.HotelsFound, tc.Notes

FROM [MLT_VisitTracking].[dbo].TrackingAcomSearches_tbl t
INNER JOIN TrackingAcomSearchesOutcome_tbl tc
ON t.trpUTMid = tc.trpUTMid
WHERE NOT EXISTS (
    SELECT NULL 
    FROM #Yazoo_TrackingAcomSearches_tbl
    WHERE trpUTMid = t.trpUTMid
)