当前位置: 首页 > news >正文

SQL Server中的CTE和临时表优化

在SQL Server中,优化查询性能是数据库管理的核心任务之一。使用公用表表达式(CTE)和临时表是两种重要的技术手段。本文将深入探讨CTE如何简化代码,以及临时表如何优化查询性能。通过实例和详尽解释,我们将展示这两种技术在实际应用中的优点和注意事项。

第一部分:公用表表达式(CTE)

公用表表达式(CTE)是SQL Server 2005引入的一项功能。CTE通过将复杂查询分解成多个可读性高的部分,使代码更加简洁明了。CTE主要有两种类型:递归CTE和非递归CTE。

1.1 非递归CTE

非递归CTE主要用于简化查询,提高代码可读性。以下是一个典型的非递归CTE示例:

WITH SalesCTE AS (SELECT SalesPersonID,SUM(TotalDue) AS TotalSalesFROM Sales.SalesOrderHeaderGROUP BY SalesPersonID
)
SELECT sp.FirstName, sp.LastName, sc.TotalSales
FROM SalesCTE sc
JOIN Sales.SalesPerson sp
ON sc.SalesPersonID = sp.SalesPersonID;

在这个示例中,我们使用CTE将总销售额的计算与人员信息的查询分开,从而提高了代码的清晰度。

1.2 递归CTE

递归CTE用于处理层次结构数据,如组织结构或目录树。以下是一个递归CTE示例:

WITH OrgCTE AS (SELECT EmployeeID, ManagerID, TitleFROM HumanResources.EmployeeWHERE ManagerID IS NULLUNION ALLSELECT e.EmployeeID, e.ManagerID, e.TitleFROM HumanResources.Employee eINNER JOIN OrgCTE oON e.ManagerID = o.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM OrgCTE;

这个示例展示了如何使用递归CTE来获取一个组织结构中的所有员工信息,包括他们的管理层级。

第二部分:临时表优化查询性能

临时表在SQL Server中扮演着重要角色,特别是在处理复杂查询时。临时表允许我们将中间结果存储在一个临时的存储结构中,从而优化查询性能。

2.1 临时表的创建

临时表分为局部临时表和全局临时表。局部临时表以单个会话为作用范围,而全局临时表则可以在多个会话间共享。以下是创建局部临时表的示例:

CREATE TABLE #TempSales (SalesPersonID INT,TotalSales MONEY
);INSERT INTO #TempSales (SalesPersonID, TotalSales)
SELECT SalesPersonID, SUM(TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID;
2.2 临时表的应用场景

临时表在以下几种场景中尤为有用:

  1. 复杂的多步查询:将查询分解为多个步骤,每个步骤的结果存储在临时表中,可以提高整体查询效率。
  2. 大数据量的处理中间结果存储:在处理大数据量时,临时表可以避免重复计算,从而显著提高性能。
  3. 索引和统计信息的应用:临时表允许我们创建索引,从而优化查询性能。

以下是一个结合临时表和索引的示例:

CREATE TABLE #TempSales (SalesPersonID INT,TotalSales MONEY
);INSERT INTO #TempSales (SalesPersonID, TotalSales)
SELECT SalesPersonID, SUM(TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID;CREATE INDEX IX_TempSales_SalesPersonID ON #TempSales(SalesPersonID);SELECT sp.FirstName, sp.LastName, ts.TotalSales
FROM #TempSales ts
JOIN Sales.SalesPerson sp
ON ts.SalesPersonID = sp.SalesPersonID;

在这个示例中,我们首先创建了一个临时表,并将中间结果存储在其中。接着,我们为临时表创建了一个索引,从而优化了后续的查询性能。

第三部分:CTE与临时表的比较与选择

在使用CTE和临时表时,我们需要根据具体情况选择最优方案。以下是CTE和临时表的优缺点比较:

3.1 CTE的优点
  • 代码简洁:CTE使得复杂查询更加易读和维护。
  • 临时作用域:CTE仅在当前查询中有效,不会影响其他查询。
3.2 CTE的缺点
  • 性能限制:对于大数据量的处理中,CTE可能会导致性能问题,因为CTE不会自动创建索引。
  • 复杂查询受限:在多步骤复杂查询中,CTE的灵活性较低。
3.3 临时表的优点
  • 性能优化:临时表可以通过创建索引和统计信息显著提高查询性能。
  • 灵活性高:在多步骤复杂查询中,临时表提供了更多的操作空间和灵活性。
3.4 临时表的缺点
  • 代码复杂度:与CTE相比,临时表的代码更加复杂,需要显式创建和删除。
  • 资源占用:临时表会占用临时数据库资源,可能导致系统负载增加。

第四部分:实例与实践

通过实际案例,我们可以更好地理解CTE和临时表的应用场景和性能表现。以下是一个实际案例,展示如何使用CTE和临时表来优化查询。

4.1 实例背景

假设我们有一个在线销售系统,需要定期生成销售报告。这个报告包括每个销售人员的总销售额、销售订单数量以及客户信息。

4.2 使用CTE的实现

首先,我们使用CTE来实现这个查询:

WITH SalesData AS (SELECT SalesPersonID,COUNT(SalesOrderID) AS OrderCount,SUM(TotalDue) AS TotalSalesFROM Sales.SalesOrderHeaderGROUP BY SalesPersonID
),
CustomerData AS (SELECT c.CustomerID, c.FirstName, c.LastName, s.SalesPersonIDFROM Sales.Customer cJOIN Sales.SalesOrderHeader sON c.CustomerID = s.CustomerID
)
SELECT sd.SalesPersonID, sd.OrderCount, sd.TotalSales, cd.FirstName, cd.LastName
FROM SalesData sd
JOIN CustomerData cd
ON sd.SalesPersonID = cd.SalesPersonID;

这个查询使用了两个CTE,将销售数据和客户数据分开处理,最后在主查询中合并结果。

4.3 使用临时表的实现

接下来,我们使用临时表来实现相同的查询:

CREATE TABLE #SalesData (SalesPersonID INT,OrderCount INT,TotalSales MONEY
);INSERT INTO #SalesData (SalesPersonID, OrderCount, TotalSales)
SELECT SalesPersonID, COUNT(SalesOrderID) AS OrderCount,SUM(TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID;CREATE TABLE #CustomerData (CustomerID INT,FirstName NVARCHAR(50),LastName NVARCHAR(50),SalesPersonID INT
);INSERT INTO #CustomerData (CustomerID, FirstName, LastName, SalesPersonID)
SELECT c.CustomerID, c.FirstName, c.LastName, s.SalesPersonID
FROM Sales.Customer c
JOIN Sales.SalesOrderHeader s
ON c.CustomerID = s.CustomerID;SELECT sd.SalesPersonID, sd.OrderCount, sd.TotalSales, cd.FirstName, cd.LastName
FROM #SalesData sd
JOIN #CustomerData cd
ON sd.SalesPersonID = cd.SalesPersonID;DROP TABLE #SalesData;
DROP TABLE #CustomerData;

使用临时表,我们将中间结果存储在两个临时表中,并在最终查询中合并结果。最后,我们删除临时表以释放资源。

第五部分:总结

CTE和临时表在SQL Server中的应用各有优劣。CTE简化代码,提高可读性,适合较简单的查询和层次结构数据处理。而临时表则提供更高的灵活性和性能优化手段,适用于复杂的多步骤查询和大数据量处理。在实际应用中,我们需要根据具体需求选择最合适的技术手段,以达到最佳的性能和可维护性。

相关文章:

SQL Server中的CTE和临时表优化

在SQL Server中,优化查询性能是数据库管理的核心任务之一。使用公用表表达式(CTE)和临时表是两种重要的技术手段。本文将深入探讨CTE如何简化代码,以及临时表如何优化查询性能。通过实例和详尽解释,我们将展示这两种技…...

CCRC信息安全服务资质认证是什么

什么是CCRC认证? CCRC 全称 China Cybersecurity Review Technology and Certification Center。CCRC认证是指中国网络安全审查技术与认证中心进行的信息安全服务资质认证。简称信息安全服务资质认证。 CCRC,即中国网络安全审查技术与认证中心&#xff0…...

第五十一天 | 1143.最长公共子序列

题目:1143.最长公共子序列718.最长重复子数组的区别是,子序列不要求连续,子数组要求连续。这一差异体现在dp数组含义和递推公式中,本题是子序列,那就要考虑上nums1[i - 1] ! nums2[j - 1]的情况。 本道题与 1.dp数组…...

未来的5-10年,哪些行业可能会被AI代替?

在未来的5-10年,多个行业可能会受到AI技术的影响,其中一些工作可能会被AI所代替。以下是对可能被AI替代的行业及工作的一些概述: 客户服务与代表:随着AI技术的发展,特别是自动话术对话和语音生成技术的进步&#xff0…...

据报道,FTC 和 DOJ 对微软、OpenAI 和 Nvidia 展开反垄断调查

据《纽约时报》报道,联邦贸易委员会 (FTC) 和司法部 (DOJ) 同意分担调查微软、OpenAI 和 Nvidia 潜在反垄断违规行为的职责。 美国司法部将牵头对英伟达进行调查,而联邦贸易委员会将调查 OpenAI 与其最大投资者微软之间的交易。 喜好儿网 今年 1 月&a…...

人工智能发展历程和工具搭建学习

目录 人工智能的三次浪潮 开发环境介绍 Anaconda Anaconda的下载和安装 下载说明 安装指导 模块介绍 使用Anaconda Navigator Home界面介绍 Environment界面介绍 使用Jupter Notebook 打开Jupter Notebook 配置默认目录 新建文件 两种输入模式 Conda 虚拟环境 添…...

Dijkstra算法的原理

Dijkstra算法的原理可以清晰地分为以下几个步骤和要点: 初始化: 引入一个辅助数组D,其中D[i]表示从起始点(源点)到顶点i的当前已知最短距离。如果起始点与顶点i之间没有直接连接,则D[i]被初始化为无穷大&a…...

maven引入依赖时莫名报错

一般跟依赖的版本无关,会报出 Cannot resolve xxx 的错误。 这种情况下去IDEA的setting中找maven的仓库位置 在仓库中顺着包路径下寻找,可能会找到.lastUpdated 的文件,这样的文件一般是下载失败了,而且在一段时间内不再下载&…...

graalvm编译springboot3 native应用

云原生时代容器先行,为了更好的拥抱云原生,spring boot3之后,推出了graalvm编译boot项目,利用jvm的AOT( Ahead Of Time )运行前编译技术,可以将javay源码直接构建成机器码二进制的文件&#xff…...

代码随想录Day58

392.判断子序列 题目:392. 判断子序列 - 力扣(LeetCode) 思路:定义重合数记录s与t的比对情况,挨个取出t的字符,与s的字符进行比较,如果相同,重合数就加1,跳到s的下一个字…...

Android Verified Boot (AVB) 与 dm-verity 之间的关系、相同点与差异点

标签: AVB; dm-verity ;Android Android Verified Boot (AVB) 与 dm-verity 之间的关系、相同点与差异点 概述 Android Verified Boot (AVB) 和 dm-verity 是 Android 操作系统中用于确保设备启动过程和运行时数据完整性的两个重要技术。尽管它们有着不同的实现和侧重点,…...

C++学习笔记“类和对象”:多态;

目录 4.7 多态 4.7.1 多态的基本概念 4.7.2 多态案例--计算器类 4.7.3 纯虚函数和抽象类 4.7.4 多态案例二 - 制作饮品 4.7.5 虚析构和纯虚析构 4.7.6 多态案例三-电脑组装 4.7 多态 4.7.1 多态的基本概念 多态是C面向对象三大特性之一 多态分为两类 静志多态: 函数…...

QT Udp广播实现设备发现

测试环境 本文选用pc1作为客户端,pc2,以及一台虚拟机作为服务端。 pc1,pc2(客户端): 虚拟机(服务端): 客户端 原理:客户端通过发送广播消息信息到ip:255.255.255.255(QHostAddress::Broadcast),局域网…...

PyTorch 统计属性-Tensor基本操作

最小 min, 最大 max, 均值 mean,累加 sum,累乘 prod … >>> a torch.arange(0,8).view(2,4).float() >>> a tensor([[0., 1., 2., 3.],[4., 5., 6., 7.]])>>> a.min() ## 最小值:tensor(0.) >>> a.ma…...

波拉西亚战记加速器 台服波拉西亚战记免费加速器

波拉西亚战记是一款新上线的MMORPG游戏,游戏内我们有多个角色职业可以选择,可以体验不同的战斗流派玩法,开放式的地图设计,玩家可以自由的进行探索冒险,寻找各种物资。各种随机事件可以触发,让玩家的冒险过…...

Mocha + Chai 测试环境配置,支持 ES6 语法

下面是一个完整的 Mocha Chai 测试环境配置,支持 ES6 语法。我们将使用 Babel 来转译 ES6 代码。 步骤一:初始化项目 首先,在项目目录中运行以下命令来初始化一个新的 Node.js 项目: npm init -y步骤二:安装必要的…...

华为网络设备攻击防范

畸形报文攻击防范 攻击行为 畸形报文攻击是通过向交换机发送有缺陷的IP报文,使得交换机在处理这样的IP包时会出现崩溃,给交换机带来损失。 畸形报文攻击主要有如下几种: 没有IP载荷的泛洪攻击 IGMP空报文攻击 LAND攻击 Smurf攻击 TCP标…...

RK3588开发笔记-100M网口自协商成1000M网口

目录 前言 一、问题描述 二、原理图连接 三、解决方法 总结 前言 在进行RK3588开发过程中,遇到一个令人困惑的问题:在使用RTL8211F-CG phy芯片出来的100M网口在自协商后连接速率变成了1000M。这篇博客将详细记录这个问题的产生、排查过程以及最终的解决方案,希望能对遇到…...

Python第二语言(十三、PySpark实战)

目录 1.开篇 2. PySpark介绍 3. PySpark基础准备 3.1 PySpark安装 3.2 掌握PySpark执行环境入口对象的构建 3.3 理解PySpark的编程模型 4. PySpark:RDD对象数据输入 4.1 RDD对象概念:PySpark支持多种数据的输入,完成后会返回RDD类的对…...

《阅读的方法》读后感——超越期待的收获

当我翻开这本书的扉页时,未曾料到它会给我带来如此深远的启示和收获。依照推荐序言中的指引,我随意翻阅、精心选读,每一次都如同打开一扇新的窗户,让我窥见不同领域的智慧和美好。 等地铁时、临睡前随便读点什么,有什么…...

算法训练营第五十八天 | LeetCode 392 判断子序列、卡码网模拟美团笔试第一、二、三题(300/500有待提高)

卡码网图论更新了可以去看看,模拟笔试第四题就是深搜/广搜还不太会 LeetCode 392 判断子序列 其实就是最长公共子序列翻版 代码如下: class Solution {public boolean isSubsequence(String s, String t) {int[][] dp new int[s.length() 1][t.lengt…...

Sa-Token鉴权与网关服务实现

纠错: 在上一部分里我完成了微服务框架的初步实现,但是先说一下之前有一个错误,就是依赖部分 上次的学习中我在总的父模块下引入了spring-boot-dependencies(版本控制)我以为在子模块下就不需要再引用了,…...

企事业单位安全生产月活动怎样向媒体投稿?

作为一名单位的信息宣传员,我肩负着将每一次重要活动的精彩瞬间转化为文字,向外界传递我们单位声音的重任。初入此行时,我满怀热情,坚信通过传统的方式——电子邮件投稿,能够有效地将我们的故事传播出去。然而,现实却给我上了生动的一课。 记得在筹备“安全生产月”活动的宣传时…...

MySQL8.0默认TCP端口介绍

1、本文内容 选择题TCP/IPMySQL 8.0 的默认TCP端口show variables查看总结 2、选择题 A、3306 B、33060 C、33062 D、33063 3、TCP/IP TCP/IP(Transmission Control Protocol/Internet Protocol,传输控制协议/网际协议)是指能够在多个不同…...

Javaweb避坑指北(持续更新)

内容较多可按CtrlF搜索 0.目录 1.获取插入数据后自增长主键的值 2.Controller中返回给ajax请求字符串/json会跳转到xxx.jsp 3.ajax请求获得的json无法解析 4.在Controller中使用ServletFileUpload获取的上传文件为null 5.莫名其妙报service和dao里方法的错误 6.ajax请求拿…...

Web前端知道:深入探索与无尽挑战

Web前端知道:深入探索与无尽挑战 Web前端,这个看似简单却实则深不可测的领域,一直以来都吸引着无数开发者投入其中。在这个充满未知与可能的世界里,我们既是探索者,也是挑战者。本文将从四个方面、五个方面、六个方面…...

QT调用vs2019生成的c++动态库

QT调用vs2019生成的c动态库 dll库的创建方法: VS2019创建c动态链接库dll与调用方法-CSDN博客 加减法示范: 头文件 // 下列 ifdef 块是创建使从 DLL 导出更简单的 // 宏的标准方法。此 DLL 中的所有文件都是用命令行上定义的 DLL3_EXPORTS // 符号编…...

C语言TC中有⼏个画线函数?怎么使⽤?

一、问题 C语⾔中画线的函数好像不⽌ line( )⼀个,那么除了 line( ) ,还有哪些画线函数?怎么使⽤? 二、解答 TC中有3种画线的函数,共语法格式如下。 void far line(int x0, int y0, int xl, int y1); void far linet…...

掌握WhoisAPI,提升域名管理的效率

在互联网时代,域名管理是网站运营中非常重要的一环。通过域名,我们能够轻松访问和识别不同的网站。然而,域名的注册和管理也是一项复杂的任务,特别是对于大规模拥有许多域名的企业来说。为了提升域名管理的效率,我们可…...

Docker与Docker-Compose详解

1、Docker是什么? 在计算机中,虚拟化(英语: Virtualization) 是一种资源管理技术,是将计算机的各种实体资源,如服务器、网络、内存及存储等,予以抽象、转换后呈现出来,打破实体结构间的不可切割的障碍&…...