|
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.
|
Copyright and use this code
The source code on this page and other samples at https://www.motobit.com/tips/
are a free code, you can use it as you want: copy it, modify it, use it in your products, ...
If you use this code, please:
1. Leave the author note in the source.
or
2. Link this sample from you page.
<A
Href="https://www.motobit.com/tips/detpg_Perfdata/"
Title="ADO, DAO, RDO, MDB, SQL, OLEDB
and ODBC comparison, performance test
of objects that can be
used for database access on
server-side in ASP."
>ASP/VBS database performance test and comparison.</A>
|