分析下自己写的SQL Server同步工具的性能和缺陷

1. C#同步SQL Server数据库Schema

2. C#同步SQL Server数据库中的数据--数据库同步工具[同步新数据]

通过测试我写的同步程序，得出结论：

1.程序第一次调用SQLBulkCopy会耗时较长

2.同步程序放在目标机器在耗时方面相对少些

测试数据：

declare @varI varchar(200)
set @varI=0

while(@varI<100000)
begin
set @varI=@varI+1;
insert into [pink].[dbo].[Customers] ([CustomerName],[IsNewData])values ('test'+@varI, 1);
end

统计数据：（单位：秒）

source--> destination
sync program host on source host:
19.1431558
1.8603441
1.4992773
1.7913309
sync program host on destination host:
20.1563122
2.3704811
3.2282182
1.1696436
1.5312439
sync program host on non-source and non-destination host:
13.072742
1.8705590
1.2103618

另外测了下100万条数据的一组数据：

source主机插入数据耗时2分钟55秒

destination主机删除数据耗时6秒

sync program host on source host:
22.1482469
sync program host on destination host:

18.5495432

通过测试发现了程序的缺陷：

当第二次同步时，查询条件到10万条已存在的记录时，就已经超过限制：

报错：internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

原因：This issue occurs because SQL Server limits the number of identifiers and constants that can be contained in a single expression of a query. The limit is 65,535.

问题代码：

string whereClause = " where ";
            while (readerSource.Read())
            {
                isSourceContainsData = true;
                whereClause += " " + primaryKeyName + "!='" + readerSource[primaryKeyName].ToString() + "' and ";
            }
            whereClause = whereClause.Remove(whereClause.Length - " and ".Length, " and ".Length);
            readerSource.Close();

解决方案：

1。把查询条件分成多个查询条件

2。批量同步，比如100条100条的同步