Test the data access efficiency of a single database, multiple databases, single instance, and multiple instances in different situations

Recently, the company's project is preparing to optimize the system performance, hoping to check whether there is room for improvement in the database. The current stress test shows that the pressure on the database server is not high enough, when the Web server is not under great pressure, the front-end page access is very slow. check whether there is a way to make full use of the performance of the database server, so I made a single database, multiple databases

Recently, the company's project is preparing to optimize the system performance, hoping to check whether there is room for improvement in the database. The current stress test shows that the pressure on the database server is not high enough, when the Web server is not under great pressure, the front-end page access is very slow. check whether there is a way to make full use of the performance of the database server, so I made a single database, multiple databases

Recently, the company's project is preparing to optimize the system performance, hoping to check whether there is room for improvement in the database. The current stress test shows that the pressure on the database server is not high enough, when the Web server is not under great pressure, the front-end page access is very slow, and there is no way to make full use of the performance of the database server, so I made a single database, multiple databases, single instance, test the data access efficiency of multiple instances under different circumstances.

Test environment:

• CPU: Inter Core2 Quad, Q8300, 2.50 GHz;
• Memory: 4.00 GB
• System: Windows 7 32-bit
• Database System: SqlServer 2008, which has two instances: one is the default instance and the other is the named instance QE2.

Test data:

0.67 million real fund income data: the data in this table is stored in three databases. For details, see the following connection string Configuration:

Test content:

First, filter out all the fund code in the table, and then calculate the latest return date of each fund, the corresponding T-SQL code is as follows:

Declare @ max_fsrq datetime
Declare @ currentjdm varchar (10)
Declare @ temp table (jjdm2 varchar (10 ))
Declare @ useTime datetime
Set @ useTime = GETDATE ();

Insert into @ temp (jjdm2)
Select jjdm from [FundYield] group by jjdm order by jjdm asc

While EXISTS (select jjdm2 from @ temp)
Begin
Set @ currentjdm = (select top 1 jjdm2 from @ temp)
Select @ max_fsrq = MAX (fsrq) from [FundYield] where jjdm = @ currentjdm
Delete from @ temp where jjdm2 = @ curtailjdm
Print @ max_fsrq
End


Print't-SQL Execute Times (MS ):'
Print datediff (MS, @ useTime, getdate ())

Directly executing this T-SQL script takes the following time if the database table is not indexed:

T-SQL Execute Times (MS ):
58796

According to this function, A. net console program is written for testing. The test program directly uses ADO. NET without any data access framework. The following is the code for multi-thread testing. Other code is omitted:

Public static void Test2 (string connName1, string connName2)
{
System. Diagnostics. Stopwatch watch = new System. Diagnostics. Stopwatch ();
Watch. Start ();
String allJjdmList = "";
String connString = getConnectionString ();
// SqlConnection conn = new SqlConnection (connString );
// Conn. Open ();

String SQL = "select jjdm from [FundYield] group by jjdm order by jjdm asc ";
DataSet ds = getData (connString, SQL );
Int allCount = ds. Tables [0]. Rows. Count;
Int p = (int) (allCount * 0.5 );

System. Threading. Thread t1 = new System. Threading. Thread (new System. Threading. ParameterizedThreadStart (tp1 =>
{
For (int I = 0; I <p; I ++)
{
String jjdm = ds. Tables [0]. Rows [I] [0]. ToString ();

Object result = getSclar (ConfigurationManager. ConnectionStrings [connName1]. ConnectionString,
String. Format ("select MAX (fsrq) from [FundYield] where jjdm = '{0}'", jjdm ));
If (result! = DBNull. Value)
{
DateTime dt = Convert. ToDateTime (result );
// Console. WriteLine ("Thread 2 No {0}, jjdm [{1}] last FSRQ is: {2}", I, jjdm, dt );
}

AllJjdmList = allJjdmList + "," + jjdm;
}

Console. WriteLine ("Tread 1 used all time is (MS): {0}", watch. ElapsedMilliseconds );
}
));

System. Threading. Thread t2 = new System. Threading. Thread (new System. Threading. ParameterizedThreadStart (tp2 =>
{
For (int I = p; I <allCount; I ++)
{
String jjdm = ds. Tables [0]. Rows [I] [0]. ToString ();
// Whether using default or express, the difference is not big
Object result = getSclar (ConfigurationManager. ConnectionStrings [connName2]. ConnectionString,
String. Format ("select MAX (fsrq) from [FundYield] where jjdm = '{0}'", jjdm ));
If (result! = DBNull. Value)
{
DateTime dt = Convert. ToDateTime (result );
// Console. WriteLine ("Thread 2 No {0}, jjdm [{1}] last FSRQ is: {2}", I, jjdm, dt );
}

AllJjdmList = allJjdmList + "," + jjdm;
}

Console. WriteLine ("Tread 2 used all time is (MS): {0}", watch. ElapsedMilliseconds );
}
));

T1.Start ();
T2.Start ();
T1.Join ();
T2.Join ();

Console. WriteLine ("=== All thread completed! ======== ");

}

The test result is as follows:

For the first time, the database does not create an index and performs a full table scan:

------ Single database, single thread test ---------
Used all time is (MS): 59916
------ Same instance, dual database, single-thread test ---------
Used all time is (MS): 59150
------ Same instance, dual database, multi-thread test ---------
Tread 2 used all time is (MS): 51223
Tread 1 used all time is (MS): 58175
==== All thread completed! ==========
------ Dual-instance, dual-database, single-thread test ---------
Used all time is (MS): 58230
------ Dual-instance, dual-database, multi-thread test ---------
Tread 2 used all time is (MS): 52705
Tread 1 used all time is (MS): 58293
==== All thread completed! ==========

The second time, index is created for the database response field. The test result is as follows:

------ Single database, single thread test ---------
Used all time is (MS): 1721
------ Same instance, dual database, single-thread test ---------
Used all time is (MS): 1737
------ Same instance, dual database, multi-thread test ---------
Tread 2 used all time is (MS): 1684
Tread 1 used all time is (MS): 1714
==== All thread completed! ==========
------ Dual-instance, dual-database, single-thread test ---------
Used all time is (MS): 1874

------ Single database, single thread test ---------
Used all time is (MS): 1699
------ Same instance, dual database, single-thread test ---------
Used all time is (MS): 1754
------ Same instance, dual database, multi-thread test ---------
Tread 1 used all time is (MS): 1043
Tread 2 used all time is (MS): 1103
==== All thread completed! ==========
------ Dual-instance, dual-database, single-thread test ---------
Used all time is (MS): 1838
------ Dual-instance, dual-database, multi-thread test ---------
Tread 1 used all time is (MS): 1072
Tread 2 used all time is (MS): 1139
==== All thread completed! ==========

Test conclusion:

Comprehensive full table scan access and indexed access,

Single-threaded access:

• In the same database instance, dual databases do not show any advantages, and even a single database is slightly better than multiple databases;
• On two database instances, dual-instance and dual-instance instances must lag behind a single database instance;

Multi-threaded access:

• Dual-database instances are slightly behind single-database instances;

According to the conclusion, both dual-database and dual-instance comparison and single-instance or single-database do not reflect the advantage. It seems that the advantage of the former is not in access efficiency, a friend said, database instances are different services, with smaller control granularity and lower maintenance impact. However, I think the performance of Dual-database instances, dual-database instances, and multi-core CPUs should be similar to that of the two database servers. Why didn't they show their advantages? Maybe my test machine only has one disk. Here, disk IO becomes a bottleneck.

This test is meaningless, or the reason for this result, please kindly advise!

--------------------------------------------------------------

Accidental Detection:

1, some people say that frequent queries in the complete database for the highest efficiency, test found that in the query analyzer directly run the above T-SQL script, with the program to retrieve data from the database, there is no significant difference in the efficiency of re-processing and computing queries."The most efficient point of view is to write complex business logic in a stored procedure !", ADO. NET is as efficient as performing back-and-forth operations on data from the database. If complex character function compute and large batches of cyclic operations are added, the storage process efficiency is not necessarily high.

2. When using programs for frequent database operations, using a connection object or using a new connection object in each method has always been a tough issue, I thought it would be faster to use a connection object for frequent data operations? The test code provided in this article contains the following statements:

// SqlConnection conn = new SqlConnection (connString );
// Conn. Open ();
Comment out these statements.Use your own connection object, And cancel comments,You can use only one connection object. There is no difference in efficiency!

The reason may be that ADO. NET automatically uses the connection pool. In fact, different programs use a connection, so there is no difference in operational efficiency.

Subsequent tests

Test on the Real Server. We found that the test conclusion is different. We have server A, with 16 cores and 32 GB memory. The other server B has 8 cores and 16 GB memory. There is an SqlServer instance on server A and two identical databases; there is an SqlServer instance and A database on server B. The test result is as follows:

------ Single database, single thread test ---------
Used all time is (MS): 650
------ Same instance, dual database, single-thread test ---------
Used all time is (MS): 418
------ Same instance, dual database, multi-thread test ---------
Tread 2 used all time is (MS): 221
Tread 1 used all time is (MS): 223
==== All thread completed! ==========
------ Dual-instance, dual-database, single-thread test ---------
Used all time is (MS): 1283
------ Dual-instance, dual-database, multi-thread test ---------
Tread 1 used all time is (MS): 228
Tread 2 used all time is (MS): 542
==== All thread completed! ==========

We can see that the same instance and multiple databases still have obvious advantages, while multithreading has a greater advantage. Due to the large performance gap between the two servers, the dual-instance test did not show any advantages, however, multithreading is better than single-instance and single-database!

Why are the conclusions of PC and server tests inconsistent? It may still be related to computing power. The PC's computing load is too large and has lost the significance of testing.