请教一个CROSS APPLY效率的问题

keueng 2018-05-09 06:03:53

(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看起来更好懂, 代码写的少些.
谢谢!



-- 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

...全文

1139 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

keueng 2018-05-15

打赏
举报

引用 8 楼 zjcxc 的回复:

table scan 没有索引怎么快

引用 5 楼 sinat_28984567 的回复:

--测试数据
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;

引用 5 楼 sinat_28984567 的回复:

--测试数据
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;

在两表的关联字段(姓名)建立了非聚集索引, TABLEB的排序字段(年度)建立了聚集索引,执行效率确实产生了质变(从原来的2小时提高到了4分钟),但还是比JOIN 变了很多,看来真不能只看代码的好看和易懂啊.那么,如果是这样的话,CORSS APPLY的使用场景主要是在什么地方呢?(如果都能用各种JOIN来替代的话)

zjcxc 2018-05-10

打赏
举报

table scan 没有索引怎么快

砸死牛顿的苹果 2018-05-10

打赏
举报

cross apply 主句没一行对应的数据都需要 cross apply 部分作子查询

keueng 2018-05-10

打赏
举报

引用 5 楼 sinat_28984567 的回复:

--测试数据
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;

谢谢版主,你的方法比我用的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

打赏
举报

引用 2 楼 sinat_28984567 的回复:

1、楼主可以看一下两个语句的执行计划，看看消耗在哪，效率为什么高和低
2、好看远远没有想能来的重要……
3、没太看懂需求，但我觉得楼主这个问题可能还有更简洁高效的写法，楼主可以给出测试数据和对应想要的结果，看看大家有没有更好的方法

因为原表涉及到一个办案调查, 因此把原表名隐了, 各位版主大哥见谅,下面是执行计划.

实际需求就是,有两张表,TABLEA (这个是张实验表,原表数据有40万)与 TABLEB(也是实验表,原表数据1万左右),两张都未建立索引
TABLEA如图:

TABLEB如图:

想要达到的结果是,TABLEA表中,张三自2012至2014年一直享有补贴,但TABLEB中看出他是有房产的(北京, 武汉, 石家庄),想要找出他享受补贴的最后一年(这里是2014年),将TABLEA与TABLEB关联,并查看其房产情况.李四,王五的需求一样(也就是查出TABLEA中每一个人最后一年享受补贴及其对应房产的情况,赵六没有房产就不显示).结果如下: