ASP/VBS database performance test and comparison.
ActiveX RegEdit.   ActiveX User account Manager   Pure-ASP Upload
Export MDB/DBF from ASP   Active LogFile   WebChecker   ActiveX/ASP Multi Dictionary object
 See 
 also 		  IISTracer, real-time IIS monitor and logging tool.
 Huge ASP file upload with progress bar. 


Do you like this article?
Please, rate it
and write review!
Rated:
by Aspin.com users
What do you think?
Scripts.com Rate Script!
 Top messages
 4.5.2002 9:16:43 
 Send an email from ASP (WSH) using VBSscript, CDONTS and Outlook. (nbsp;ASP / ASP.Net, WSH, VBScript, Email)
 22.3.2003 19:18:41 
 Read and write SQL image data, store binary file to sql table. (nbsp;WSH, Database, Conversion, VBScript)
 12.6.2003 9:14:29 
 Download multiple files in one http request (nbsp;File & data transfer, VBScript)

 ASP/VBS database performance test and comparison. 

 Areas>ASP / ASP.Net>Performance tests
 Areas>ASP / ASP.Net>Database

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
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
Full connection string
 SQL6.5 - OLEDB
 ADODB - SQLOLEDB		 OLEDB  4.73  4.62 


Connect = "Provider=SQLOLEDB;Data Source=" & SQLServer
Conn.Open Connect, UID, PWD
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
Full connection string
 MDB - ODBC
 ADODB - MSDASQL		 ODBC with DSN  133.39  4.00 


Connect = "DSN"
Conn.Open Connect
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
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
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
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)
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.
 
 

See also for 'ASP/VBS database performance test and comparison.' article:

     Best way to get HTML page from a recordset Performance tests of six different VBS codes, each of them generates the same HTML table.
     Recordset convertor performance test Recordset convertor is great object which enables direct output of binary database data from ASP page as DBF/MDB.
     DLL object/class with methods or include with functions ? Time tests to make decision between ASP include and VB/C++ object in ASP/VBS.

If you like this page, please include next link on your pages: 
<A
 Href="http://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>
     IISTracer - IIS ISAPI real-time monitor IISTracer is a real-time monitoring tool for Microsoft IIS, which will show/log you what is happenning on IIS server right now. It let's you reveal problems with long-running scripts (.asp, .cgi, cfm...), hang-up states and low resource situations and lets you stop long-running requests (uploads/downloads).      ActiveX User account Manager - Set of simple objects for creating, deleting, and managing user accounts, groups, servers and domains in the Windows NT environment.
     Active log file - Hi-performance text file logging for ASP/VBS/VBA applications. Lets you create daily/weekly/monthly log files with variable number of logged values and extra timing and performance info.      ActiveX windows registry editor - Intuitive, easy to use COM interface to windows registry. Set of classes to read/enumerate/modify windows registry keys and values from ASP, VBS and T-SQL.
     ActiveX/ASP Multi Dictionary object - Free-threaded hi-speed dictionary algorithm with unique/nonunique keys (map/multimap). Connect to another dictionary object in the same process. Lock and Unlock methods to synchronize tasks (application scope). Share ASP Application/Session objects.      Export DBF/MDB from ASP - Conversion from recordset to MDB/DBF. Direct binary output of MDB or DBF files from ASP pages with one row of code.
     Pure-ASP upload - lets you upload files using Pure ASP VBS code (using multipart/form-data and input type=file).      ByteArray - Works with safearray binary data (VT_UI1 | VT_ARRAY) - save/restore binary data from disk, find, work with code pages, convert to string/hexstring(SQL).
     WebChecker - Checks http, https, ftp and gopher internet connections in regular intervals. Lets you monitor web site functionality (uptime). Enables restart or notification on problems.      HTTPLog ISAPI filter - Lets you log incomming/outgoing http header and document data to separate files. Monitor of IIS service input/output.
© 1996 – 2008 Antonin Foller, PSTRUH Software, e-mail help@pstruh.cz