sql-server - SQL CTE vs Temp 表在性能方面
问题描述
挑战:找到从 1 到 512 的所有三元组(n1, n2, n3)
,使得n1=n2*n3
. 仅使用纯 SQL,没有预期的预先存在的表空间,也没有创建新的永久表。
CTE 解决方案:
;with two as
(
select 0 as ID union select 1 as ID
), eight as
(
select t1.ID*4+t2.ID*2+t3.ID as ID
from two t1 inner join two t2 on 1=1 inner join two t3 on 1=1
), halfk as
(
select t1.ID*8*8 + t2.ID*8 + t3.ID + 1 as ID
from eight t1 inner join eight t2 on 1=1 inner join eight t3 on 1=1
)
select t1.ID, t2.ID, t3.ID
from halfk t1
inner join halfk t2 on t1.ID % t2.ID = 0
inner join halfk t3 on t3.ID * t2.ID = t1.ID
运行时间:不知道;将近2分钟后停止。
临时表解决方案:
if (object_id('tempdb..#tmp_two', 'U') is not null) drop table #tmp_two
select 0 as ID into #tmp_two union select 1 as ID
if (object_id('tempdb..#tmp_eight', 'U') is not null) drop table #tmp_eight
select t1.ID*4+t2.ID*2+t3.ID as ID into #tmp_eight
from #tmp_two t1 inner join #tmp_two t2 on 1=1 inner join #tmp_two t3 on 1=1
if (object_id('tempdb..#tmp_halfk', 'U') is not null) drop table #tmp_halfk
select t1.ID*8*8 + t2.ID*8 + t3.ID + 1 as ID into #tmp_halfk
from #tmp_eight t1 inner join #tmp_eight t2 on 1=1 inner join #tmp_eight t3 on 1=1
select t1.ID, t2.ID, t3.ID as ID
from #tmp_halfk t1 inner join #tmp_halfk t2 on t1.ID % t2.ID = 0
inner join #tmp_halfk t3 on t3.ID * t2.ID = t1.ID
运行时间:1秒。
问题:为什么性能如此不同?为什么即使在上述解决方案中多次使用 CTE 表也没有实现?
更重要的是,作为巨大的性能影响,我怎样才能避免使用干净整洁的方式来完成工作,但会带来性能风险?是否有任何指导方针可以避免此类情况?
select @@VERSION
Microsoft SQL Server 2016 (SP1-CU10-GDR) (KB4293808) - 13.0.4522.0 (X64) Jul 17 2018 22:41:29 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor)
解决方案
问题来自最后一个查询及其第二个连接。它必须进行额外的计算,并且行数刚刚超过屋顶。通过了解您要执行的操作,很容易避免第三次调用 CTE。
WITH
two AS (
SELECT 0 AS ID UNION ALL
SELECT 1 AS ID
),
eight AS (
SELECT t1.ID * 4 + t2.ID * 2 + t3.ID AS ID
FROM two AS t1
CROSS JOIN two AS t2
CROSS JOIN two AS t3
),
halfk AS (
SELECT t1.ID * 8 * 8 + t2.ID * 8 + t3.ID + 1 AS ID
FROM eight AS t1
CROSS JOIN eight AS t2
CROSS JOIN eight AS t3
)
SELECT t1.ID,
t2.ID,
t1.ID / t2.ID AS ID
FROM halfk AS t1
INNER JOIN halfk AS t2 ON t1.ID >= t2.ID AND t1.ID % t2.ID = 0;
CTE 不会实现,因为它们只是经过重构以简化读取和编写代码的查询,因此每次调用它们时,都会重新计算它们。
您甚至可以采用更有效的方式来创建计数表。
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E3(n) AS(
SELECT a.n FROM E a, E b, E c
),
cteTally(ID) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E3
)
SELECT t1.ID,
t2.ID,
t1.ID / t2.ID AS ID
FROM cteTally AS t1
INNER JOIN cteTally AS t2 ON t1.ID >= t2.ID AND t1.ID % t2.ID = 0;
推荐阅读
- neo4j - NEO4J | 因果集群 - 如何更改用户密码(跨所有实例)
- python - 在python中使用openCV和Tesseract从图像中提取主标题
- node.js - 我一开始打字,网站就会出现
- python - 大约每 24 场比赛一次,我的二十一点游戏的随机选择失败
- ios - 如何在一个视图中制作两个集合视图?
- android - 如何在 mpandroid 条形图中显示从负到正的值?
- php - 如何使用 preg_match_all 使用正则表达式模式将字符串转换为数组
- lua - 挂断代码在Lua中自由切换
- python - 迁移 admin.0001_initial 在其依赖 app.0001_initial 之前应用在数据库“默认”上
- gitlab - 当我手动运行管道时,Gitlab 会触发所有监听分支更改的作业吗