无奈的刷新——8000条记录刷了一天都没好

ycchenz

digital unix，sybase12.0。设表A和表B，分别有记录500万条和500条，均有字段X和Y，且均有索引，X还是A的主键。执行如下语句：update b set y=a.y from a where a.x=b.x，结果可能要一个多小时才能完成，有时也很快，几秒钟就搞定。
     索引应该没坏，因为直接对表A或B进行关于X的查询（如select * from a where x=555555），速度始终是很快的，我想如果我有50个人，把B表的500条记录给分了，一人10条，手工到A表查出其正确的Y值，再update回B表，也用不了几分钟（对B的update也是很快的，毕竟只有几百条记录么）。因此我一开始怀疑系统按最慢的一种方案走：从几百万的A表里取出记录一条条到B表比对。我就做了一个存储过程，里面定义一个游标，从B表取出记录，一条条到A表比较，到A表里找出与X对应的正确的Y值，再更新回B表。结果还是很慢，系统始终在做select，我一看，这个进程在对A做全表扫描，就是说索引根本没起作用，要对A表做几百次全表扫描当然快不了。怪了，为什么同样的语句在客户端执行索引有用，在存储过程的游标里不行呢？
      不过几百条的更新毕竟是小事，可这两天我在更新两个比较大的表，一个有20000条记录，一个有8000条，开始两个表无论用UPDATE还是我那个存储过程（游标也慢，不过不锁表，我可以看到已经有多少记录被刷新，要是在PB或ADVANTAGE上，不晓得要过二十分钟还是二十个小时才能结束）都慢得让人无法忍受，不过我觉得也许将两个表关联起来select会比update快一点？就执行了如下语句：
select dhhm,jjxbh,jdxh,jx_t_jjd.jjdbh from jx_t_jjd,yi_jjx where jx_t_jjd.jjdbh=yi_jjx.jjdbh; 这里jx_t_jjd 有几百万条记录，相当于前面的A表，yi-jjx相当于B表，不过这回是两万条记录。
很快就出结果了，这样我也得到了X字段和Y字段的正确的对应关系。用showplan看动作如下：

QUERY PLAN FOR STATEMENT 1 (at line 1).


    STEP 1
        The type of query is INSERT.
        The update mode is direct.
        Worktable1 created for REFORMATTING.

        FROM TABLE
            yi_jjx
        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.
        TO TABLE
            Worktable1.

    STEP 2
        The type of query is SELECT.

        FROM TABLE
            jx_t_jjd
        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
            Worktable1.
        Nested iteration.
        Using Clustered Index.
        Forward scan.
        Positioning by key.
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

(return status = 0)

我也不知道它都做了些啥，不过显然它使用聚簇索引了，这是能够很快得出结果的原因吧。
再对那个8000条记录的表yi_fxh也执行类似语句：select dhhm,a.fxhbh,a.fxhxx,b.fjd from jx_t_fxhjd a,yi_fxh b where a.fjdbh=b.fjd (jx_t_fxhjd相当于前面说的几百万条记录的表A，yi_fxh为表B，不过它有8000条记录) 速度和前者完全不能相比，showplan动作如下：
    STEP 1
        The type of query is SELECT.

        FROM TABLE
            yi_fxh
            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
            jx_t_fxhjd
            a
        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.

(return status = 0)
它在做全表扫描，可这个jx_t_fxhjd是以fjdbh为主键的啊。哭笑不得的是如果我把前面那个看起来挺正常的语句中要检索的字段换成*，也就是select * from jx_t_jjd,yi_jjx where jx_t_jjd.jjdbh=yi_jjx.jjdbh;那它也变成全表扫描了，奇慢无比。FAINT。不过好在这个表我只要其中几个字段，也就算了。

我对这个jx_t_fxhjd表做了update statistics，没用，删除主键重建（当然也在jjdbh上重建了聚促唯一索引），顺便把yi_fxh的索引也重建了，又做了更新统计，还是没用。绝望，只好让它自己慢慢地刷。从早上到现在，大概有三四千条记录被处理过了。也许到明天早上就能都处理完了吧。不知哪位高手能解答我的疑惑。

jazy

很久没看到有人这么认真的描述自己的问题了。我帮你对问题的描述进行了一下简单的归纳，不知道我的理解是否正确：

第一：“怪了，为什么同样的语句在客户端执行索引有用，在存储过程的游标里不行呢？ ”

我的建议：将你在客户端执行的语句包装到存储过程中执行，可以分不用游标和只用游标取一条记录，看看速度如何？

第二：关于执行计划：
1、测试一
       表名        记录数     索引信息
大表   jx_t_jjd     几百万    jjdbh主键
小表   yi-jjx         2万条     jjdbh索引


执行语句1：
select dhhm,jjxbh,jdxh,jx_t_jjd.jjdbh
from jx_t_jjd,yi_jjx
where jx_t_jjd.jjdbh=yi_jjx.jjdbh

执行结果：速度很快
执行计划：对表jx_t_jjd使用全表扫描
但对工作表采用了索引，工作表的数据来自于表yi_jjx

执行语句2：
select * from jx_t_jjd,yi_jjx
where jx_t_jjd.jjdbh=yi_jjx.jjdbh
执行速度：很慢
执行计划：未贴出，但描述未使用任何索引


2、测试2
       表名        记录数     索引信息
大表   jx_t_fxhjd  几百万      未明确说明
小表   yi_fxh       8000条      未明确水明


执行语句1：
select dhhm,a.fxhbh,a.fxhxx,b.fjd
from jx_t_fxhjd a,yi_fxh b
where a.fjdbh=b.fjd
执行结果：速度很慢
执行计划：对jx_t_fxhjd 使用的全表扫描
对表yi_fxh也采用了全表扫描

如果执行计划和表的描述均如你所说的话，在你的两次执行中对大表均未采用索引，但第一个语句在执行时对工作表采用了索引，但第二个语句未使用任何索引。现在的关键是，为什么在测试一的执行语句1使用了工作表而其他的语句中未使用？
这点根据你目前提供的信息还无法确定。

我的建议：在你执行刷新数据操作的同时，执行sp_sysmon操作来看看系统的性能，找出瓶颈所在

此贴置顶三天！

ycchenz

谢谢版主的回复，你总结很正确，而且比我说的简洁清晰。
      测试一的语句2没有贴出执行计划，因为它和测试2的语句执行计划是一样的，只有表名不同。测试2的索引情况没有说明，这里补充：大表的主键是fjdbh，小表的fjd也有索引，不过不是唯一索引。其实测试一、测试二的情况是类似的。也正因如此，我对它们大相径庭的结果很迷惑。
      版主的第一条建议很有启发，我一直以为和游标有关，没想到同样的语句搬到存储过程里就已经索引失效了。存储过程很简单：
create procedure cz_p_fxhtst
as   
begin   
  declare @fjdbh decimal  
  declare @fxhbh decimal
        declare @fxhxx decimal

         
        select @fjdbh = 5213273
   select @fxhbh=fxhbh,@fxhxx=fxhxx from jx_t_fxhjd where fjdbh=@fjdbh               
   update cz_fxhtst set bh=@fxhbh,xx=@fxhxx where fjd=@fjdbh               
                              
        select @fjdbh = 4820812
   select @fxhbh=fxhbh,@fxhxx=fxhxx from jx_t_fxhjd where fjdbh=@fjdbh               
   update cz_fxhtst set bh=@fxhbh,xx=@fxhxx where fjd=@fjdbh
                        
end;
同样的简单动作重复两次而已，结果一执行没立即出结果，就知道坏了，果然执行计划如下：
    STEP 1
        The type of query is SELECT.

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

(return status = 0)
用同样的语句
select fxhbh,fxhxx from jx_t_fxhjd where fjdbh in (4834305，27492);
只改动了fjdbh的检索条件，在PB执行立即出结果，我还没来得及切换过去执行showplan呢。这和我日常使用经验也是一致的。感觉jx_t_fxhjd平时用还是蛮正常的，但是关联起来检索、刷新奇慢。
sysmon我没用过，明天问问同事。
jx-t-fxhjd我还用DBCC tablealloc完全查了一下，没发现问题，真是没辙。

zhangyh123

其实查询计划中都用的是 表扫描。

你将大表执行如下操作，看速度如何。再进行更新操作。

select * into #1 from large_table where x in
  (select x from small_table )

如果这个操作快，就用 #1 代替大表

ycchenz

上面的方法也不行。
我将测试的存储过程又改了一下：
create procedure cz_p_fxhtst
as   
begin   
  declare @fjdbh decimal  
  declare @fxhbh decimal
  declare @fxhxx decimal

  select @fjdbh = 5213273
   select @fxhbh=fxhbh,@fxhxx=fxhxx from jx_t_fxhjd where fjdbh= @fjdbh

end;
速度极慢，当时系统又忙，等了一分钟执行完毕，计划任务仍然显示全表扫描。但只要把@fjdbh换成5213273，则命令飞快完成：
create procedure cz_p_fxhtst
as   
begin   
  declare @fjdbh decimal  
  declare @fxhbh decimal
  declare @fxhxx decimal

   select @fxhbh=fxhbh,@fxhxx=fxhxx from jx_t_fxhjd where fjdbh= 5213273

end;
就是说把根据常量改成根据变量检索，索引就失效了。
我们另外在这个主键上又建了一个索引，还是没用。打算明天把表BCP出来，重建后再倒进去。几百万条记录，够戗，还不一定有用！

zhangyh123

你有没有 MSN ?

jazy

你的问题还没解决阿？

“是说把根据常量改成根据变量检索，索引就失效了。 “

这个是比较奇怪了，没遇过这样的情况，你能否强制采用索引试试呢？



select @fxhbh=fxhbh,@fxhxx=fxhxx from jx_t_fxhjd where fjdbh= @fjdbh
(index 索引名 )


另外顺便问问你这个表采用的是什么锁方案？

ycchenz

我上午还是把表BCP出来，然后新建了一个结构相同的表，再倒到这个新表里，最后建主键，还是没用，执行上面测试的存储过程时还是用表扫描。SYBASE版本是12。0。5，行级锁。
zhangyh123 ：我的OICQ是30647274，MSN：cz__@yeah.net。这个也是我的邮箱。谢谢！

chenfeng825

强制索引应该能够解决问题！

li2002

关注！