purplesoup
asked on
How to run performance tests for .NET Db Libraries
I want to write some tests to compare performance of accessing a SQL Server database using different .NET Db libraries - in particular OleDb and SQL Server.
Can anyone point me to a list of dos and don'ts for running performance tests? I'm thinking that perhaps there is some way to turn off caching for example to ensure it is the actual Db access that is being measured.
Thanks for any help you can give.
Can anyone point me to a list of dos and don'ts for running performance tests? I'm thinking that perhaps there is some way to turn off caching for example to ensure it is the actual Db access that is being measured.
Thanks for any help you can give.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The first group of tests uses a Command object which has an INSERT statement assigned, parameters set, and 500 rows inserted.
First I used a table with one ID UNIQUEIDENTIFIER PRIMARY KEY and 989 DATETIME NULL columns.
The average SQL performance to insert 500 rows was 13.62 seconds, the average OLEDB time was 2.86.
Next I used a table with one ID UNIQUEIDENTIFIER PRIMARY KEY and 350 NVARCHAR(10 NOT NULL columns.
The average SQL performance to insert 500 rows was 5.11 seconds, the average OLEDB time was 2.19.
I've included the StringInsert code to give an example of what was run.
Clearly, OleDb is actually faster than the native SQL library. Can anyone explain why this might be??
The second group of tests use a DataAdapter which is populated with data from a SQL table containing around 10,000 rows.
At first just the 10,000 rows were returned, with results as follows:
OLE SQL
4.39 2.53
4.21 2.71
4.26 2.55
4.40 2.69
4.32 2.62
This makes the SQL 1.65 times faster than the OLEDB.
I then modified the test so that as well as returning 10,000 rows, 5000 rows are updated, 500 rows inserted and the data adapter Update command is then run.
Test results are as follows (in seconds):
OLE SQL
90 84
87 82
96 95
83 89
-------------
89 87.5
As you can see, the SQL is marginally faster, but not much.
I had thought things would be much faster with SQL - for example this test
http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/fadb2742-d75a-49fb-a839-b2d4a9183998
claims SQL to be 2.5 times faster than OLEDB.
Can anyone give some comments on my tests to see whether I am missing something?
Open in new window