Performance Test and Analysis of SQL Server 2000

Test Environment

Operating System: Windows Server 2003

CPU: Intel (r) Xeon (TM) CPU 3.00 GHz, 4-core

Memory: 2 GB, mainly used to run MySQL services

Hard Disk: CCISS 37g

File System: NTFS, supporting large files

Database Version: SQL Server 2000 sp3

NIC: 100 m

Network Environment: M LAN

Test Method

The data size can be divided into 10 thousand, 0.1 million, 1 million, and 10 million records.

Execute 2000 random queries on each data scale, which can prevent the database from directly returning the query results from the cache, and bring the query results closer to the actual query conditions. Take the system time as the Random Seed and perform the query operation randomly within the existing record range.

The query types include single-Table select, double-Table select, insert, update, and delete.

Each execution entry is the average execution time of a single query. The unit is subtle. If you want to view the overall execution time, multiply it by 2000.

Because the script inserts data very slowly, the SQL Server Client tool is used to test the efficiency is very low and it cannot ensure enough randomness, so the data insertion and test performance are completed by writing the corresponding program.

Enable binary Update log.

Test Record Test Data

Average execution time of local query operations (unit: microseconds)

Number of project records	Single Table select	Double table select	Insert	Update	Delete
10 thousand	295	2249	16292	2552	2681
0.1 million	302	134658	16406	2689	2790
1 million	524746	691188	16832	546313	501983
10 million	49000000	103000000	16882	49100000	49100000

Average cross-host query operation execution time (unit: microseconds)

Number of project records	Single Table select	Double table select	Insert	Update	Delete
10 thousand	986	3143	16431	3608	3497
0.1 million	1076	125462	17248	4087	3825
1 million	525524	709991	17514	552526	526370
10 million	49002000	103003000	18882	49102000	49104000

By the scale of tens of millions of records, SQL Server queries slowly and occupies up to 1.7 GB of memory.

The query is too slow, resulting in extremely low test efficiency. As a result, 2000 random queries cannot be performed, and only three queries can be used to calculate the average value.

Performance Comparison

Since the query time for millions and tens of millions is greatly different from that for 10 thousand and 0.1 million, it is difficult to display the legend normally.

Here, we only provide table data for comparative reference.

Average execution time of SQL Server local query operations (unit: microseconds)

Number of project records	Single Table select	Double table select	Insert	Update	Delete
10 thousand	295	2249	16292	2552	2681
0.1 million	302	134658	16406	2689	2790
1 million	524746	691188	16832	546313	501983
10 million	49000000	103000000	16882	49100000	49100000

MySQL MyISAM engine average execution time of local query operations (unit: microseconds)

Number of project records	Single Table select	Double table select	Insert	Update	Delete
10 thousand	178	247	694	141	170
0.1 million	195	271	705	157	186
1 million	3055	5164	746	4308	12373
10 million	8665	15259	803	11033	17224

Average execution time of SQL Server Cross-host query operations (unit: microseconds)

Number of project records	Single Table select	Double table select	Insert	Update	Delete
10 thousand	986	3143	16431	3608	3497
0.1 million	1076	125462	17248	4087	3825
1 million	525524	709991	17514	552526	526370
10 million	49002000	103003000	18882	49102000	49104000

Average execution time of MySQL MyISAM engine cross-host query operations (unit: microseconds)

Number of project records	Single Table select	Double table select	Insert	Update	Delete
10 thousand	358	484	1188	269	298
0.1 million	375	505	1227	283	322
1 million	3174	5293	1242	4142	7981
10 million	12302	19920	1261	12355	20449

Performance Analysis

1. As shown in the chart, SQL server has a very low query efficiency when executing a large-scale query of more than 1 million records on a common PC server, and it is almost impossible to carry out the query of 10 million businesses, each query takes more than 40 seconds.

2. The performance of SQL Server data insertion is slightly affected by the data size, which is similar to that of MySQL.

The main reason is that the inserted data is inserted from the end, and the size of the preceding data has little to do with it.

3. The larger the data size, the greater the performance difference between MySQL and SQL Server.

4. MySQL performance degrades linearly as the data size increases, while SQL Server degrades exponentially.

5. Since the performance of MyISAM engine and InnoDB engine is not much different after MySQL 5.0, I chose MyISAM engine to compare it with SQL Server.

6. Run the database on the low-configuration server and PC. We recommend using Linux and MySQL.

7,