Analyze the performance and defects of the Self-written SQL Server synchronization tool.

Analyze the performance and defects of the Self-written SQL Server synchronization tool.
Analyze the performance and defects of the Self-written SQL Server synchronization Tool

1. C # synchronize SQL Server database Schema

2. C # synchronize data in the SQL Server database-Database Synchronization tool [synchronize new Data]

 

Test the synchronization program I wrote and draw a conclusion: 1. The first time the program calls SQLBulkCopy, it will take a long time. 2. The time consumption of the synchronization program on the target machine is relatively small.

Test data:

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

While (@ varI <100000)
Begin
Set @ varI = @ varI + 1;
Insert into [pink]. [dbo]. [MERs] ([CustomerName], [IsNewData]) values ('test' + @ varI, 1 );
End

Statistical data: (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, we tested a set of data of the following 1 million pieces of data:

It takes 2 minutes and 55 seconds to insert data to the source host.

It takes 6 seconds for the destination host to delete data

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

18.5495432

Program defects discovered through tests:

During the second synchronization, when the query condition reaches 0.1 million existing records, the limit is exceeded:

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

Cause: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.

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. Divides a query condition into multiple query conditions.

2. Batch synchronization, for example, synchronization of 100 million entries

 

Are there any gadgets that can analyze SQL for free?

Are you talking about the analysis statement? It depends on the specific table structure. Generally, the database itself must provide such a tool, for example, the performance analysis of SQL statements run after the informix database is set explain on is generated into a text file, and the mysql database provides the explain SQL statement to view the statement performance, the explain analysis result shows the statement performance intuitively, and can optimize the database or change the statement accordingly.

Which of the following commonly used management and development tools in SQL server 2000?

Database Design; failover cluster; management of client servers; replication and recovery of enterprise data backup; monitoring of Server performance and activities; DTS service; troubleshooting and Security Management of SQL Server Enterprise servers; data Mining and building of Enterprise Warehouse Analysis Systems