过程中用新变量，导致该列不走索引问题

华夏小卒 2014-01-07 09:54:20 


调用过程时候，比如日期变量  

过程中，用另外变量替换了一下。

 set @rq11=substring(@rq1,1,4)||'/'||substring(@rq1,5,2)||'/'||substring(@rq1,7,2)||' 00:00:00:000'

 set @rq22=substring(@rq2,1,4)||'/'||substring(@rq2,5,2)||'/'||substring(@rq2,7,2)||' 23:59:59:999' 

 

 

 这一替换，导致最后查询时候。rq列始终不走索引，走表扫描。

 

 rq>=@rq11 and rq<=@rq22 

 

 必须强制指定rq索引才可以。统计值已经更新到最新

 

 测试了下，不用新变量替换，就可以正常走rq索引。

 哪位有研究的。？
...全文
384 41 打赏 收藏 转发到动态 举报
写回复
用AI写文章
41 条回复
切换为时间正序
请发表友善的回复…
发表回复
發糞塗牆 2014-01-07
  • 打赏
  • 举报
 回复
用sp_executesql试试
阳泉酒家小当家 2014-01-07
  • 打赏
  • 举报
 回复
引用 27 楼 js_szy 的回复:
[quote=引用 26 楼 yupeigu 的回复:] [quote=引用 24 楼 js_szy 的回复:] [quote=引用 22 楼 DBA_Huangzj 的回复:] 或者每次执行sp的时候在内部重编译
重编译试过了，没有用。 还有个奇怪现象 下面还有个类似语句。就相差1个表，执行计划就正常 
insert #tmp
select a.djlx,a.dh,a.lx,a.kh,a.je
  from ZQ_IV_XSFQ_ZQ_H a ,CM_ZQ_SET_HDDQ_H b 
 where a.djlx='ZQJH' and a.dh=b.dh and a.str >= @str1 and a.str <= @str2
   and b.oprid>=@oprid1 and b.oprid<=@oprid2 
   and b.adtrs>=@rq11 and b.adtrs<=@rq22
[/quote] 要不更新一下表的统计信息，sybase中应该也有吧[/quote] 统计 信息，都更新到最新了 表数据也不大，前表32w，后表79W[/quote] sybase能强制用索引不
华夏小卒 2014-01-07
  • 打赏
  • 举报
 回复
引用 26 楼 yupeigu 的回复:
[quote=引用 24 楼 js_szy 的回复:] [quote=引用 22 楼 DBA_Huangzj 的回复:] 或者每次执行sp的时候在内部重编译
重编译试过了，没有用。 还有个奇怪现象 下面还有个类似语句。就相差1个表，执行计划就正常 
insert #tmp
select a.djlx,a.dh,a.lx,a.kh,a.je
  from ZQ_IV_XSFQ_ZQ_H a ,CM_ZQ_SET_HDDQ_H b 
 where a.djlx='ZQJH' and a.dh=b.dh and a.str >= @str1 and a.str <= @str2
   and b.oprid>=@oprid1 and b.oprid<=@oprid2 
   and b.adtrs>=@rq11 and b.adtrs<=@rq22
[/quote] 要不更新一下表的统计信息，sybase中应该也有吧[/quote] 统计 信息，都更新到最新了 表数据也不大，前表32w，后表79W
阳泉酒家小当家 2014-01-07
  • 打赏
  • 举报
 回复
引用 24 楼 js_szy 的回复:
[quote=引用 22 楼 DBA_Huangzj 的回复:] 或者每次执行sp的时候在内部重编译
重编译试过了，没有用。 还有个奇怪现象 下面还有个类似语句。就相差1个表，执行计划就正常 
insert #tmp
select a.djlx,a.dh,a.lx,a.kh,a.je
  from ZQ_IV_XSFQ_ZQ_H a ,CM_ZQ_SET_HDDQ_H b 
 where a.djlx='ZQJH' and a.dh=b.dh and a.str >= @str1 and a.str <= @str2
   and b.oprid>=@oprid1 and b.oprid<=@oprid2 
   and b.adtrs>=@rq11 and b.adtrs<=@rq22
[/quote] 要不更新一下表的统计信息，sybase中应该也有吧
發糞塗牆 2014-01-07
  • 打赏
  • 举报
 回复
sybase的plan不会看
华夏小卒 2014-01-07
  • 打赏
  • 举报
 回复
引用 22 楼 DBA_Huangzj 的回复:
或者每次执行sp的时候在内部重编译
重编译试过了，没有用。 还有个奇怪现象 下面还有个类似语句。就相差1个表，执行计划就正常 
insert #tmp
select a.djlx,a.dh,a.lx,a.kh,a.je
  from ZQ_IV_XSFQ_ZQ_H a ,CM_ZQ_SET_HDDQ_H b 
 where a.djlx='ZQJH' and a.dh=b.dh and a.str >= @str1 and a.str <= @str2
   and b.oprid>=@oprid1 and b.oprid<=@oprid2 
   and b.adtrs>=@rq11 and b.adtrs<=@rq22
阳泉酒家小当家 2014-01-07
  • 打赏
  • 举报
 回复
引用 18 楼 js_szy 的回复:
[quote=引用 17 楼 DBA_Huangzj 的回复:] 我觉得可能是参数嗅探的问题，你有检查过你的那个日期的筛选度不？
实际上，这个语句，结果集是0， CM_ZQ_SET_HDDQ_H 这个表在4号是没记录的。 过程里，总是走表扫描[/quote] 要不就把这个： set @rq11=substring(@rq1,1,4)||'/'||substring(@rq1,5,2)||'/'||substring(@rq1,7,2)||' 00:00:00:000' set @rq22=substring(@rq2,1,4)||'/'||substring(@rq2,5,2)||'/'||substring(@rq2,7,2)||' 23:59:59:999' 加上类型转化，转化为 datetime类型
發糞塗牆 2014-01-07
  • 打赏
  • 举报
 回复
或者每次执行sp的时候在内部重编译
發糞塗牆 2014-01-07
  • 打赏
  • 举报
 回复
sybase不知道有没有plan guide
华夏小卒 2014-01-07
  • 打赏
  • 举报
 回复
引用 19 楼 DBA_Huangzj 的回复:
因为是参数，优化器不知道你传什么，所以要预估统计信息的平均值，当平均值比较大，那么就选择扫描操作，在最终步骤才发现“没有数据”
看来只能强制索引了。
發糞塗牆 2014-01-07
  • 打赏
  • 举报
 回复
因为是参数，优化器不知道你传什么，所以要预估统计信息的平均值，当平均值比较大，那么就选择扫描操作，在最终步骤才发现“没有数据”
华夏小卒 2014-01-07
  • 打赏
  • 举报
 回复
引用 17 楼 DBA_Huangzj 的回复:
我觉得可能是参数嗅探的问题，你有检查过你的那个日期的筛选度不？
实际上，这个语句，结果集是0， CM_ZQ_SET_HDDQ_H 这个表在4号是没记录的。 过程里，总是走表扫描
發糞塗牆 2014-01-07
  • 打赏
  • 举报
 回复
我觉得可能是参数嗅探的问题，你有检查过你的那个日期的筛选度不？
华夏小卒 2014-01-07
  • 打赏
  • 举报
 回复
引用 15 楼 DBA_Huangzj 的回复:
oracle是吧？执行计划贴一下，尝试看看

 是sybase ase
 
 单个语句，变量值带进去，执行计划没有问题，1秒出结果。
 一旦过程里，就走表扫描，要8分钟出结果
 
 
 调用语句
 execute dbo.szy_test;1 @empid = '12090227', @oprid1 = '', @oprid2 = '99999999', @rq1= '20140104', @rq2 = '20140104', @str1 = '8270', @str2 = '8270'
 
 部分语句：
 set @rq11=substring(@rq1,1,4)||'/'||substring(@rq1,5,2)||'/'||substring(@rq1,7,2)||' 00:00:00:000'
 set @rq22=substring(@rq2,1,4)||'/'||substring(@rq2,5,2)||'/'||substring(@rq2,7,2)||' 23:59:59:999' 
 
 insert #tmp                                                                                                  
select a.djlx,a.dh,a.lx,a.kh,a.je
  from ZQ_IV_XSFQ_ZQ a ,CM_ZQ_SET_HDDQ_H b  
 where a.djlx='ZQJH' and a.dh=b.dh and a.str >= @str1 and a.str <= @str2
   and b.adtrs>=@rq11 and b.adtrs<=@rq22 
   and b.oprid>=@oprid1 and b.oprid<=@oprid2 
   
   
   执行计划：
       STEP 1
        The type of query is INSERT.
        The update mode is direct.

        FROM TABLE
            CM_ZQ_SET_HDDQ_H
            b
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            ZQ_IV_XSFQ_ZQ
            a
        Nested iteration.
        Using Clustered Index.
        Index : PK_ZQ_IV_XSFQ_ZQ
        Forward scan.
        Positioning by key.
        Keys are:
            djlx  ASC
            dh  ASC
            str  ASC
        Using I/O Size 4 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size 4 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            #tmp
        Using I/O Size 4 Kbytes for data pages.
發糞塗牆 2014-01-07
  • 打赏
  • 举报
 回复
oracle是吧？执行计划贴一下，尝试看看
华夏小卒 2014-01-07
  • 打赏
  • 举报
 回复
引用 12 楼 DBA_Huangzj 的回复:
那可能是日期转换导致的，你试一下定义的时候先转换好同样的类型，不要在where中转换
where 中没有转换，直接就是 rq>=@rq11 and rq<=@rq22 只是@rq11是在查询之前赋值的
华夏小卒 2014-01-07
  • 打赏
  • 举报
 回复
引用 10 楼 OrchidCat 的回复:
[quote=引用 8 楼 js_szy 的回复:] [quote=引用 6 楼 OrchidCat 的回复:] [quote=引用 4 楼 js_szy 的回复:] [quote=引用 3 楼 OrchidCat 的回复:] lz 这个其实是蛮典型的，索引失效。 对索引列的查询，如果加入了计算，转换等等情况，索引即会失效。 可以理解为 依照一个不确定的因素，来查找信息的最保守方法，即为全部信息的扫描。 参考
最终查询的时候，没有计算哦。 而且 过程中，写死 set @rq11='2014/01/04 00:00:00:000' set @rq22='2014/01/04 23:59:59:000' 然后这样查询也不走索引 rq>=@rq11 and rq<=@rq22 [/quote] lz的输出是？ 如果输出较多，查询分析器也会给出扫描的结果。 [/quote] 你说的输出指的是啥？ 记录数还是列数 返回3条结果，列数10列左右[/quote] 列数！ 是否走索引，查询分析器，会根据成本来进行选择。 如果获取的列数较多，走索引的开销更大（这个部分的计算，不仅仅是时间开销），查询分析器仍然会给出全表扫描的分析结果。 lz小修改一下，试试看，如果只输出rq列 [/quote] 看了下，结果是5列，rq也不在输出结果中
發糞塗牆 2014-01-07
  • 打赏
  • 举报
 回复
那可能是日期转换导致的，你试一下定义的时候先转换好同样的类型，不要在where中转换
华夏小卒 2014-01-07
  • 打赏
  • 举报
 回复
引用 9 楼 DBA_Huangzj 的回复:
日期类型是相同的吗？
表里是datetime 因为传的格式，所以过程定义的是@rq1 varchar(8),@rq2 varchar(8),
Mr_Nice 2014-01-07
  • 打赏
  • 举报
 回复
引用 8 楼 js_szy 的回复:
[quote=引用 6 楼 OrchidCat 的回复:] [quote=引用 4 楼 js_szy 的回复:] [quote=引用 3 楼 OrchidCat 的回复:] lz 这个其实是蛮典型的，索引失效。 对索引列的查询，如果加入了计算，转换等等情况，索引即会失效。 可以理解为 依照一个不确定的因素，来查找信息的最保守方法，即为全部信息的扫描。 参考
最终查询的时候，没有计算哦。 而且 过程中，写死 set @rq11='2014/01/04 00:00:00:000' set @rq22='2014/01/04 23:59:59:000' 然后这样查询也不走索引 rq>=@rq11 and rq<=@rq22 [/quote] lz的输出是？ 如果输出较多，查询分析器也会给出扫描的结果。 [/quote] 你说的输出指的是啥？ 记录数还是列数 返回3条结果，列数10列左右[/quote] 列数！ 是否走索引，查询分析器，会根据成本来进行选择。 如果获取的列数较多，走索引的开销更大（这个部分的计算，不仅仅是时间开销），查询分析器仍然会给出全表扫描的分析结果。 lz小修改一下，试试看，如果只输出rq列
加载更多回复（21）
Excel wps 小白偷懒系列（电子表格制作 vlookup函数的使用方法）
Excel小白偷懒系列课程 ，本套教程的特点，在工作中你遇到什么样的问题，我快速的带你解决这个问题，最后在解决这个问题中用到了哪些知识点，我们在拓展或者关联一下。 通过本套教程，您将学会 快速绘制表格，并完成...
matlab 此类型的变量不支持使用点进行索引错误
直接双击运行通过GUIDE创建的.fig文件，就会报这个错误； 运行对应的.m文件就不会报错了 问题出现在这条语句： set(handles.Distance,'String',Final_distance); 就是把Final_distance赋值到handles.Distance这个tag上， 如果使用GUIDE创建gui界面时，传递到函数中的句柄结构始终引用针对图形元素所在的图形存储的信息。句柄不是全局结构，而是每个GUI的结构。 解决办法就是首先找到这个句柄对象再进行赋值 修改如下：
keil5查找不到变量的出处
我发现keil5里面， 在一个函数里面定义一个,static静态变量后，后面使用这个变量，按F12或者右击查找这个变量的出处都会找不到 在一个函数的接口里定义的变量，按F12或者右击查找这个变量的出处也会找不到 定义一个枚举，在其他地方找这个枚举的变量成员也会找不到 keil5有个全局查找的功能，并且在查找的结果中双击可以跳转，但是这个功能在目录级别达到8级的深度时，已经不能跳转了而目录是...
为什么where条件中使用or索引不起作用？
为什么where条件中使用or索引不起作用？where条件中使用or，索引就会失效，会造成全表扫描    是误区 一，要求使用的所有字段，都必须建立索引。 二，数据量太少，制定执行计划时发现全表扫描比索引查找更快。 三，确保mysql版本5.0以上，且查询优化器开启了index_merge_union=on, 也就是变量optimizer_switch里存在index_mer
IN语句引起的索引失效
在使用SQL语句查询时，需要注意避免使用会导致索引失效的操作，比如IN语句、函数或表达式处理索引列、不等于操作符、LIKE操作符等。如果需要使用这些操作，可以考虑使用替代方案，比如EXISTS语句和JOIN语句。
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章