What's more about the efficiency of using LINQ to operate databases? (supplementary test: under the same conditions, the speed of writing to a database is faster than that of dataset)

recently, some people who are interested in or not interested in LINQ often talk about database operation efficiency using LINQ. Once I ask, "Are you doing the test, or cloud?" They often pull data from the east to the west, so that they can't get real evidence. Instead of making guesses over there, it would be better to do a test, and the test is extremely simple.

Machine configuration and development tools: CPU/P e5200 2.5 GHz

Memory/2 GB

Operating System/Microsoft Windows XP SP3

Database/Microsoft SQL Server 2005

Framework/. NET Framework 3.5 SP1

Development tools/Microsoft vs 2008 SP1

Data Table:Ipinfo[Ipid (Identity), Ipfrom, ipto, iplocation, ipcity, iptonumber, ipfromnumber]

Number of data entries: 346000

 

1. query test (query all fields. For more information about fields, see Query 10 million data records)

Ado. Net query test (Here, sqldatareader is used to read data and list <entity> is returned.)

Note: The bottom 0.78124 seconds indicates the number of seconds consumed.(Multiply by 1000 in milliseconds), As shown in the following figure.

Note: It takes a little longer to perform the first CRUD operation, and the later time will be much shorter. This is because Microsoft SQL server has a cache and you can remember our previous operations, the execution speed will be greatly improved in subsequent operations.

Next let's take a lookSqldatareader:

At this time, if the database table suddenly adds records, let's look at the query time:

Result: adding a record does not affect the database query.

Ado. Net datasEt reads 10 million data (additional test)

This result is a little surprising, and it is slower than the speed of LINQ.46.7 MsI tested the results many times. The Code is as follows:

Code

Public   Static Dataset get10wdata_dataset ()
{

Sqlconnection Conn =   New Sqlconnection ( " Data Source = ***; initial catalog = groupsell; user id = **; Password = ** " );
String Strsql =   " Select top 100000 * From ipinfo " ;
Dataset DS =   New Dataset ();
Sqldataadapter adaper =   New Sqldataadapter (strsql, Conn );
Try
{
Adaper. Fill (DS );
Return DS;
}
Catch
{
Return   Null ;
}
Finally
{
Conn. Dispose ();
Adaper. Dispose ();
}
}

 

 

Test (Returns list <entity>)

First Query

Result: The first query (10 million records) by LINQ is very time-consuming, which is nearly 3.3 seconds slower than sqldatareader. The first query here is the first query performed on the database after I started ms SQL service.

Query later:

The database suddenly adds 1 million records and then queries (this part of the query takes a little longer than the general time consumed by the LINQ query, about 12.5ms ):

As a result, we will find that the speed has improved a lot, so we don't have to worry about it in the project. At least every time you execute the SQLCode, You have run the test, so you don't have to worry about efficiency. Here we will find that, under the same conditions, the query is executed by LINQ in general.Efficiency is not much slower than ADO. net, 0.046885 s(Multiply by 1000 in milliseconds). However, I don't know if you can accept this result?

2. Update operation test (modify other fields of this record except ipid based on the value of an ipidNote that the table contains346000Data entries)

Ado. Net update test:

Test the LINQ update function:

Result: The difference is 0.0468737 s, which is similar to the above time.

3. insert operation test (Add a new record. Note that the table contains346000Data entries)

Ado. Net insert Test

LINQ insert Test

Result: Difference0.0468837 S is almost the same as above

4. delete operation test (Delete a record based on the value of ipid. Note that the table contains346000Data entries,)

Ado.net delete test

LINQ delete test

Result: The difference is 0.0624996 S. I am not very clear about the reason why the delete operation takes a little time in the LINQ operation. I tested it several times. This is the result.

Final Result: I don't know whether this test result can recognize the efficiency of LINQ. This also verifies Lao Zhao's statement: "For most Internet applications, the performance bottleneck lies in database access ". In fact, the execution efficiency of C # language is not low, but the efficiency is objective. Instead of talking about how to use LINQ, let's take a test seriously. Compared with the convenience provided by LINQ in development, I don't think the time difference is too much.