Table A
ID,Date_to_Fill
1,2000-01-01
2,2000-01-01
3,2000-01-01
..
10000000,2000-01-01Table B:
ID_Low, ID_High, Datevalue
1, 5, 2008-01-01
6,100,2008-03-02
101,9999, 2009-03-03
10000,1000000, 2009-05-05查询如下Update TableA set
Date_to_Fill=
(select Datevalue from TableB b
where
TableA.Id between b.id_low and b.id_high)表a 有 10000000列
表b 100000列ID是int类型, 主键( clustered, unique index)
ID_LOW, ID_HIGH也是int, 虽然唯一,但只建了non unique index(这个有影响吗?)在很小的A表上(10000条纪录)试,也比较慢,几分钟才能出来select出用于更新的5000条ID<=b.ID_HIGH and ID>b.ID_LOW 也试过,同样慢各位遇到过类似情况吗?
多谢! 这个查询照这样估计几天也运行不出来,着急啊!看执行计划,100%都是在访问索引
ID,Date_to_Fill
1,2000-01-01
2,2000-01-01
3,2000-01-01
..
10000000,2000-01-01Table B:
ID_Low, ID_High, Datevalue
1, 5, 2008-01-01
6,100,2008-03-02
101,9999, 2009-03-03
10000,1000000, 2009-05-05查询如下Update TableA set
Date_to_Fill=
(select Datevalue from TableB b
where
TableA.Id between b.id_low and b.id_high)表a 有 10000000列
表b 100000列ID是int类型, 主键( clustered, unique index)
ID_LOW, ID_HIGH也是int, 虽然唯一,但只建了non unique index(这个有影响吗?)在很小的A表上(10000条纪录)试,也比较慢,几分钟才能出来select出用于更新的5000条ID<=b.ID_HIGH and ID>b.ID_LOW 也试过,同样慢各位遇到过类似情况吗?
多谢! 这个查询照这样估计几天也运行不出来,着急啊!看执行计划,100%都是在访问索引
UPDATE TableA
SET TableA.Date_to_Fill = TableB.Datevalue
FROM TableB
WHERE TableA.Id BETWEEN TableB.ID_Low AND TableB.ID_High
into result
from
a LEFT OUTER JOIN B
on a.id >=b.id_low and a.id<=b.id_high这是我写的,1楼是我同事写的a表即使只有10000行,也要5分钟左右,b表就是10万行
跟查询无关。。
这么多的update,IO性能不高的话,当然慢了。
即使不update,用以下语句也很慢Date_to_fill 没有索引Select a.id, b.*
into result
from
a LEFT OUTER JOIN B
on a.id >=b.id_low and a.id<=b.id_high
--将表重建下试试看!!
UPDATE A SET A.Date_to_Fill = B.Datevalue
FROM TableA A
INNER JOIN TableB B
ON A.Id BETWEEN B.ID_Low AND B.ID_High