Introduction
This project compares the performance of different data access layer implementations in a 2 tier application. The presentation layer is a Windows Forms application, and the data access layer is implemented in four different ways:
SQLDataAccess: Class without transactions using SqlDataAdapter.SqlDataAccessTrans: Class with transactions using SqlDataAdapter and SqlTransaction.SqlDataAccessComPlus: COM+ class with transactions using SqlDataAdapter and ContextUtil.SqlDataAccessWS: Web service class with transactions using SqlDataAdapter and TransactionScope.
Each test inserts 10,000 records into the table Items which has 6 fields. The tests are run by modifying the following parameters:
- Batch size: Number of records that are sent at once to the data access layer. The 10,000 records are split into batches of equal sizes, and each batch is sent separately to the data access layer.
- Adapter batch size: According to the .NET documentation, the property
UpdateBatchSize gets or sets the number of rows that are processed in each round-trip to the server. - Number of threads: The number of concurrent threads that send data to the data access layer.
This project tries to answer the following questions:
- How does the performance depend on the above parameters?
- Which is the best performing data access layer implementation?
Code Description
Projects
The solution UpdatePerformance.sln has 3 projects:
UpdatePerformace.UI: provides the user interface
UpdatePerformance.DA: provides the data access layer implementations
UpdatePerformance.WS: provides a web service that has data access methods
Parallelization
RunTest
The method RunTest builds a number of DataSets for the given batch size and fills them with random numbers:
ReDim mDataSets(nrDataRows \ saveBatchSize - 1)
For j As Integer = 0 To UBound(mDataSets)
mDataSets(j) = New ItemsData
AddRandoms(mDataSets(j), saveBatchSize)
Next
The RunParallelTest method is called which takes as argument the parallel method to use and the number of threads.
RunParallelTest
The RunParallelTest method starts the stopwatch, invokes the parallel method (ParallelFor or ParallelThreads) and stops the stopwatch:
mNrThreads = nrThreads
mStopWatch.Restart()
m.Invoke(Me, New Object() {nrThreads})
mStopWatch.Stop()
ParallelFor
The ParallelFor method uses the .NET Parallel.For method to start the given number of threads. Each thread runs the method RunTestLoop:
Dim p As New ParallelOptions() With {.MaxDegreeOfParallelism = nrThreads}
Parallel.For(0, nrThreads, p, AddressOf RunTestLoop)
ParallelThreads
The Parallelthreads method creates the given number of threads, starts the threads, and waits until all have finished. Each thread runs the method RunTestLoop:
Dim threads(nrThreads - 1) As Threading.Thread
For i = 0 To UBound(threads)
threads(i) = New Threading.Thread(AddressOf RunTestLoop)
Next
For i = 0 To UBound(threads)
threads(i).Start(i)
Next
For i = 0 To UBound(threads)
threads(i).Join()
Next
RunTestLoop
The RunTestLoop method does the actual work of sending the DataSets to the database. If there are 5 threads, the first thread takes iteratively the 1st, 6th, 11th, etc. batches, the second thread takes iteratively the 2nd, 7th, 12th, etc. batches, and in the same manner work the remaining threads.
While Not mCancel AndAlso batchNr < mDataSets.Length
Dim ds As DataSet = db.Update(mDataSets(batchNr), _
mDataSets(batchNr).Tables(0).TableName, mAdapterBatchSize)
batchNr += mNrThreads
End While
Prerequisites
This project has been tested with Visual Studio 2010 and SQL Server 2008 R2. Later versions of these products should also work. An SQL server database must exist with a user being a member of the roles db_datareader, db_datawriter and db_ddladmin.
Compiling and Launching
Test Form
Upon launching the executable, the main form as shown at the top of the article, appears.
Description of the fields:
- Save batch size step: Save batch size will be incremented by this value up to the next divisor of the total rows. For example: Total rows = 100 and save batch size step = 1, then the batch sizes will take the values of 1, 2, 4, 5, 10, 20, 25, 50, 100. If save batch size step = 2, then the batch sizes will take the values of 2, 4, 10, 20, 50, 100.
- Adapter batch sizes: Values of different
SqlDataAdapter.UpdateBatchSize separated by ";". Ex: 1;10;100 - Number threads: Number of threads will iteratively double from 1 up to this value, for example for a value of 8, tests will have 1, 2, 4 and 8 threads respectively.
- Total rows: Number of rows to save in each test. These will be saved in batches by the different threads.
- Parallel method: Parallelization method to use, .NET
Parallel.For or spawning threads. - Data access class: The data object to use for saving.
First thing to do, is to create the table "Items" in the database.
This may be done by selecting the menu File/Recreate table. The button "Load from db" retrieves the data from the table "Items" and displays it on the grid. The button "Truncate db" truncates the table "Items", that means all data is deleted. All values on the form are saved to the user settings when the form is closed, and these values are loaded from the user settings when the form is opened the next time. By pressing the button Reset, all values on the form are reverted to the original settings.
Choose some values for the fields on the form. For example, to reproduce the test cases described below, set the following values:
- Save batch size step:
1 - Adapter batch sizes:
1;10;100 - Number threads:
256 - Total rows:
10000 - Parallel method:
ParallelFor - Data access class:
Check all
To prepare the test cases, press the button "Prepare". This will display the different test cases on the grid, as the following picture shows:
Upon pressing the button "Run" the test cases are executed row by row starting at the first column, and after having completed all rows, the execution proceedes to the next column. The elapsed time is displayed in the respective cell of each test case, with up to millisecond precision.
With the button "Pause", the execution may be paused, and with the button "Resume", the execution may be resumed. Logging is written with System.Trace.WriteLine(). The logging output can be viewed with DebugView.exe.
Discussion of the Results
The test cases were run on a computer with Intel Core 2 Quad CPU, 2.4GHz, 3GB RAM. The presentation layer, data access layer, web service and database were all running on the same computer.
The following test cases were run:
| 1 | ParallelFor | UpdatePerformance.DA.SQLDataAccess | adapter batch size=1 |
| 2 | ParallelFor | UpdatePerformance.DA.SQLDataAccess | adapter batch size=10 |
| 3 | ParallelFor | UpdatePerformance.DA.SQLDataAccess | adapter batch size=100 |
| 4 | ParallelFor | UpdatePerformance.DA.SqlDataAccessTrans | adapter batch size=1 |
| 5 | ParallelFor | UpdatePerformance.DA.SqlDataAccessTrans | adapter batch size=10 |
| 6 | ParallelFor | UpdatePerformance.DA.SqlDataAccessTrans | adapter batch size=100 |
| 7 | ParallelFor | UpdatePerformance.DA.SqlDataAccessComPlus | adapter batch size=1 |
| 8 | ParallelFor | UpdatePerformance.DA.SqlDataAccessComPlus | adapter batch size=10 |
| 9 | ParallelFor | UpdatePerformance.DA.SqlDataAccessComPlus | adapter batch size=100 |
| 10 | ParallelFor | UpdatePerformance.UI.PX.Service1Client | adapter batch size=1 |
| 11 | ParallelFor | UpdatePerformance.UI.PX.Service1Client | adapter batch size=10 |
| 12 | ParallelFor | UpdatePerformance.UI.PX.Service1Client | adapter batch size=100 |
The results are saved in the Excel file UpdatePerformance.zip.
The following overall chart, shows the performance for a reduced number of varying batch sizes and threads.
The line colors correspond to the row colors in the above table.
The following conclusions can be extracted from the above chart:
- For a batch size of 1, the web service is the slowest, followed by the COM+ class. This is the case in many applications, where a user inserts records one at a time, or a process handles messages one by one and inserts them to the database.
- From a batch size of 10 on, the performance improves considerably.
- The simple class without transactions (
SQLDataAccess) has irregular performance. It is anyway not used in real applications, and therefore will not be discussed further. - It is not clear how the performance depends on the number of threads. This will be investigated further.
In order to investigate the influence of the number of threads, we have to look more closely to some specific batch sizes.
The next three charts display the performance results of batch sizes 10, 100 and 1000 respectively.
The following conclusions can be extracted from the above charts:
- Performance is best with 4 threads.
- Performance is deteriorating with 1 or 2 threads and with 32 threads and above.
- The best performance is obtained by
SqlDataAccessTrans followed by SqlDataAccessComPlus and finally by SqlDataAccessWS. - Higher values of adapter batch size have an almost negligible better performance.
Overall Conclusion
It is worth making batches of about 10 records or more, and updating the whole batch. Threads between 4 and 32 have a good performance, but this certainly depends on the number of cores and memory available. The adapter batch size has little influence on the performance.
History
- Measuring performance of
insert data into a table with different number of data batch sizes, adapter batch sizes and threads.
