SQL 跨数据库优化
为避免全局扫描,跨数据库时,尽量派生子表关联;如示列有查询数据1.8K左右,进行如下查询:
方法一:
SELECT A.times,A.aid,A.asn,A.pn,A.code,B.barcode FROM [10].TEST.dbo.TBL_A A
LEFT JOIN [20].TEST.dbo.TBL_B B ON A.aid=B.bid
WHERE A.code='11223344'
测试执行时间为30S左右(aid为索引列)
方法二:
;WITH CTE AS
(SELECT times,aid,asn,pn,code FROM [10].TEST.dbo.TBL_A
WHERE code='11223344')
,CTE2 AS
(SELECT B.bid,B.barcode FROM [20].TEST.dbo.TBL_B B
WHERE EXISTS(SELECT aid FROM [10].TEST.dbo.TBL_A A WHERE B.bid=A.aid AND A.code='11223344'
) SELECT a.*,b.barcode FROM CTE a,CTE2 b
WHERE a.aid=b.bid
测试执行方法二时间0.5S左右