执行环境：Win2003SP2 + MSSQLSERVER2005SP3一个数据采集、同步程序，7*24小时地在固定间隔内（60秒）从固定IP地址采集数据，该数据为xml格式的SQL INSERT或UPDATE或DELETE内容。要将xml文件中的内容更新到数据库中。xml格式大概为：
<?xml version="1.0" encoding="UTF-8"?>
<operation>
<update tablename="monitor" id="2313620">
<column name="date">2009-12-10 13:25:01.681</column>
<column name="value">35.7</column>
</update>
<update tablename="monitor" id="2313638">
<column name="date">2009-12-10 13:27:35.967</column>
<column name="value">35.7</column>
</update>
<delete tablename="monitor" id="2036821"/>
<delete tablename="monitor" id="2036825"/>
<insert tablename="monitor" id="2410581">
<column name="date">2009-12-11 07:46:20.538</column>
<column name="value">34.9</column>
</insert>
</operation>update元素表示SQL-UPDATE，delete元素表示SQL-DELETE，insert元素表示SQL-INSERT，column元素表示要操作的列，其中name属性表示列名称，column的元素值表示列值。【注意：xml文件中SQL DML语句的顺序是没有规律的，其体现的是实时的变化情况，我上面举的列子只是为了让大家看清楚才故意显得井然有序】。之前的做法是：解析xml，生成对应的SQL DML语句，然后写到q.sql批处理文件中，xml解析结束后调用命令行sqlcmd程序执行该批处理文件。例如上面解析后生成如下SQL语句：
update monitor set date = '2009-12-10 13:25:01.681',value = 35.7 where id = 2313620
update monitor set date = '2009-12-10 13:27:35.967',value = 35.2 where id = 2313638
delete monitor where id = 2036821
delete monitor where id = 2036825
insert monitor (id,date,value) values(2410581,'2009-12-11 07:46:20.538',34.9)
现在的问题是：因为业务变化，近期数据量徒增（而且要一直持续下去），每次（60秒一次）采集的xml文件经过解析后生成的SQL语句将近4万条，这4万条SQL根本无法在60秒内写入数据库，导致更新周期越来越长，未执行的xml文件越积越多，根本无法同步！请大家在解答的时候注意：
1.要求必须严格按照xml文件中的顺序进行更新操作，xml文件中的顺序体现了业务随时间的变动，不允许分别将insert、update、delete语句归类然后并发执行。
2.不要考虑xml解析的速度，因为现在的xml解析速度已经能保证最慢在2秒内拼出4万条SQL语句并写到q.sql文件中。关键是剩下的58秒怎么样把q.sql语句执行完。
3.不要考虑对insert的优化，因为对insert可以使用BCP或BULK INSERT，关键是对update和delete怎么优化，因为貌似没有针对update和delete的“BCP或BULK INSERT”。如果不拼成SQL语句去批执行而使用底层技术也行，这个项目本身就是VC++做的。
4.被更新的目的表上有一个索引，不能删除该索引，因为该表还要接受网站页面的大量查询，所以还要考虑更新时带来的锁表问题。
5.不要考虑服务器硬件（CPU、内存、RAID），不要考虑日志问题，已设为简单恢复模式。
<?xml version="1.0" encoding="UTF-8"?>
<operation>
<update tablename="monitor" id="2313620">
<column name="date">2009-12-10 13:25:01.681</column>
<column name="value">35.7</column>
</update>
<update tablename="monitor" id="2313638">
<column name="date">2009-12-10 13:27:35.967</column>
<column name="value">35.7</column>
</update>
<delete tablename="monitor" id="2036821"/>
<delete tablename="monitor" id="2036825"/>
<insert tablename="monitor" id="2410581">
<column name="date">2009-12-11 07:46:20.538</column>
<column name="value">34.9</column>
</insert>
</operation>update元素表示SQL-UPDATE，delete元素表示SQL-DELETE，insert元素表示SQL-INSERT，column元素表示要操作的列，其中name属性表示列名称，column的元素值表示列值。【注意：xml文件中SQL DML语句的顺序是没有规律的，其体现的是实时的变化情况，我上面举的列子只是为了让大家看清楚才故意显得井然有序】。之前的做法是：解析xml，生成对应的SQL DML语句，然后写到q.sql批处理文件中，xml解析结束后调用命令行sqlcmd程序执行该批处理文件。例如上面解析后生成如下SQL语句：
update monitor set date = '2009-12-10 13:25:01.681',value = 35.7 where id = 2313620
update monitor set date = '2009-12-10 13:27:35.967',value = 35.2 where id = 2313638
delete monitor where id = 2036821
delete monitor where id = 2036825
insert monitor (id,date,value) values(2410581,'2009-12-11 07:46:20.538',34.9)
现在的问题是：因为业务变化，近期数据量徒增（而且要一直持续下去），每次（60秒一次）采集的xml文件经过解析后生成的SQL语句将近4万条，这4万条SQL根本无法在60秒内写入数据库，导致更新周期越来越长，未执行的xml文件越积越多，根本无法同步！请大家在解答的时候注意：
1.要求必须严格按照xml文件中的顺序进行更新操作，xml文件中的顺序体现了业务随时间的变动，不允许分别将insert、update、delete语句归类然后并发执行。
2.不要考虑xml解析的速度，因为现在的xml解析速度已经能保证最慢在2秒内拼出4万条SQL语句并写到q.sql文件中。关键是剩下的58秒怎么样把q.sql语句执行完。
3.不要考虑对insert的优化，因为对insert可以使用BCP或BULK INSERT，关键是对update和delete怎么优化，因为貌似没有针对update和delete的“BCP或BULK INSERT”。如果不拼成SQL语句去批执行而使用底层技术也行，这个项目本身就是VC++做的。
4.被更新的目的表上有一个索引，不能删除该索引，因为该表还要接受网站页面的大量查询，所以还要考虑更新时带来的锁表问题。
5.不要考虑服务器硬件（CPU、内存、RAID），不要考虑日志问题，已设为简单恢复模式。
就楼主所言：通常的解析成sql语句，然后执行的方式肯定是不行的了：生成的sql送到sqlserver会引起数据库引擎先解析成执行计划——这是成本很高的操作——估计58秒有相当的时间浪费在这上面了，再执行！如果不考虑业务实现的现有逻辑构架，可以考虑将上面的insert、delete、update的表操作换成对存储过程来调用执行，应该能提高不少的性能！再有：我想楼主的insert肯定不会引起锁定性能下降；delete可以考虑延迟删除——将行标志位删除，稍后执行删除——这样锁定成本会下降；update就没有办法了个人的建议，高手们给个意见
一次性exec ?
思路是,将xml的数据导入到T_Command临时表中,将数据字段一行行写入
然后将原表的内容copy到内存表,再使用内存表+游标(T_Command),将内存表的数据处理掉,然后更新回原表create table monitor(
ID int primary key ,
date datetime,
value numeric(18,4)
)
insert into monitor
select 1, '20090101', 10.0 union all -- for update
select 2, '20090202', 20.0 union all -- for update
select 21, '20080101', 00.0 union all -- for delete
select 22, '20080102', 00.0 -- for deleteselect * from monitor设：p.sql的内容已导入到表T_Command：if object_id('T_Command') is not null
drop table T_Commandcreate table T_Command(
seqid int primary key identity(1,1),
flag smallint, -- 1: insert; 2: delete; 3: update
mid int, -- monitor.id
date datetime, -- monitor.date
value numeric(18,2) -- monitor.value
)create index IX_MID on T_Command(MID)truncate table T_Commandinsert into T_Command
select 3, 1, '2010-12-10 13:25:01.681', 11.1 union all
select 3, 2, '2010-12-11 13:25:01.681', 22.2 union all
select 2, 21, null, null union all
select 2, 22, null, null union all
select 1, 31, '2010-12-10 13:25:01.681', 100.0select count(1) from T_Command
insert into T_Command
select flag, mid, date, value from T_Command--
set nocount on
declare @buffertable table(
mid int primary key, -- ==>monitor.id
flag int default 0, -- 1: delete, 0: none
date datetime, -- ==>monitor.date
value numeric(18,2) -- ==>monitor.value
)insert into @buffertable(MID, date, value)
select ID, date, value from monitor a
where exists(select 1 from T_Command b where a.ID = b.mid)declare cursor_data cursor for
select flag, mid, date, value from T_Command order by seqid
for read onlydeclare @flag smallint, @mid int, @date datetime, @value numeric(18,2)
open cursor_data
fetch next from cursor_data into @flag, @mid, @date, @value
while @@fetch_status = 0
begin
-- 1: insert; 2: delete; 3: update
if @flag = 1
if not exists(select 1 from @buffertable where mid = @mid)
insert into @buffertable values(@mid, 0, @date, @value)
else
update @buffertable set flag = 0, date = @date, value = @value where mid = @mid
else
if @flag = 2
--delete @buffertable where MID = @mid
update @buffertable set flag = 1 where MID = @mid
else
if @flag = 3
update @buffertable set date = @date, value = @value where MID = @mid fetch next from cursor_data into @flag, @mid, @date, @value
end
close cursor_data
deallocate cursor_data-- insert
insert into monitor(ID, date, value)
select MID, date, value from @buffertable a
where not exists(select 1 from monitor b where a.mid = b.id)
-- update
update a set
a.date = b.date,
a.value = b.value
from monitor a, @buffertable b
where a.id = b.mid
--delete
delete a from monitor a, @buffertable b
where a.id = b.mid and b.flag = 1
-- ok
select * from monitor
QQ：315054403 MSN:[email protected]
谢谢这位朋友提供的方案。
还有一个我没有说的细节，就是xml中update的字段不是固定的，被更新的字段数量经常变动，不是固定更新几个字段，所以，如果把更新写入到内存表中，那么内存表与实体表JOIN后更新那部分(SET子句)就没办法写了。
>>我试了试,用下面的方法,3W的操作,1-2秒可以了,
这个1-2秒时间,我说的是:游标+循环时间,
最后的insert, update, delete时间未算,具体未测试.
while @@fetch_status = 0
begin
-- 1: insert; 2: delete; 3: update
if @flag = 1
begin
if not exists(select 1 from @buffertable where mid = @mid)
insert into @buffertable values(@mid, 0, @date, @value)
else
update @buffertable set flag = 0, date = isnull(@date, date), value = isnull(@value, value) where mid = @mid
end
else
if @flag = 2
--delete @buffertable where MID = @mid
update @buffertable set flag = 1 where MID = @mid
else
if @flag = 3
update @buffertable set date = isnull(@date, date), value = isnull(@value, value) where MID = @mid
fetch next from cursor_data into @flag, @mid, @date, @value
end
只有本sql文件只操作同一个表和某条记录，所以就可以不同的sql文件并发
2: 不管MSSQL,还是Oracle都自带有导入导出工具，它的速度1W/s是没问题的（自己的开发机测试情况）。
3：简单的批量SQL命令: insert, update, delete，在几十W条的数据量操作都1-20s内的。
4: 数据量大了，用分区表，我没试过MSSQL分区表的导入在千万级别的导入。在Oracle的分区表，导入10W条的增量数据到千万级量存在分区表，速度是10s内（有一个全局，一个本地索引）。
为啥用分区表，不使用分区表，当数据在上了千万级别后，再导入数据，速度成倍增加。当然也可能我没做好优化工作有关，用了分区表后，基本导入速度跟第一次建表的导入速度相当。其它的LZ自己想吧。
----------------------------------------
1.这是个数据实时同步的程序，数据源提供方给我们的数据接口说明中明确强调“必须按xml中元素的顺序”执行，也就是说这个规定限制了不能多线程并发执行，xml中元素的顺序是代表更新顺序的，不能违背这个顺序，因为数据相当重要！
2.xml中可能更新的表有5个，但是只有一个表是频繁更新的，其它几个表很少更新。而且在UPDATE的时候列不是固定的，没有规律。
---------------------------------
TO： ERR0RC0DE朋友，真的非常感激你！这些参与的朋友中，只有你与众不同、知难而上，并非常认真地设计出了方案，谢谢你！
（一）不能使用MSSQL自带的导入导出工具的原因是：
1.其只能实现INSERT，而无法实现UPDATE和DELETE；
2.这是个1分钟*60*24*7不间断的同步程序，不能手动导入。
（二）那个频繁更新的表的数据量会稳定在30万行左右，因为xml中有DELETE，会经常删除过期的无效数据，所以没有分区。其它的表只有数百行而已，不用考虑。
----------------------------------------------------
这个方法不太可靠，因为UPDATE有时会指定某列的值为NULL。主题中提到的当前做法正是因为这些很多不确定的因素，所以才只好把xml拼成SQL-DML语句来执行，最要命的是必须按【顺序】执行，这是规则，根本不能多线程！
---------------------------------------------
在程序中创建进程CreateProcess() WIN32 API，在进程中调用命令行的sqlcmd.exe程序来执行q.sql批处理文件。
【主题中提到的当前做法正是因为这些很多不确定的因素，所以才只好把xml拼成SQL-DML语句来执行，最要命的是必须按【顺序】执行，这是规则，根本不能多线程！ 】可以多线程的：xml转sql时，把涉及同一条记录的操作，按时间顺序写入同一个sql文件
这样得到的sql文件是可以并发提交执行的如果这样sql文件会很多，则可以把涉及某100条记录的操作，按时间顺序写入同一个sql文件，即对这100条记录操作的语句，不会在别的sql文件出现
这样得到的sql文件也是可以并发提交执行的
调整这两个属性看看是否能够缩短数据写入时间.PS:如果每个.SQL脚本在一个transaction里面完成,是否可以考虑将INSERT/UPDATE/DELETE按照对应的顺序分开分块执行,如先按照xml中预定的顺序INSERT所有数据,然后在按照xml的UPDATE顺序UPDATE所有数据,最后一次性执行DELETE操作?这样是否可行.
在逻辑上来讲,一个transaction中间执行的话,会锁你的表的,所以在一个transaction里面分INSERT/UPDATE/DELETE这样执行的话,应该不会影响你最终的执行结果的.除非你重新设定了READ的隔离级别,允许脏读和跳读.
Create and Modify SQL Server Indexes Properly
http://www.devx.com/getHelpOn/Article/10016/1763/page/1第三页有谈到填充因子的问题/.
只有INSERT语句可以使用BCP，但是UPDATE 和DELETE怎么使用BCP呢？我主题已经提到了，“3.不要考虑对insert的优化，因为对insert可以使用BCP或BULK INSERT，关键是对update和delete怎么优化，因为貌似没有针对update和delete的“BCP或BULK INSERT”。”-------------------------------------------------------
这个不是我们能决定的，对方的规则指明了数据格式为xml，是为了跨数据库平台。
-------------------------------------------------------
这个尚未考虑，因为现在硬件已经足够强了，所以主要还是集中在怎样优化UPDATE和DELETE的算法。------------------------------------------------------
我们的表没有主键，因为这个表中的数据相当重要，类似股票价格那样，是用来做交易的，我们绝对不能改，只能按照xml中的指定执行更新。
---------------------------------------------------------
表中的索引是按ID升序排列的聚集索引，去掉了该索引后，性能没有明显改善。这个表没有任何我们自己添加的列，与xml中规定的表结构完全相同，我们接受xml后只是忠实地执行它所包含的指令（INSERT、UPDATE、DELETE）而已，所以连这个ID也是xml中指定的，并不是表中的标识列。------------------------------------------------------
“把涉及同一条记录的操作”--这样判断起来会很耗时的。
我的意思是说：将xml的数据先导入(BCP/buck insert)的一个临时表中，然后根据临时表中的字段进行update, insert和delete。例：
一：xml数据：
<update tablename="monitor" id="2313620">
<column name="date">2009-12-10 13:25:01.681</column>
<column name="value">35.7</column>
</update>
<delete tablename="monitor" id="2036821"/>
<insert tablename="monitor" id="2410581">
<column name="date">2009-12-11 07:46:20.538</column>
<column name="value">34.9</column>
</insert>二：
那么生成一个import.txt文件：(flag:1=insert;2:update;3:delete)
flag,xml
2,'update monitor set value = xx where id = xx'
3,'delete monitor where id = xx'
1,'insert monitor values(xx)'三：通过bcp/buck insert将import.txt文件导入到临时表Temp_Monitor（不是真的临时表，是真表，但只作临时存储）四：
定义一个表值变量：@buffer，当作一个内存表，先将monitor，根据Temp_Monitor.ID insert到@buffer, 然后根据Temp_Monitor的数据，一条条解析出来，然后更新到@buffer中
最后将@buffer更新回原表。
然后，你说这种方法会出现update monitor set 后面的字段无法写赋值的情况。这个不是问题来的。
在insert, delete是不会出现这情况，只有flag=update会出现。
那么就解析一里面的数据：
比如在Temp_Monitor有这两句语句：
'update monitor set f1 = 'new_1_1', f2 = 'new_1_2' where id = 1'
'update monitor set f1 = 'new_2' where id = 2'原数据表中数据：
id, f1, f2
1, 'old_1_1', 'old_1_2'
2, 'old_2', 'old_2_2'这个问题可以将在同步数据程序在生成import.txt中，丰富import.txt里面的数据，再生成字段：
UpdateFlag字段
该字段为varchar(X), X值等于monitor需更新字段的数量，表示了第X个字段是否需要更新
该字段的值为字符：0或1
那么在生成import.txt语句：
'update monitor set f1 = 'new_1_1', f2 = 'new_1_2' where id = 1'
应该可以知道，我需要更新f1，f2字段和对应的值
那UpdateFlag='1100000000' (设X=10)
然后再写入F1的值'new_1_1', F2='new_1_2'
再导入，也就是Temp_monitor比原表多了三个字段：OPSeqID(操作顺序ID)，flag(操作类型:insert,update,delete)和updateFlag,其它跟原表字段相同有了updateflag，那就知道哪些值是从原表取，哪些是从Temp_monitor取，这样就完成上面赋值操作了。完了，打字真累。
一：程序运行始，取数据从表中，将该表全部load到一个hash表中，这个很简单的吧。好像STL有这个hash，效率高不高不知，也可以自己写一个。二：来数据后，一边解析一边更新内存的数据。三：更新完，生成txt，导入到临时表，truncate 原表，insert新数据。
OK这样操作，只要写好了那个hash列表，速度应该巨快。哈
我以前写过，就一个PK ID，数据也差不多30W条，1-2秒就OK了，不过数据只有一个手机号码而已。
你那表全部字段加起来估计不过超过20字段，然后每字段算20字段，400字节，算512
30W=300,000*512=153,600,000
光那数据需占用153M，然后再加上hash操作的内存，估计上200M，呵呵。我觉得可以接受。
这两个对于频繁更新的表的影响很大的说,