sql server尽量避免滥用影响性能的标量函数
相信很多新手学了 函数的用法就不可避免的想把学到的东西用起来,然而这个函数使用却有坑, 在实际用的时候我发现一个简单的计算封装 ,不用函数和用函数执行耗时差太多了。
能避免列上进行函数则尽量避免,这是在实际上遇到的坑 ,封装成函数和直接计算效果差太多。
行中函数(Scalar-valued functions)在 SQL Server 中的性能通常较差,主要原因是它们在查询执行过程中被视为"黑盒",使得 SQL Server 优化器无法有效优化这些函数的执行。下面是一些针对行中函数优化的建议和替代方法:
-- 原始标量值函数
CREATE FUNCTION dbo.GetDiscount (@ProductID INT)
RETURNS DECIMAL(10, 2)
AS
BEGINDECLARE @Discount DECIMAL(10, 2)SELECT @Discount = DiscountFROM ProductsWHERE ProductID = @ProductIDRETURN @Discount
END-- 转换为内联表值函数
CREATE FUNCTION dbo.GetDiscountInline (@ProductID INT)
RETURNS TABLE
AS
RETURN
(SELECT DiscountFROM ProductsWHERE ProductID = @ProductID
)
使用内联表值函数时,你可以通过 JOIN 或 CROSS APPLY 来调用它,而不会丢失性能优势。
SELECT p.ProductID, p.ProductName, d.Discount
FROM Products p
CROSS APPLY dbo.GetDiscountInline(p.ProductID) d
避免在查询中的列上使用标量函数
-- 性能较差的写法
SELECT OrderID, dbo.CalculateTax(OrderAmount) AS TaxAmount
FROM Orders-- 性能更好的写法(将计算逻辑直接写入查询)
SELECT OrderID, OrderAmount * 0.08 AS TaxAmount
FROM Orders
使用计算列(Computed Columns)
ALTER TABLE Orders
ADD TaxAmount AS OrderAmount * 0.08 PERSISTED
使用 CASE 语句代替简单的函数
如果标量函数只涉及简单的逻辑判断,可以考虑使用 CASE 语句直接在查询中实现。
-- 使用 CASE 语句替代简单函数
SELECT OrderID, CASE WHEN OrderAmount > 100 THEN OrderAmount * 0.1ELSE OrderAmount * 0.05END AS Discount
FROM Orders
消除标量子查询
标量函数在 WHERE 或 JOIN 条件中使用时会影响性能,可以考虑将其转换为 JOIN 操作。
-- 性能较差的标量子查询
SELECT OrderID
FROM Orders
WHERE dbo.GetCustomerStatus(CustomerID) = 'Active'-- 性能更好的 JOIN 替代
SELECT o.OrderID
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE c.Status = 'Active'
使用存储过程替代复杂的标量函数
对于复杂的逻辑,可以使用存储过程来代替标量函数,因为存储过程的执行效率通常较高。
标量值函数在 SQL Server 中的性能瓶颈通常可以通过以下方式解决:
总结的优化:
转换为内联表值函数(ITVF)
在查询中内联计算逻辑
使用计算列
使用 CASE 语句
使用存储过程
表值函数和内联表值函数的区别
CREATE FUNCTION dbo.GetOrdersByCustomer (@CustomerID INT)
RETURNS TABLE
AS
RETURN (SELECT OrderID, OrderDate, TotalAmountFROM OrdersWHERE CustomerID = @CustomerID
)
多语句表值函数(MSTVF)
CREATE FUNCTION dbo.GetOrdersByCustomerMulti (@CustomerID INT)
RETURNS @OrderTable TABLE
(OrderID INT,OrderDate DATETIME,TotalAmount DECIMAL(18, 2)
)
AS
BEGININSERT INTO @OrderTableSELECT OrderID, OrderDate, TotalAmountFROM OrdersWHERE CustomerID = @CustomerIDRETURN
END
性能表现
内联表值函数(ITVF):
性能更高,因为它们直接嵌入到调用查询中,与视图类似。
SQL Server 优化器能够完全展开内联表值函数,并将其优化为与查询其他部分一起执行的一个执行计划。
没有额外的计算开销,因为它不使用表变量。
多语句表值函数(MSTVF):
性能通常较差,因为 SQL Server 优化器无法提前知道函数内的具体逻辑。
由于使用了表变量,可能会影响查询性能,因为表变量不会生成统计信息,这限制了优化器的能力。
对于复杂的逻辑和多个步骤的计算,MSTVF 的灵活性更高,但执行效率往往不如 ITVF。
适用场景
内联表值函数(ITVF):
适合简单的查询逻辑。
用于那些查询不需要复杂处理逻辑的场景。
性能要求较高的情况下,应该优先选择使用 ITVF。
多语句表值函数(MSTVF):
适合复杂的业务逻辑和多步骤处理。
当需要多个 SQL 语句来生成最终结果时,可以使用 MSTVF。
如果需要在函数中执行复杂的数据操作(如条件判断、循环等),MSTVF 是更好的选择。
SQL Server 优化器支持
ITVF:因为是单个查询,优化器可以将 ITVF 中的逻辑与主查询一起优化。SQL Server 能够生成更高效的执行计划。
MSTVF:由于多语句表值函数的逻辑是一个"黑盒",优化器在执行之前无法知道其中包含的具体内容,这会导致它生成一个次优的执行计划。
为啥标量值函数尽量避免使用
标量值函数(Scalar-valued functions)在 SQL Server 中的性能往往较差,通常建议尽量避免使用。原因如下:
1. 逐行执行
标量值函数在查询中被调用时,会对每一行数据逐一执行。这种逐行处理(Row-by-row execution)方式会导致性能显著下降,尤其是当查询结果集非常大时。相比之下,SQL Server 通常更擅长处理批量操作。
示例:假设有一个返回税额的标量函数 dbo.CalculateTax:
sql
SELECT OrderID, dbo.CalculateTax(OrderAmount) AS TaxAmount
FROM Orders
在此查询中,如果 Orders 表有一百万行记录,SQL Server 会为每一行调用一次 CalculateTax 函数,导致性能极差。
2. 阻碍查询优化器优化
SQL Server 的查询优化器在生成查询计划时,无法有效地优化标量值函数。标量函数的逻辑对于优化器来说是一个“黑盒”,无法提前知道函数内的执行逻辑,因此优化器无法进行充分的优化。这就限制了查询的性能提升。
相比之下,内联表值函数(Inline Table-Valued Functions, ITVF)中的逻辑会被直接嵌入到查询计划中,优化器可以根据整体查询来选择最优的执行计划。
3. 不会生成执行计划并行化
标量值函数通常会导致查询计划的并行化被禁用。SQL Server 优化器会倾向于将使用标量函数的查询设计为单线程执行,这在处理大量数据时,会显著降低性能。
4. 隐藏了真正的计算成本
标量值函数中的操作很容易被忽略,因为它们的执行是隐藏在函数调用中的。这使得查询执行时间的分析和调优变得更加困难。使用标量函数时,开发者可能低估了计算成本,从而导致性能问题。
5. 带来额外的上下文切换开销
标量值函数在执行时会频繁地在 SQL Server 的上下文和函数自身的上下文之间进行切换。每次调用函数时都需要这种开销,在处理大量数据时,这种开销会被放大,从而影响查询性能。
替代方案
为了避免标量值函数的性能问题,可以考虑以下替代方案:
使用内联表值函数(ITVF):它们的性能更好,因为优化器可以将它们直接嵌入到主查询中进行优化。
将计算逻辑直接写在查询中:将简单的计算逻辑内联到查询中,避免使用函数封装。
使用计算列(Computed Columns):对于简单的计算,可以在表中创建计算列,并根据需要为其创建索引。
使用 CASE 语句:对于简单的条件判断,CASE 语句可以替代标量函数,实现相同的逻辑。
相关文章:
sql server尽量避免滥用影响性能的标量函数
相信很多新手学了 函数的用法就不可避免的想把学到的东西用起来,然而这个函数使用却有坑, 在实际用的时候我发现一个简单的计算封装 ,不用函数和用函数执行耗时差太多了。 能避免列上进行函数则尽量避免,这是在实际上遇到的坑 &am…...

python画图|二维动态柱状图输出
【1】引言 在前面的学习过程中,已经探索过二维柱状图和三维柱状图的绘制教程,包括且不限于的文章链接有: python画图|水平直方图绘制_绘制水平直方图-CSDN博客 python画图|3D bar进阶探索_ax.bar3d-CSDN博客 此外也学习了动态的直线输出和…...

CocosCreator 快速部署 TON 游戏:Web2 游戏如何使用 Ton支付
在本篇文章中,我们将继续探讨如何使用 Cocos Creator 开发 Telegram 游戏,重点介绍如何集成 TON 支付功能。通过这一教程,开发者将学会如何在游戏中接入 TON Connect,实现钱包连接、支付以及支付后的校验流程,最终为 W…...

生信初学者教程(二十八):单细胞数据标准化
文章目录 介绍加载R包导入数据消除测序深度影响评估细胞周期的影响识别高度可变的特征缩放数据降维聚类输出结果总结介绍 scRNA-seq的标准化是一个重要的预处理步骤,目的是消除技术变异(比如比如测序深度和基因长度等因素),使基因表达和/或样本之间的比较更加可靠。标准化方…...
【OceanBase诊断调优】—— 错误码 5065 和 5066 的区别
适用版本:V2.1.x、V2.2.x、V3.1.x、V3.2.x 5065 与 5066 是两个近似的报错。 OB_ERR_QUERY_INTERRUPTED(-5065): Message: Query execution was interrupted。 含义为执行中断, 例如终端执行 SQL 过程中按 ctrlc 终止 SQL 执行会报 -5065。 OB_ERR_SESSION_INTER…...

Spring Boot RESTful API开发教程
一、RESTful API简介 RESTful API是一种基于HTTP协议的Web API,其设计原则是简单、可扩展、轻量级、可缓存、可靠、可读性强。RESTful API通常使用HTTP请求方法(GET、POST、PUT、DELETE等)来操作资源,使用HTTP状态码来表示操作结…...

<Rust>iced库(0.13.1)学习之番外:如何为窗口添加初始值?
前言 本专栏是学习Rust的GUI库iced的合集,将介绍iced涉及的各个小部件分别介绍,最后会汇总为一个总的程序。 iced是RustGUI中比较强大的一个,目前处于发展中(即版本可能会改变),本专栏基于版本0.12.1. 注:新版本已更新为0.13 概述 这是本专栏的番外篇,主要介绍一下新…...

Redis:list类型
Redis:list类型 list命令非阻塞LPUSHLRANGELPUSHXRPUSHRPUSHXLPOPRPOPLINDEXLINSERTLLENLREMLTRIMLSET 阻塞BLPOPBRPOP 内部编码ziplistlinkedlistquicklist 几乎每种语言都有顺序表、数组、链表这样的顺序结构,Redis也做出了相应的支持。 如图ÿ…...
政府采购方式有哪些,竞争性谈判和竞争性磋商的区别
政府采购的方式主要包括公开招标、邀请招标、竞争性谈判、竞争性磋商、询价、单一来源采购和框架协议采购等几种。以下是对这些方式的具体介绍: 公开招标 定义:公开招标是指采购单位依法以招标公告的方式邀请不特定的供应商参与投标的采购方式。适用情形…...
【JavaScript】移动色块案例 实现一个可以拖动并且在拖动过程中会自动改变颜色的色块(JS 事件监听器)
移动色块案例 实现一个可以拖动并且在拖动过程中会自动改变颜色的色块。 移动色块:用户可以通过鼠标按住并拖动页面上的红色方块(#blocks)。当用户按下鼠标左键时,色块开始跟随鼠标的移动而移动;当用户释放鼠标左键时…...

[Linux#62][TCP] 首位长度:封装与分用 | 序号:可靠性原理 | 滑动窗口:流量控制
目录 一. 认识TCP协议的报头 1.TCP头部格式 2. TCP协议的特点 二. TCP如何封装与分用 TCP 报文封装与解包 如何封装解包,如何分用 分离有效载荷 隐含问题:TCP 与 UDP 报头的区别 封装和解包的逆向过程 如何分用 TCP 报文 如何通过端口号找到绑…...
【中短文】区分神经网络中 表征特征、潜层特征、低秩 概念
1. 表征特征(Representational Feature): 表征特征通常指的是输入数据经过NN处理就得到的中间表示或输出表示。 这些特征由NN经学习过程自动提取,能更好捕捉输入数据的本质属性。 例如:在图像识别任务中&…...

MySQL8.0环境部署+Navicat17激活教程
安装MySQL 下载MySQL MySQL官网下载当前最新版本,当前是8.0.39。 选择No thanks, just start my download等待下载即可。 安装MySQL 下载完成后,双击安装进入安装引导页面。选择Custom自定义安装。 选择MySQL Server 8.0.39 - X64安装。 点击Execute执…...

每日读则推(十)——Elon Musk‘s speech on self-driving at Tesla‘s annual meeting
Elon Musk: You cant solve self-driving unless you have millions of cars on the road. n.自动驾驶 v.限制,约束,强迫,迫使“We are no longer compute-constrained for training. 不再 n/v.估算,计…...
C++新特性——外部模板
1、What C++11标准引入的一种机制,允许在头文件中声明模板,但仅在一个单独的源文件中显式实例化这些模板。这一机制使得编译器只需要在源文件中实例化模板一次,其它源文件引用已经实例化的模板,从而减少编译时间和生成的二进制文件大小。 2、Why 2.1 优化编译时间 模板实…...

字节跳动青训营开始报名了!
关于青训营: 青训营是字节跳动技术团队发起的技术系列培训 &人才选拔项目;面向高校在校生,旨在培养优秀且具有职业竞争力的开发工程师。 本次技术训练营由掘金联合豆包MarsCode 团队主办课程包含前端、后端和 A 方向,在这个飞速发…...

从SQL Server过渡到PostgreSQL:理解模式的差异
前言 随着越来越多的企业转向开源技术,商业数据库管理员和开发者也逐渐面临向PostgreSQL迁移的需求。 虽然SQL Server和PostgreSQL共享许多数据库管理系统(RDBMS)的基本概念,但它们在处理某些结构上的差异可能会让人感到困惑&…...

刷题 排序算法
912. 排序数组 注意这道题目所有 O(n^2) 复杂度的算法都会超过时间限制,只有 O(nlogn) 的可以通过 快速排序空间复杂度为 O(logn)是由于递归的栈的调用归并排序空间复杂度为 O(n) 是由于需要一个临时数组 (当然也需要栈的调用,但是 O(logn) < O(n) 的…...
【python3】tornado高性能编程
使用多进程充分利用cpu使用异步编程 asyncio import asyncio import time from abc import ABC from concurrent.futures import ProcessPoolExecutor from tornado import web, ioloop, genasync def async_task(name):print(f"start: {name}")st int(time.time()…...

构建高效购物推荐系统:SpringBoot实战
1系统概述 1.1 研究背景 如今互联网高速发展,网络遍布全球,通过互联网发布的消息能快而方便的传播到世界每个角落,并且互联网上能传播的信息也很广,比如文字、图片、声音、视频等。从而,这种种好处使得互联网成了信息传…...

Zustand 状态管理库:极简而强大的解决方案
Zustand 是一个轻量级、快速和可扩展的状态管理库,特别适合 React 应用。它以简洁的 API 和高效的性能解决了 Redux 等状态管理方案中的繁琐问题。 核心优势对比 基本使用指南 1. 创建 Store // store.js import create from zustandconst useStore create((set)…...

为什么需要建设工程项目管理?工程项目管理有哪些亮点功能?
在建筑行业,项目管理的重要性不言而喻。随着工程规模的扩大、技术复杂度的提升,传统的管理模式已经难以满足现代工程的需求。过去,许多企业依赖手工记录、口头沟通和分散的信息管理,导致效率低下、成本失控、风险频发。例如&#…...
2024年赣州旅游投资集团社会招聘笔试真
2024年赣州旅游投资集团社会招聘笔试真 题 ( 满 分 1 0 0 分 时 间 1 2 0 分 钟 ) 一、单选题(每题只有一个正确答案,答错、不答或多答均不得分) 1.纪要的特点不包括()。 A.概括重点 B.指导传达 C. 客观纪实 D.有言必录 【答案】: D 2.1864年,()预言了电磁波的存在,并指出…...
什么是EULA和DPA
文章目录 EULA(End User License Agreement)DPA(Data Protection Agreement)一、定义与背景二、核心内容三、法律效力与责任四、实际应用与意义 EULA(End User License Agreement) 定义: EULA即…...

如何在网页里填写 PDF 表格?
有时候,你可能希望用户能在你的网站上填写 PDF 表单。然而,这件事并不简单,因为 PDF 并不是一种原生的网页格式。虽然浏览器可以显示 PDF 文件,但原生并不支持编辑或填写它们。更糟的是,如果你想收集表单数据ÿ…...
#Uniapp篇:chrome调试unapp适配
chrome调试设备----使用Android模拟机开发调试移动端页面 Chrome://inspect/#devices MuMu模拟器Edge浏览器:Android原生APP嵌入的H5页面元素定位 chrome://inspect/#devices uniapp单位适配 根路径下 postcss.config.js 需要装这些插件 “postcss”: “^8.5.…...

Kafka入门-生产者
生产者 生产者发送流程: 延迟时间为0ms时,也就意味着每当有数据就会直接发送 异步发送API 异步发送和同步发送的不同在于:异步发送不需要等待结果,同步发送必须等待结果才能进行下一步发送。 普通异步发送 首先导入所需的k…...

从 GreenPlum 到镜舟数据库:杭银消费金融湖仓一体转型实践
作者:吴岐诗,杭银消费金融大数据应用开发工程师 本文整理自杭银消费金融大数据应用开发工程师在StarRocks Summit Asia 2024的分享 引言:融合数据湖与数仓的创新之路 在数字金融时代,数据已成为金融机构的核心竞争力。杭银消费金…...
在 Spring Boot 项目里,MYSQL中json类型字段使用
前言: 因为程序特殊需求导致,需要mysql数据库存储json类型数据,因此记录一下使用流程 1.java实体中新增字段 private List<User> users 2.增加mybatis-plus注解 TableField(typeHandler FastjsonTypeHandler.class) private Lis…...

永磁同步电机无速度算法--基于卡尔曼滤波器的滑模观测器
一、原理介绍 传统滑模观测器采用如下结构: 传统SMO中LPF会带来相位延迟和幅值衰减,并且需要额外的相位补偿。 采用扩展卡尔曼滤波器代替常用低通滤波器(LPF),可以去除高次谐波,并且不用相位补偿就可以获得一个误差较小的转子位…...