22,209
社区成员
发帖
与我相关
我的任务
分享请教一个CROSS APPLY效率的问题
(SQL SERVER2016)想问个CROSS APPLY效率的问题.有两张表TABLEA(数据有1万行),TABLEB(数据有40万行).
想在TABLEA表中, 找到各'ID'分组中的'最后的年'与TABLEB中的'SNO'进行关联查询.
想到两种方法达到查询结果,使用CTE的方法只用2秒种, 而使用CROSS APPLY的方法,耗时近2小时. 想问:
1. 为啥CROSS APPLY的效率这么低呢?
2. 有啥办法可以提高 CROSS APPLY的效率吗,毕竟CROSS APPLY看起来更好懂, 代码写的少些.
谢谢!
想在TABLEA表中, 找到各'ID'分组中的'最后的年'与TABLEB中的'SNO'进行关联查询.
想到两种方法达到查询结果,使用CTE的方法只用2秒种, 而使用CROSS APPLY的方法,耗时近2小时. 想问:
1. 为啥CROSS APPLY的效率这么低呢?
2. 有啥办法可以提高 CROSS APPLY的效率吗,毕竟CROSS APPLY看起来更好懂, 代码写的少些.
谢谢!
-- 1. CTE方法
WITH CTEA
AS
(
SELECT *
FROM TABLEA AS A
INNER JOIN TABLEB AS B
ON A.ID = B.SNO
)
, CTEB
AS
(
SELECT *
, MAX(年度) OVER (PARTITION BY ID) AS 最后的年
FROM CTEA
)
SELECT *
, COUNT(*) OVER (PARTITION BY ID) AS NUM
FROM CTEA AS A
WHERE EXISTS
(
SELECT *
FROM CTEB
WHERE A.年度 = 最近年月
AND A.ID = ID
);
-- 2. CROSS APPLY方法
SELECT *
FROM TABLEB AS A
CROSS APPLY
(
SELECT *
FROM TABLEA
WHERE A.SNO = ID
ORDER BY
年度 DESC
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
) AS B
...全文
请发表友善的回复…
发表回复
keueng 2018-05-15
- 打赏
- 举报
在两表的关联字段(姓名)建立了非聚集索引, TABLEB的排序字段(年度)建立了聚集索引,执行效率确实产生了质变(从原来的2小时提高到了4分钟),但还是比JOIN 变了很多,看来真不能只看代码的好看和易懂啊.那么,如果是这样的话,CORSS APPLY的使用场景主要是在什么地方呢?(如果都能用各种JOIN来替代的话)
zjcxc 2018-05-10
- 打赏
- 举报
table scan 没有索引怎么快
砸死牛顿的苹果 2018-05-10
- 打赏
- 举报
cross apply 主句 没一行对应的数据 都需要 cross apply 部分作子查询
keueng 2018-05-10
- 打赏
- 举报
谢谢版主,你的方法比我用的CTE又简洁了很多,我更想搞明白 的是,为啥 CROSS APPLY的效率会这么低呢?
二月十六 2018-05-10
- 打赏
- 举报
--测试数据
if not object_id(N'Tempdb..#TABLEA') is null
drop table #TABLEA
Go
Create table #TABLEA([姓名] nvarchar(22),[房产号] int,[位置] nvarchar(23))
Insert #TABLEA
select N'张三',520,N'石家庄' union all
select N'张三',710,N'北京' union all
select N'张三',280,N'武汉' union all
select N'李四',750,N'杭州' union all
select N'李四',220,N'上海' union all
select N'王五',310,N'成都'
GO
if not object_id(N'Tempdb..#TABLEB') is null
drop table #TABLEB
Go
Create table #TABLEB([年度] Date,[姓名] nvarchar(22),[补贴金额] int)
Insert #TABLEB
select '2012',N'张三',50 union all
select '2013',N'张三',70 union all
select '2014',N'张三',80 union all
select '2014',N'李四',70 union all
select '2015',N'李四',90 union all
select '2016',N'李四',20 union all
select '2017',N'王五',30 union all
select '2018',N'王五',50 union all
select '2016',N'赵六',20 union all
select '2017',N'赵六',30 union all
select '2018',N'赵六',50
Go
--测试数据结束
SELECT t.年度,
t.姓名,
t.补贴金额,
#TABLEA.*
FROM #TABLEA
LEFT JOIN
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY 姓名 ORDER BY 年度 DESC) rn
FROM #TABLEB
) t
ON t.姓名 = #TABLEA.姓名
AND t.rn = 1;
keueng 2018-05-10
- 打赏
- 举报
因为原表涉及到一个办案调查, 因此把原表名隐了, 各位版主大哥见谅,下面是执行计划.
实际需求就是,有两张表,TABLEA (这个是张实验表,原表数据有40万)与 TABLEB(也是实验表,原表数据1万左右),两张都未建立索引
TABLEA如图:
TABLEB如图:
想要达到的结果是,TABLEA表中,张三自2012至2014年一直享有补贴,但TABLEB中看出他是有房产的(北京, 武汉, 石家庄),想要找出他享受补贴的最后一年(这里是2014年),将TABLEA与TABLEB关联,并查看其房产情况.李四,王五的需求一样(也就是查出TABLEA中每一个人最后一年享受补贴及其对应房产的情况,赵六没有房产就不显示).结果如下:
卖水果的net 2018-05-10
- 打赏
- 举报
表结构，索引情况，都说一下。
二月十六 2018-05-09
- 打赏
- 举报
1、楼主可以看一下两个语句的执行计划，看看消耗在哪，效率为什么高和低
2、好看远远没有想能来的重要……
3、没太看懂需求，但我觉得楼主这个问题可能还有更简洁高效的写法，楼主可以给出测试数据和对应想要的结果，看看大家有没有更好的方法
吉普赛的歌 2018-05-09
- 打赏
- 举报
贴一下执行计划吧。
