Analyze the performance and flaws of the SQL Server Synchronization tool you wrote

Analyze the performance and flaws of the SQL Server Synchronization tool you wrote

1. C # Synchronous SQL Server database schema

2. C # Synchronizing data in a SQL Server database--Database synchronization tool [synchronizing new data]

By testing the synchronization program I wrote, I concluded: 1. The first time the program calls the SqlBulkCopy will take longer than 2. The synchronization program is relatively less time-consuming than the target machine

Test data:

DECLARE @varI varchar (200)
Set @varI =0

while (@varI <100000)
Begin
Set @[email protected]+1;
insert into [pink]. [dbo]. [Customers] ([Customername],[isnewdata]) VALUES (' Test ' + @varI, 1);
End

Statistics: (Unit: seconds)

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

In addition, a set of data for the next 1 million data is measured:

Source host inserting data time consuming 2分钟55秒

Destination host deletes data for 6 seconds

Sync program host on source host:
22.1482469
Sync program Host on destination host:

18.5495432

The defects of the program were discovered through testing:

When a second synchronization occurs, the limit is exceeded when the condition is queried to 100,000 records that already exist:

error : internal Error:server stack limit has been reached. Potentially deep nesting in your query, and try to simplify it.

Reason: This issue occurs because SQL Server limits the number of identifiers and constants that can is contained in a single expr Ession of a query. The limit is 65,535.

Problem code:

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 ();

Solution:

1. Divide the query criteria into multiple query conditions

2. Batch synchronization, such as 100 100-piece synchronization

Analyze the performance and flaws of your own SQL Server Synchronization tool that you wrote