外套的替代方案是什么?
问题描述:
最近我在我的查询中添加了outer apply。从那以后,这个查询就会一直存在我知道与之相关的表格是数据库中最大的表格的原因之一。外套的替代方案是什么?
select
a.*,
b.*,
BTab.*,
BTTab.*
from
tableA a
join tableB b ON a.ID = b.UID
join *****
left join *******
....
....
....
outer apply
(SELECT TOP 1 *
FROM
biggestTable bt
WHERE
bt.id = a.id
and a.id <> 100
ORDER BY a.datetime desc) BTab
Outer apply
(SELECT TOP 1 *
FROM
biggestTable btt
WHERE
btt.id = a.id
AND btt.DateTime <> '1948-01-01 00:00:00.000'
and btt.value = 0
order by btt.datetime desc) BTTab
where
..................
.................
....................
.................
有没有比使用outer apply更好的解决方案?
答
这里有一个替代方案,不能说它的更好与否。您可能只需要更好的索引你的大表
WITH BTAB as
( SELECT TOP 1
* ,
row_nubmer() over (partition by b.id) rn
FROM
biggestTable bt
) ,
BTTab as (SELECT TOP 1
* ,
row_nubmer() over (partition by btt.id order by btt.datetime desc) rn
FROM
biggestTable btt
WHERE
AND btt.DateTime <> '1948-01-01 00:00:00.000'
and btt.value = 0
)
select
a.*,
b.*,
BTab.*,
BTTab.*
from
tableA a
join tableB b ON a.ID = b.UID
join *****
left join BTab on ON a.ID = BTab.ID
and BTAB.rn = 1
left join BTTabon ON a.ID = BTTab.ID
and BTTab.rn = 1
答
+1康拉德作为他的答案可能是你所需要的,我再利用他的一些语法。
Apply和CTE的问题是它们针对a,b连接中的每一行进行评估。
我会创建两个临时表。代表最大行并在其上放置PK。这样做的好处是这两个昂贵的问题一次完成,并且加入到PK中。加入PK的大好处。我吃掉#temp的开销来获得单个评估和PK。
Create table #Btab (int ID PK, ...)
insert into #Btab
WITH BTAB as
( SELECT * ,
row_nubmer() over (partition by b.id) rn
FROM
biggestTable
where ID <> 100
)
Select * from BTAB
Where RN = 1
order by ID
Create table #Bttab (int ID PK, ...)
insert into #Bttab
WITH BTTAB as
( SELECT * ,
row_nubmer() over (partition by id order by datetime desc) rn
FROM
biggestTable
where DateTime <> '1948-01-01 00:00:00.000' and value = 0
)
Select * from BTAB
Where RN = 1
order by ID
select
a.*,
b.*,
#Btab.*,
#Bttab.*
from
tableA a
join tableB b ON a.ID = b.UID
join *****
left join *******
....
....
....
left outer outer join #Btab
on #Btab.ID = a.ID
left outer outer join #Bttab
on #Bttab.ID = a.ID
where
..................
.................
P.S.我正在通过#TEMP为此探索TVP。 TVP支持PK并且比#tmp开销少。但是我没有把它们在这种类型的应用中进行比较。
通过#TEMP测试TVP并获得了1/2秒的改进(大约需要创建和删除临时表的时间)。
显然我在这里失去了一些东西。我对APPLY的了解是调用一个表值函数,但你没有TVF。 APPLY在这个查询中做了什么?这是否就像内联TVF? – Paparazzi 2012-02-22 17:18:40
@BalamBalam - OP有两个相关的子查询。两者都是以'a.id'作为参数的Inline TableValuedFunctions。 – MatBailie 2012-02-22 17:21:38