) Ado.net, LINQ to SQL and Entity Framework Performance Test Analysis

Recently, many documents have been written, leading to the growth in Word and Excel usage. Considering the low efficiency of the methods used to read and write databases in the project, which affects the user experience, we decided to test whether Microsoft's newly implemented LINQ and EF can improve the efficiency.

The test environment is of course my notebook. The limited speed and Hard Drive Speed must not be as good as that of the desktop, but at least three software and hardware environments with the same solution are guaranteed: Windows Server 2008, visual Studio 2008, ms SQL Server 2008, the latest one-color product.

The test is divided into six phases. The data volume increases step by step for 10, 10, 10 thousand, 0.1 million, and, respectively. The time consumed for reading, writing, changing, and deleting four basic operations is tested, the result is as follows (unit: seconds ):

Read and Write 10 data records for the first time
Read/write mode	Read duration	Add time consumed	Modification duration	Delete time consumed	Average time consumed
Current mechanism (simplified)	0.007	0.35	0.02	0.014	0.09775
LINQ to SQL	0.023	0.083	0.102	0.068	0.069
Entity Framework	0.238	3.084	0.009	0.006	0.83425

Read and Write 10 data records for the second time
Read/write mode	Read duration	Add time consumed	Modification duration	Delete time consumed	Average time consumed
Current mechanism (simplified)	0.002	0.034	0.011	0.020	0.01675
LINQ to SQL	0.003	0.011	0.043	0.058	0.02875
Entity Framework	0.004	0.006	0.005	0.004	0.00475

Operate 100 data records
Read/write mode	Read duration	Add time consumed	Modification duration	Delete time consumed	Average time consumed
Current mechanism (simplified)	0.005	0.202	0.103	0.062	0.093
LINQ to SQL	0.003	0.083	0.350	0.298	0.1835
Entity Framework	0.004	0.035	0.030	0.021	0.0225

Operate 1000 data records
Read/write mode	Read duration	Add time consumed	Modification duration	Delete time consumed	Average time consumed
Current mechanism (simplified)	0.044	2.086	1.056	0.720	0.9765
LINQ to SQL	0.006	0.805	3.035	2.925	1.69275
Entity Framework	0.010	0.392	0.296	0.209	0.22675

Operate 10000 data records
Read/write mode	Read duration	Add time consumed	Modification duration	Delete time consumed	Average time consumed
Current mechanism (simplified)	0.435	21.069	10.328	6.925	9.68925
LINQ to SQL	0.02	973	29.985	28.891	16.71725
Entity Framework	0.029	4.142	3.321	2.434	2.47925

Operate 100000 data records
Read/write mode	Read duration	Add time consumed	Modification duration	Delete time consumed	Average time consumed
Current mechanism (simplified)	4.525	213.603	100.668	82.203	100.25
LINQ to SQL	0.207	80.789	305.912	290.481	169.347
Entity Framework	0.387	42.402	38.497	24.36	26.4115

[Test Summary]

The test results in the first phase are very unexpected. The time required to operate data in ado.net and LINQ to SQL is controlled within 0.5 seconds, which is very fast. However, the Entity Framework performs very poorly in adding data in this step, since these five steps are continuous tests, adding data is the first step, and EF is delayed for three seconds during the first step! What is EF doing in the last three seconds?

From the second stage, the performance advantages and disadvantages are clearly displayed in front of us. From the second stage to the sixth stage, regardless of the size of the operation data, the consumption ratio in the figure is almost the same. Entity Framework stands out among the three solutions with extremely high efficiency, while the quick modification and deletion operations of LINQ to SQL consume almost 10 times of EF, the performance of ado.net in adding data is really unsatisfactory, which is also related to the underlying Writing of our project.

From the test results, we can see that the average efficiency of EF is 6 times that of LINQ to SQL except that EF is delayed for 3 seconds (initially considered as the initialization time) during the first operation, it is four times more efficient than the current project mechanism. It is not difficult to understand why Microsoft almost gave up the LINQ to SQL and fully supported EF.

[Analyze why Entity Framework is slow for the first time)

It takes a lot of time to create objectcontext and query data for the first time. Why? Is there any optimization method? This article provides a reasonable explanation.

The pie chart below shows the time ratio of various operations when the objectcontext is created for the first time and accessed by the objectcontext.

It can be seen that only one view generation operation takes 56% of the time. However, this operation only appears during the first query, the generated view is cached for future use. A sample of the view. CS file is as follows:

We can use edmgen2.exe to generate view. CS and add it to the Project for compilation. This will greatly reduce the duration of view generation operations. According to the test by the ADO. Net team, compiling the view by yourself will save about 28% of the time. However, the test results on my computer are not that ideal, about 8%.

 

 

Transferred from: http://cid-79cf5e75e6e5fd50.spaces.live.com/blog/cns! 79cf5e75e6e5fd50! 379. Entry