Update Performance

• Download source - 101.1 KB

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:

1. UpdatePerformace.UI: provides the user interface
   • Form1.vb: Windows form to define and run test cases.
   • DBAccess.vb: Wrapper class for the different data access layers.

     Gets in the constructor the type of the data access layer to use.

   • ItemsData.xsd: The typed DataSet for the table "Items".
   • ModSettings.vb: Code to save and load control values to and from the user settings.
   • ModSql.vb: Some helper functions, mainly to create SQL statements.
   • app.config: Defines the connection string to the database, and the access to the web service.
2. UpdatePerformance.DA: provides the data access layer implementations
   • IInit.vb: Interface defining an Init Method, which is implemented by all data access layer implementations.

     The Init method is used to pass the connection string.

   • SQLDataAccess: Class without transactions using SqlDataAdapter.
   • SqlDataAccessTrans: Class with transactions using SqlDataAdapter and SqlTransaction.
   • SqlDataAccessComPlus: COM+ class with transactions using SqlDataAdapter and ContextUtil.
3. UpdatePerformance.WS: provides a web service that has data access methods
   • ISqlDataAccessWS.vb: The interface that is implemented by the web service.
   • SqlDataAccessWS.vb: Web service class with transactions using SqlDataAdapter and TransactionScope.
   • App.config: Configuration of the web service.

     When the web service is deployed to IIS, this file is renamed automatically to web.config.

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 1^st, 6^th, 11^th, etc. batches, the second thread takes iteratively the 2^nd, 7^th, 12^th, 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

• In Visual Studio, open the solution UpdatePerformance.sln.
• In UpdatePerformance.UI/app.config/ConnectionStrings/DB1, set the connectionString property to an existing SQL server database.
• Right-click on the solution and click on "Build Solution".
• Right-click on project UpdatePerformance.WS and click on "Publish". The following form appears:

  

  Click the button "Publish", and the web service will be deployed to the local IIS. Point a browser to the URL http://localhost/UpdatePerformance.WS/UpdatePerformance.WS.SqlDataAccessWS.svc to verify that the web service is up and running.

• Start the executable UI/bin/Release/UpdatePerformance.UI.exe

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

1. Measuring performance of insert data into a table with different number of data batch sizes, adapter batch sizes and threads.