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

分析下自己写的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条的同步

 

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页