ASP/VBS database performance test and comparison.

Performance test configuration

- P200, 128MB RAM
- Win NT 4.0 SP5
- SQL 6.5 SP5
- IIS 4

- DAO 3.5 SP1
- MDAC 2.1 SP2
- RDO 2.0
- format of MDB - Access 97 (not Access 2000)
- SQL server on the same machine as IIS

Database connections

The table contains used database connections, source code for open the connection, connection strings and connection string from opened connection. Predefined constants:


Public Const UID = "sa"
Public Const PWD = ""
Public Const Driver = "{SQL Server}"
Public Const Database = "Test"
Public Const DSN = "Test"
Public Const SQLServer = "(local)"
Public Const MDBFile = "E:\inetpub\wwwroot\WebTest\db\Test.mdb"

Used database connections. Open time for the connection.

Server Engine - Client engine
Class - Provider

Note

Script
Time [ms] 4)

Kernel+User
Time [ms] 5)

Open Connection VBS code

SQL6.5 - ODBC
ADODB - MSDASQL

ODBC DSN-less

4.73

4.66


Connect = "driver=" & Driver & ";server=" & SQLServer
Conn.Open Connect, UID, PWD

Hide

Provider=MSDASQL.1;
User ID=sa;
Connect Timeout=15;
Extended Properties="DRIVER=SQL Server;
SERVER=(local);
UID=sa;
APP=Internet Information Server;
WSID=DOMA";
Locale Identifier=1029

Full connection string

SQL6.5 - ODBC
ADODB - MSDASQL

ODBC with DSN

4.65

4.50


Connect = "DSN=" & DSN & ";Database=" & Database & ""
Conn.Open Connect, UID, PWD

Hide

Provider=MSDASQL.1;
User ID=sa;
Connect Timeout=15;
Extended Properties="DSN=Akcie;
SERVER=(local);
UID=sa;
APP=Internet Information Server;
WSID=DOMA;
DATABASE=Akcie;
AutoTranslate=No;
TranslationOption=100;
StatsLog_On=0;
Regional=No";
Locale Identifier=1029

Full connection string

SQL6.5 - OLEDB
ADODB - SQLOLEDB

OLEDB

4.73

4.62


Connect = "Provider=SQLOLEDB;Data Source=" & SQLServer
Conn.Open Connect, UID, PWD

Hide

Provider=SQLOLEDB.1;
User ID=sa;
Locale Identifier=1029;
Extended Properties="Data Source=(local)";
Connect Timeout=15;
Use Procedure for Prepare=1;
Auto Translate=True;
Packet Size=4096;
Workstation ID=DOMA

Full connection string

MDB - ODBC
ADODB - MSDASQL

ODBC DSN-less

154.12

13.52


Connect = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & MDBFile & ";"
Conn.Open Connect

Hide

Provider=MSDASQL.1;
Connect Timeout=15;
Extended Properties="DBQ=E:\inetpub\wwwroot\WebTest\db\Test.mdb;
Driver={Microsoft Access Driver (*.mdb)};
DriverId=281;
FIL=MS Access;
MaxBufferSize=2048;
PageTimeout=5;
";
Locale Identifier=1029

Full connection string

MDB - ODBC
ADODB - MSDASQL

ODBC with DSN

133.39

4.00


Connect = "DSN"
Conn.Open Connect

Hide

Provider=MSDASQL.1;
Data Source=Test;
Connect Timeout=15;
Extended Properties="DSN=Test;
DBQ=E:\inetpub\wwwroot\WebTest\db\Test.mdb ;
DriverId=281;
FIL=MS Access;
MaxBufferSize=2048;
PageTimeout=5;
";
Locale Identifier=1029

Full connection string

MDB - OLEDB
ADODB - Microsoft.Jet.OLEDB.4.0

JetOLEDB

121.48

49.97


Connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MDBFile & ";"
Conn.Open Connect

Hide

Provider=Microsoft.Jet.OLEDB.4.0;
Persist Security Info=False;
User ID=Admin;
Data Source=E:\inetpub\wwwroot\WebTest\db\Test.mdb;
Mode=Share Deny None;
Extended Properties="";
Locale Identifier=1029;
Jet OLEDB:System database="";
Jet OLEDB:Registry Path="";
Jet OLEDB:Database Password="";
Jet OLEDB:Engine Type=4;
Jet OLEDB:Database Locking Mode=0;
Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False

Full connection string

SQL6.5 - ODBC
RDO - -

RDOSQL, connection pooling

28.04

17.23


Connect = "driver=" & Driver & ";server=" & SQLServer & ";" & _
  "uid=" & UID & ";pwd=" & PWD

Set Conn = CreateObject("MicrosoftRDO.RdoConnection2.0")
Conn.Connect = Connect 
Conn.cursordriver = 2
Conn.EstablishConnection rdDriverNoprompt

Hide

DRIVER=SQL Server;
SERVER=(local);
UID=sa;
PWD=;
APP=Internet Information Server;
WSID=DOMA;
DATABASE=Akcie

Full connection string

SQL6.5 - ODBC
RDO - -

RDO-SQL, without connection pooling

56.68

30.75


Connect = "driver=" & Driver & ";server=" & SQLServer & ";" & _
  "uid=" & UID & ";pwd=" & PWD 

Set Conn = CreateObject("MicrosoftRDO.RdoConnection2.0")
Conn.Connect = Connect 
Conn.cursordriver = 2
Conn.EstablishConnection rdDriverNoprompt

Hide

DRIVER=SQL Server;
SERVER=(local);
UID=sa;
PWD=;
APP=Internet Information Server;
WSID=DOMA;
DATABASE=Akcie

Full connection string

MDB - DAOJet
DAO - -

native DAO connection to MDB

121.00

83.22


Set WS = CreateObject("DAO.DBEngine.35").Workspaces(0)
Set Conn = WS.OpenDatabase(MDBFile,False,True)

Hide

E:\inetpub\wwwroot\WebTest\db\Test.mdb

Full connection string

Used objects.
Object	Script Time [ms] 4)	Kernel+User Time [ms] 5)
Set Conn = CreateObject("ADODB.Connection")	1.16	1.11
Set Conn = Server.CreateObject("ADODB.Connection")	4.96	2.85
Set Conn = CreateObject("MicrosoftRDO.RdoConnection2.0")	2.46	2.28
Set WS = CreateObject("DAO.DBEngine.35").Workspaces(0)	112.00	74.27
Set Conn = WS.OpenDatabase(MDBFile,False,True)	14.76	11.25

The best open time has ADODB connection to SQL. The ADO-MSSQL connect time does not depend on connection method (ODBC or OLEDB, DSN or DSNless). Of course - the connection has connection pooling on.

Read access times.

The consumed times of data retrieval statements was done on table with around 2000 rows and 10 columns with mixed data (Int, Money, Char and VarChar).
There was three ways to retrieve data from database:
1. SQL 6.5 :

Set RowCount n
SELECT IntColumn|* From Table
Set RowCount 0


Set RS = Conn.Execute|OpenResultset (SQL)

2. MDB by SQL

SELECT TOP n IntColumn|* From Table


Set RS = Conn.Execute|OpenRecordset (SQL)

3. Native DAO


Set RS = Conn.OpenRecordset (Table,dbOpenTable)
RS.Index = "..."

Read time (open recordset + get its contents).
Conn Type	Cols 1)	Rows 2)	Script Time [ms] 4)	Kernel+User Time [ms] 5)
RDO-ODBC-SQL	1	1	5.61	3.00
DAO-MDB(Native)	1	1	4.21	3.97
DAO-MDB(SQL)	1	1	9.42	8.81
ADO-SQLOLEDB-SQL	1	1	15.92	8.91
ADO-MSDASQL-MDB	1	1	26.04	9.21
ADO-MSDASQL-SQL	1	1	25.54	16.83
ADO-JetOLEDB-MDB	1	1	27.34	24.43
RDO-ODBC-SQL	10	1	13.32	9.91
ADO-SQLOLEDB-SQL	10	1	19.73	11.31
DAO-MDB(SQL)	10	1	13.32	12.71
ADO-MSDASQL-MDB	10	1	38.05	15.92
ADO-MSDASQL-SQL	10	1	30.34	19.23
ADO-JetOLEDB-MDB	10	1	60.89	57.78
RDO-ODBC-SQL	1	20	12.12	9.31
DAO-MDB(SQL)	1	20	17.00	16.42
ADO-SQLOLEDB-SQL	1	20	23.53	17.53
ADO-MSDASQL-MDB	1	20	38.95	20.63
ADO-MSDASQL-SQL	1	20	34.05	23.74
ADO-JetOLEDB-MDB	1	20	35.05	32.14
DAO-MDB(Native)	10	20	9.95	9.70
RDO-ODBC-SQL	10	20	21.33	16.92
ADO-SQLOLEDB-SQL	10	20	28.64	19.73
DAO-MDB(SQL)	10	20	20.93	20.32
ADO-MSDASQL-SQL	10	20	40.26	28.84
ADO-MSDASQL-MDB	10	20	62.49	29.34
ADO-JetOLEDB-MDB	10	20	70.70	66.69

      RDO consumes minimum of client processor time for one row and one column. Some of the time takes also MS SQL (5.61-3.0 ms).
      The minimum of all processor time with more rows or columns takes native DAO. For 20 rows/10 columns - native DAO is up to 2 times faster than second - RDO and more than 2 times faster than ADO with OLEDB for SQL or OLEDB for ODBC/SQL.
      Native DAO is more than 6 times faster than ADO/JetOLEDB.
      ADO - the difference between OLEDB for ODBC/SQL and OLEDB for SQL are minimal for one row (3%), but the difference grows with more rows and columns to up to 50% for 20 rows/10 columns.

Real performance.

Previous table contains processor times consumed by the data objects and SQL server. Next table contains real performance (request per second for 10 clients) of the data access methods.

Read time (open recordset + get its contents).
Conn Type	Data Count 3)	Rows 2)	requests [1/s]
Plain ASP	0	0	50.00
ADO_SQLOLEDB_SQL	1	1	22.00
ADO_MSDASQL_SQL	1	1	22.00
RDOSQL	1	1	14.00
DAO-MDB(Native)	1	1	14.00
DAO-MDB(SQL)	1	1	12.00
ADO_MSDASQL_MDB	1	1	9.00
ADO_JetOLEDB_MDB	1	1	7.80
ADO_MSDASQL_SQL	1	20	18.00
ADO_SQLOLEDB_SQL	1	20	18.00
DAO-MDB(Native)	1	20	13.00
DAO-MDB(SQL)	1	20	11.50
DAO-MDB(Native)	10	1	9.00
RDOSQL	10	1	6.00
DAO-MDB(SQL)	10	1	5.00
ADO_SQLOLEDB_SQL	10	1	4.50
ADO_MSDASQL_SQL	10	1	3.00
DAO-MDB(Native)	10	20	6.00
RDOSQL	10	20	4.00
DAO-MDB(SQL)	10	20	3.20
ADO_SQLOLEDB_SQL	10	20	3.20
ADO_MSDASQL_SQL	10	20	2.30
ADO_JetOLEDB_MDB	10	20	1.30
ADO_MSDASQL_MDB	10	20	1.30

      Real performance is touched by both open connection to database and retrieve data from database. The capital meaning has also threading model and allowed number of concurent users of the data access. Database open time (workspace creation) gives main performance loss to DAO (DAO 120 ms, RDO 28ms, ADO 4ms).
       The best performance for one simple SQL statement on a page has ADO with SQL (The performance is same for both ODBC and OLEDB).
      The best performance with strong database access (10 SQL statements retrieving 20 rows on a page) has native DAO, second is RDO and then ADO/OLEDB. The strong database access also separates performance of OLEDB for ODBC/SQL and OLEDB for SQL - page with OLEDB for SQL is up to 40% faster.

Results.

      The best performance of data access with small number of SQL statements on a page has ADO with OLEDB for SQL (Of course, I did not test ORACLE :-). Also MS says that this is its "mainstream" of data access in future.
      I do not recommend OLEDB for SQL for updates of database - we have significant problems with the provider when we are using such connections to open dynamic recordsets. The better way for update connections is OLEDB/ODBC provider.

      Use native DAO instead JetOLEDB or ODBC/MDB if your selection is MDB.

      The tests was provided with open/close connection on each page.
      I have also test one ADO connection cached in application object. The performance of the global connection was as same as with open connection in each page : 22req/s for 1SQL/1row, 3req/s for 10SQls,20rows.
      The same performance is because open connection that is cached in the connection pool takes only 2/3 time of retrieve data from simple recordset and connection cached in Application object must also open more SQL-conections to work with more clients.
      OLEDB for ODBC ADO connection stored in application has problems with it's functionality. The connection raises 'Error - Connection is busy with another ...' for 10 clients.

      THIS DOCUMENT IS A TEST OF DATA ACCESS TECHNOLOGIES, NOT TEST OF DATA ENGINES. Native MDB engine has much time better performance than SQL engine for one client (other test says up to 40 times for reading and 20 times for write). But the engine has limitted number of clients. The problem appears with more complex SQL statements.

1) Number of data columns retrieved by recordset - 10 columns means Select * ..., 1 column means Select IntColumn ...
2) Number of data rows retrieved by recordset - 1 row means retrieve of one row (value = RS("IntColumn")), 20 rows means Loop (Do while not RS.Eof ... Loop)
3) Data count means number of SQL calls in the page. 1 means open database + open one recordset and retrieve the data. 10 means open database + 10 times open recordset and retrieve the data.
4) Script time - TickCount [ms] of the operation. The time has two parts - Kernel+User time of calling thread and execution time of all other processes.
The Kernel+User time of DAO and JetOLEDB is as same as Script Time, but the times are different for retrieving data by ADO/SQL or RDO/SQL - the Script Time contains also Kernel+User time of SQL server.
5) Kernel+User time - times of calling thread in [ms]
The time has only relative meaning - because the times was on the pure configuration (P200/128M), the real times will be better.

ASP/VBS database performance test and comparison.MOTOBIT.COM

Performance test configuration

Database connections

Read access times.

Real performance.

Results.

See also

Copyright and use this code