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

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也做出了相应的支持。 如图&#xff…...

政府采购方式有哪些,竞争性谈判和竞争性磋商的区别

政府采购的方式主要包括公开招标、邀请招标、竞争性谈判、竞争性磋商、询价、单一来源采购和框架协议采购等几种。以下是对这些方式的具体介绍: 公开招标 定义:公开招标是指采购单位依法以招标公告的方式邀请不特定的供应商参与投标的采购方式。适用情形…...

【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) 复杂度的算法都会超过时间限制&#xff0c;只有 O(nlogn) 的可以通过 快速排序空间复杂度为 O(logn)是由于递归的栈的调用归并排序空间复杂度为 O(n) 是由于需要一个临时数组 (当然也需要栈的调用&#xff0c;但是 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 研究背景 如今互联网高速发展&#xff0c;网络遍布全球&#xff0c;通过互联网发布的消息能快而方便的传播到世界每个角落&#xff0c;并且互联网上能传播的信息也很广&#xff0c;比如文字、图片、声音、视频等。从而&#xff0c;这种种好处使得互联网成了信息传…...

装饰模式(Decorator Pattern)重构java邮件发奖系统实战

前言 现在我们有个如下的需求&#xff0c;设计一个邮件发奖的小系统&#xff0c; 需求 1.数据验证 → 2. 敏感信息加密 → 3. 日志记录 → 4. 实际发送邮件 装饰器模式&#xff08;Decorator Pattern&#xff09;允许向一个现有的对象添加新的功能&#xff0c;同时又不改变其…...

云原生核心技术 (7/12): K8s 核心概念白话解读(上):Pod 和 Deployment 究竟是什么?

大家好&#xff0c;欢迎来到《云原生核心技术》系列的第七篇&#xff01; 在上一篇&#xff0c;我们成功地使用 Minikube 或 kind 在自己的电脑上搭建起了一个迷你但功能完备的 Kubernetes 集群。现在&#xff0c;我们就像一个拥有了一块崭新数字土地的农场主&#xff0c;是时…...

java_网络服务相关_gateway_nacos_feign区别联系

1. spring-cloud-starter-gateway 作用&#xff1a;作为微服务架构的网关&#xff0c;统一入口&#xff0c;处理所有外部请求。 核心能力&#xff1a; 路由转发&#xff08;基于路径、服务名等&#xff09;过滤器&#xff08;鉴权、限流、日志、Header 处理&#xff09;支持负…...

rknn优化教程(二)

文章目录 1. 前述2. 三方库的封装2.1 xrepo中的库2.2 xrepo之外的库2.2.1 opencv2.2.2 rknnrt2.2.3 spdlog 3. rknn_engine库 1. 前述 OK&#xff0c;开始写第二篇的内容了。这篇博客主要能写一下&#xff1a; 如何给一些三方库按照xmake方式进行封装&#xff0c;供调用如何按…...

如何在看板中体现优先级变化

在看板中有效体现优先级变化的关键措施包括&#xff1a;采用颜色或标签标识优先级、设置任务排序规则、使用独立的优先级列或泳道、结合自动化规则同步优先级变化、建立定期的优先级审查流程。其中&#xff0c;设置任务排序规则尤其重要&#xff0c;因为它让看板视觉上直观地体…...

Java - Mysql数据类型对应

Mysql数据类型java数据类型备注整型INT/INTEGERint / java.lang.Integer–BIGINTlong/java.lang.Long–––浮点型FLOATfloat/java.lang.FloatDOUBLEdouble/java.lang.Double–DECIMAL/NUMERICjava.math.BigDecimal字符串型CHARjava.lang.String固定长度字符串VARCHARjava.lang…...

大学生职业发展与就业创业指导教学评价

这里是引用 作为软工2203/2204班的学生&#xff0c;我们非常感谢您在《大学生职业发展与就业创业指导》课程中的悉心教导。这门课程对我们即将面临实习和就业的工科学生来说至关重要&#xff0c;而您认真负责的教学态度&#xff0c;让课程的每一部分都充满了实用价值。 尤其让我…...

AspectJ 在 Android 中的完整使用指南

一、环境配置&#xff08;Gradle 7.0 适配&#xff09; 1. 项目级 build.gradle // 注意&#xff1a;沪江插件已停更&#xff0c;推荐官方兼容方案 buildscript {dependencies {classpath org.aspectj:aspectjtools:1.9.9.1 // AspectJ 工具} } 2. 模块级 build.gradle plu…...

#Uniapp篇:chrome调试unapp适配

chrome调试设备----使用Android模拟机开发调试移动端页面 Chrome://inspect/#devices MuMu模拟器Edge浏览器&#xff1a;Android原生APP嵌入的H5页面元素定位 chrome://inspect/#devices uniapp单位适配 根路径下 postcss.config.js 需要装这些插件 “postcss”: “^8.5.…...

安宝特案例丨Vuzix AR智能眼镜集成专业软件,助力卢森堡医院药房转型,赢得辉瑞创新奖

在Vuzix M400 AR智能眼镜的助力下&#xff0c;卢森堡罗伯特舒曼医院&#xff08;the Robert Schuman Hospitals, HRS&#xff09;凭借在无菌制剂生产流程中引入增强现实技术&#xff08;AR&#xff09;创新项目&#xff0c;荣获了2024年6月7日由卢森堡医院药剂师协会&#xff0…...