高分急求答案！sql2008 数据库创建分区表，用分区表查询比用源表查询慢，求解

我建立了分区函数和分区方案，middleclass是分区列，然后我又根据basemoney创建了索引不唯一非聚集索引，数据库里540w数据，下面我的测试结果

分区函数： Sale_Main_Zone

我根据分区表去查询数据，如
select top 2000 * from Sale_Main where Eb80_Sale.$partition.Sale_Main_Zone(middleclass)=612


(2000 行受影响)
表 'Sale_Main'。扫描计数 1，逻辑读取 6147 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(5 行受影响)

(1 行受影响)

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 149 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

我想再加一个排序
select top 2000 * from Sale_Main where Eb80_Sale.$partition.Sale_Main_Zone(middleclass)=612 order by basemoney desc

(2000 行受影响)
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Sale_Main'。扫描计数 1，逻辑读取 15 次，物理读取 1 次，预读 2503 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(7 行受影响)

(1 行受影响)

 SQL Server 执行时间:
   CPU 时间 = 5968 毫秒，占用时间 = 21529 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

这样搜索，为什么还没有在普通表(即原始表)里搜索快，就如
select top 2000 * from Sale_Main where middleclass=5654

(2000 行受影响)
表 'Sale_Main'。扫描计数 1，逻辑读取 10606 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(3 行受影响)

(1 行受影响)

 SQL Server 执行时间:
   CPU 时间 = 78 毫秒，占用时间 = 197 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

对其进行排序：
select top 2000 * from Sale_Main where middleclass=5654 order by basemoney desc

(2000 行受影响)
表 'Sale_Main'。扫描计数 1，逻辑读取 74841 次，物理读取 2 次，预读 51 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(5 行受影响)

(1 行受影响)

 SQL Server 执行时间:
   CPU 时间 = 78 毫秒，占用时间 = 230 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

补充： $partition.Sale_Main_Zone(middleclass)=612与middleclass=5654
是一样的 

请问高手们这个原因是什么呢？急求啊！小弟在这谢过了

16 个解决方案

#1

应该是 第二个查询 因为使用了分区函数而导致 basemoney 索引未能使用。
具体原因还要分析下执行计划。贴出来看看

#2

表  'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

看到分区表排序是有这样一个操作，worktable 是SQL优化器在tempdb中生成一个worktable来缓存你的中间查询结果（用来排序），而不用Partition函数没有这个操作，可能是按照这个栏位有排序，所以速度会快。

还是按照楼上说的，将执行计划贴出来看一下具体哪里有区别。

#3

这个是执行计划
求各位高手指教

#4

最右面的部分是什么看不到啊，你不用执行set statistics io on/off,只需要显示查询语句的执行计划就可以了。

#5

你的where条件中有自定义函数，这样用不到索引导致全表扫描自然就慢。

#6

#7

1、不要用函数查询，虽然结果一样
2、执行计划被遮住了，应该与函数有关
3、必要的话，非聚集索引也可以考虑分区
4、540万数据量，分区效果本来就不会很明显，分区需要在CPU数量较多，内存足够的时候发挥作用，不合适的分区理论上是有可能比不分区慢的
5、分区函数和分区方案很重要，定位问题没有这个信息只能靠猜，猜错几率总比猜队的大些

#8

这里，你的聚集索引在ID列，而你的分区在middleclass列，说明你的表没有建立分区，只是在middleclass列建立了一个分区索引，这样确实不能提高速度法反而下降速度了

表分区需要对聚集索引分区

#9

这个开销最大的是键查找，能不能把另外一条语句的执行计划也发一下？

#10

引用 8 楼 Haiwer 的回复:

表分区需要对聚集索引分区

这个不一定的，是根据你的WHERE条件建立分区，这样才能充分发挥分区函数的效果

#11

都是提示键查询开销最大！都是95%以上，这个是怎么回事啊？

#12

引用 7 楼 Haiwer 的回复:

1、不要用函数查询，虽然结果一样
2、执行计划被遮住了，应该与函数有关
3、必要的话，非聚集索引也可以考虑分区
4、540万数据量，分区效果本来就不会很明显，分区需要在CPU数量较多，内存足够的时候发挥作用，不合适的分区理论上是有可能比不分区慢的
5、分区函数和分区方案很重要，定位问题没有这个信息只能靠猜，猜错几率总比猜队的大些

但是为什么键查找开销会那么大呢？

#13

引用 11 楼 zoulei2546 的回复:

都是提示键查询开销最大！都是95%以上，这个是怎么回事啊？

你两次的执行计划是同一条语句的不能对比

#14

我把所有的分析都贴出来吧！



我的索引是建好的，但是还是提示说缺少索引！

#15

因为你的键在ID字段，ID字段没有分区，所以这个查找过程不会因为分区而变快

#16

告诉你了，你的表并没有分区，你只不过在为分区的表中增加了一个分区索引而已

由于没有聚集，也没有包含必要列，在有排序的查询中并没有使用这个分区索引，使用了排序字段的索引扫描，提示缺少INCLUDE索引

怎么修改就不想猜了，谁知道你这几个字段的重复情况，你的分区函数等