SQL Server ->> 条件筛选做法之 -- IN(VALUE1,VALUE2,...)与INNER JOIN STRING_SPLIT()性能对比...

最新推荐文章于 2022-04-13 09:25:14 发布
最新推荐文章于 2022-04-13 09:25:14 发布
阅读量178 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： 数据库
原文链接：http://www.cnblogs.com/jenrrychen/p/7064504.html
本文通过实例比较了在SQL查询中使用IN关键字与INNER JOIN在处理大量数据时的不同性能表现，尤其是在I/O操作方面。

在以逗号拼接而成的字符串，传入给IN字句的元素字符串中包涵了1400多个元素

两种做法分别为 

AND e.ssPfCityId IN (
SELECT
CAST(value AS INT)
FROM STRING_SPLIT('110000,310000,120000,210100,210200,210400,210800,211200,350100,350500,350200,350800,350700,350900,441200,441300,440500,445100,450100,451000,450800,450300,451100,450200,450900,450500,450400,450600,460100,510100,...'
,',')
)

 

INNER JOIN (SELECT DISTINCT CAST(value AS INT) AS VALUE FROM STRING_SPLIT('110000,310000,120000,210100,210200,210400,210800,211200,350100,350500,350200,350800,350700,350900,441200,441300,440500,445100,450100,451000,450800,450300,451100,450200,450900,450500,450400,450600,460100,510100,...',',') T) T ON e.ssPfCityId = T.VALUE

 

 

对比看出如果用IN字句会用一个HASH MATCH的聚合操作符，而用INNER JOIN则用DISTINCT SORT。

 

 

而如果对比IO统计数据可以发现IN字句的做法多出了许多Workfile产生的IO

(128478 行受影响)

表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 577 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Workfile'。扫描计数 70，逻辑读取 2424 次，物理读取 172 次，预读 2268 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'employee'。扫描计数 9，逻辑读取 4559 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'verifyProcess'。扫描计数 9，逻辑读取 3136 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 

而用INNER JOIN则么有Workfile产生的IO

(128478 行受影响)
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Workfile'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'employee'。扫描计数 9，逻辑读取 4559 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'verifyProcess'。扫描计数 9，逻辑读取 3136 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 

这个例子的性能看上去总时间开销差别并不是很明显，因为连接的表数量少，而如果连接的表数量多起来，可能整个执行计划会是另一回事，那个时候IN字句的弊端就显现了。

转载于:https://www.cnblogs.com/jenrrychen/p/7064504.html

Hive string join int -＞ double / (string + int ) join int -＞ double
Top5软件工程硕士，先后在京东、字节从事多年Java后端开发、实时和离线大数据开发
01-14 965
非数字的string转为double后变为NULL，如果不对NULL做处理的话，NULL值会输入到一个Reduce中，容易导致数据倾斜！ 1 string join int -> double，2个字段都会转化成double(非数字的string值会转化为NULL) > explain > select t1.c1,t2.c1 > from ( > select '1' as c1 > > union all .
大数据-案例-离线数仓-在线教育：MySQL(业务数据)-ETL(Sqoop)-＞Hive数仓【ODS层-数据清洗-＞DW层(DWD-统计分析-＞DWS)】-导出(Sqoop)-＞MySQL-＞可视化
u013250861的博客
07-13 2341
一、访问咨询主题看板 1. 需求分析 目的: 分析每一个调研需求需要计算什么指标, 以及计算这个指标需要通过那些维度,而且还包括计算这个需求涉及 到那些表和那些字段 需求1: 统计指定时间段内，访问客户的总数量。能够下钻到小时数据 指标: 访问量 维度: 时间维度: 年 季度 月 天 小时 涉及到哪些表: web_chat_ems_2019_12 涉及到哪些字段: 时间维度: create_time 说明: 发现create_time字段中包含有年 月 天 小时
SQL in的拼接
07-23
源代码
SQL查询between value1 and value2 之间的值(value1和value2可以是时间、文本、数字、日期等)
qq_38374562的博客
01-16 1769
如题： 遇到这么一个需求，需要根据数据库表中的字段（时间）来查找这一范围的数据（比如yyyy-MM-dd）。 释义：between and操作符作用：操作符 between and 会查询出介于两个值之间的数据范围，这两个值可以是时间、文本、数字、日期等。 举个示例:查询学生表中的分数(范围：90~100分) select st.score from student st where st.sco...
SqlServer数据批量模糊查询及使用String.Join串联字符串
LYLGFYZ的专栏
01-25 2857
实现模糊查询需要使用LIKE '%XXXX%' 1、一个订单号的模糊查询 string.Fomat(@"SELECT[OrderID],[OrderDate],[ShipAddress]   FROM Orders WHERE OrderID LIKE'%{0}%'",strOrderID) 2、多个订单号的模糊查询 Sql格式WHERE OrderID LIKE'%X0%' OR
在SQL SErver中实现数组功能
weixin_34228617的博客
08-10 435
在SQL SErver中实现数组功能  本文摘自肖桂东编著的《SQL Server 疑难解析》 问题描述：     我需要向一个存储过程传递数组类型的参数，可是Transact-SQL里面没有数组类型，请问应该怎样实现数组的功能？ 问题分析：     SQL Server并没有数组类型，ANSI SQL-92标准中并没有任何有关数组方面的定义。要实现其他高级语言中的数组的功能，我们必须使...
<select id="getAccountInfoPage" resultType="java.util.Map"> select t1.account_id, t2.id,t2.update_time <if test="condition.type == 'org'"> ,t3.org_id,t3.org_code,t3.org_name </if> <if test="condition.type == 'item'"> ,t3.item_id,t3.item_code,t3.item_name </if> <if test="condition.type == 'person'"> ,t3.user_id,t3.user_code,t3.user_name </if> from salary_account t1 <if test="condition.type == 'org'"> inner join salary_account_org t2 on t1.account_id = t2.account_id inner join sys_org t3 on t2.org_id = t3.org_id </if> <if test="condition.type == 'item'"> inner join salary_account_item t2 on t1.account_id = t2.account_id inner join salary_item t3 on t2.item_id = t3.item_id </if> <if test="condition.type == 'person'"> inner join salary_account_user t2 on t1.account_id = t2.account_id inner join sys_user t3 on t2.user_id = t3.user_id </if> where t1.deleted = '0' </select> mybatis-plus: configuration: map-underscore-to-camel-case: true # 启用全局驼峰转换 返回的Map对象还是没有自动转驼峰
最新发布
08-14
.map(map -> objectMapper.convertValue(map, new TypeReference<Map<String, Object>>() {})) .collect(Collectors.toList()); } ``` #### 3. 配置验证（确保基础设置正确） 在 `application.yml` 中确认配置： ...
SELECT t.version_id, t.unnested_value from ( SELECT version_id, array_agg(store_ids_array) AS all_store_ids FROM ( SELECT version_id ,split(REPLACE(REPLACE(store_ids, '[', ''), ']', ''),',') AS store_ids_array from ddp_pro_ods.menus_template t WHERE t.year_month_day = CURRENT_DATE() and t.store_select = 2 and t.version_id in ( SELECT t.id from ddp_pro_ods.menu_version t WHERE t.status = 2 and t.year_month_day = CURRENT_DATE() ) ) tmp GROUP BY version_id ) t CROSS JOIN UNNEST(t.all_store_ids) AS unnested_value SQL 错误 [1064] [42000]: Getting analyzing error. Detail message: Column '`t`.`unnested_value`' cannot be resolved.
08-12
CROSS JOIN UNNEST(t.array1, t.array2) AS (u1, u2); ``` 因此，错误的原因是试图通过表别名`t`来访问展开后的列（即`t.unnested_value`），而实际上展开后的列是直接作为新列出现的，不需要表别名。 现在，...
package cn.itcast.edu.analysis import cn.itcast.edu.bean.Answer import cn.itcast.edu.utils.RedisUtil import com.google.gson.Gson import org.apache.kafka.common.serialization.StringDeserializer import org.apache.spark.ml.recommendation.ALSModel import org.apache.spark.sql.functions.udf import org.apache.spark.{SparkContext, streaming} import org.apache.spark.sql.{SaveMode, SparkSession} import org.apache.spark.streaming.StreamingContext import org.apache.spark.streaming.kafka010.{ConsumerStrategies, KafkaUtils, LocationStrategies} import java.util.Properties object StreamingRecommend { def main(args: Array[String]): Unit = { val spark:SparkSession = SparkSession.builder() .appName("streamingrecommend") .master("local[*]") .config("spark.sql.shuffle.partitions", "3") .getOrCreate() val sc:SparkContext = spark.sparkContext val ssc:StreamingContext = new StreamingContext( sc, streaming.Seconds(5) ) import spark.implicits._ val kafkaParams = Map[String, Object]( "bootstrap.servers" -> "hadoop01:9092,hadoop02:9092", "key.deserializer" -> classOf[StringDeserializer], "value.deserializer" -> classOf[StringDeserializer], "group.id" -> "StreamingRecommend", ) val topic = Array("edu") val kafkaDStream = KafkaUtils.createDirectStream[String, String]( ssc, LocationStrategies.PreferConsistent, ConsumerStrategies.Subscribe[String, String](topic, kafkaParams) ) //获取Redis连接配置，并读取存储在Redis中推荐模型的存储路径 kafkaDStream.map(_.value()).foreachRDD(rdd => { if (!rdd.isEmpty()) { val jedis = RedisUtil.pool.getResource val path = jedis.hget( "model", "recommended_question_id" ) //加载推荐模型 val model = ALSModel.load(path) //解析JSON格式的数据，将解析的数据与样例类Answer中的字段进行映射 val answerDF = rdd.coalesce(1) .map(josnStr => { val gson = new Gson() gson.fromJson(josnStr, classOf[Answer]) }).toDF() val id = udf((student_id: String) => { student_id.split("_")(1).toInt }) val studentIdDF = answerDF.select( id ($"student_id") as "student_id" ) val recommendDF = model.recommendForUserSubset( studentIdDF, 10 ) val recommendResultDF = recommendDF .as[(Int, Array[(Int, Float)])] .map(t => { val studentIdStr = "学生ID_" + t._1 val questionIdsStr = t._2.map("题目ID_" + _._1) .mkString(",") (studentIdStr, questionIdsStr) }).toDF("student_id", "recommendations") val allInfoDF = answerDF.join( recommendResultDF, "student_id" ) allInfoDF.show(false) if (allInfoDF.count() > 0) { val properties = new Properties() properties.setProperty("user", "root") properties.setProperty("password", "123456") allInfoDF .write .mode(SaveMode.Append) .jdbc( "jdbc:mysql://hadoop01:3306/edu?" + "createDatabaseIfNotExist=true&" + "useUnicode=true&characterEncoding=utf8", "t_recommended", properties ) } jedis.close() } } ) //启动StreamingContext ssc.start() //使StreamingContext持续运行，除非人为干预停止 ssc.awaitTermination() ssc.stop(stopSparkContext = true, stopGracefully = true) } }
06-17
另外，我们使用`innerjoin`来合并原始数据与推荐结果，这要求原始数据中的`student_id`在推荐结果中存在。如果推荐结果中没有某个学生（例如，模型中没有该学生），则这个学生会被过滤掉。如果希望保留这些学生，...
Spring boot JPA 查询使用in(value1,value2,...)
Java小白的进阶之路
05-07 9308
在使用Spring boot JPA查询时，我们有时候会涉及到使用in的需求，相信很多小伙伴第一次使用的时候会手足无措：这个该怎么办呢？ 下面就是in的用法： 1.先根据该属性创建in CriteriaBuilder.In<Object> in = cb.in(root.get("id")); 2.获取你要的(value1,value2,...)集合 List<Ob...
存储过程中的字符串拼接问题
yeak2001的专栏
01-11 854
-1. 构造使用IN子句的动态Transact-SQL方法进行编号查询 --a. 要查询的字段类型是数字型 --查询的值列表 DECLARE @idlist varchar(100) SET @idlist='1,2,3' --拼接并执行动态Transact-SQL语句 EXEC('SELECT * FROM tbname WHERE fdname IN('+@idlist...
SQLServer将查询内容拼接成字符串输出（超全面！）
Alone_in_的博客
03-05 1万+
一、问题回顾 当我想通过 select 语句将查询内容拼接成字符串输出时出现这种情况。 select U_ID+U_NAME from MES_USER 消息 245，级别 16，状态 1，第 1 行 在将 varchar 值 '小白' 转换成数据类型 int 时失败。 用+号直接拼接的肯定不行，那是在编程语言中的做法。 二、问题解决 方案一： select cast(U...
SQL 表值函数(fn_Split) --- IN(表值函数) 与 CHARINDEX、LIKE 性能测试
qq_38567182的博客
04-13 2649
SQL 表值函数(fn_Split) IN(表值函数 -分隔 ) 与 CHARINDEX、LIKE 性能测试
sqlserver字符串拆分(split)方法汇总
热门推荐
东方匠心的专栏
08-28 6万+
declare @s varchar(100),@sql varchar(1000) set @s='1,2,3,4,5,6,7,8,9,10,11'  while(charindex(',',@s)  begin   --第一个','之前的字符串   select substring(@s,1,charindex(',',@s)-1)   --将第一个','后面的字符串重新赋给@
sql函数 StringSplit(SELECT * from Split('黄色,蓝色,黑色',','))
weixin_30615767的博客
01-06 320
create function [dbo].[StringSplit]( @str nvarchar(max), --字符串 @spliter nvarchar(10)) --分割符returns @tb table(Item nvarchar(256)) --返回对应表ASBEGIN DECLARE @Num int,@Pos int, @NextPos int SET @Num = 0 SE...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值