You are Here: FAQ Scripting & Programming ASP Article #7

WebHosting Microsoft-Edition This Article is for 1&1 Microsoft Web Hosting Only.

Optimising database connections

Below are some hints and tips for optimising ASP pages with database connections and for avoiding errors or performance loss. These notes and examples are rather generic and they are valid for both, Microsoft Access and MS SQL databases.

Adjust your record sets

A simple possibility is using specifically adjusted record sets. In order to do so the RS object has to be created and its attributes have to be set before the actual query. This way cursor and transaction locks specifically optimised for the desired query can be used. Tables are only opened for read access most of the time; therefore it is usually sufficient to open a database as "ReadOnly". Furthermore data sets are normally read sequentially (from top to bottom) only. The record set cursor can deallocate data sets that have already been read if being opened with the OpenForwardOnly property.

The following code example demonstrates how these properties can be set.


              

Const adOpenForwardOnly = 0

Const adOpenKeyset = 1

Const adOpenDynamic = 2

Const adOpenStatic = 3

Const adLockReadOnly = 1

Const adLockPessimistic = 2

Const adLockOptimistic = 3

Const adLockBatchOptimistic = 4



strQuery = "select * from ExampleTable where firstname = 'John' "

Set oConn = Server.CreateObject("ADODB.Connection")

objConn.Open "BeispielDSN"

Set RS = Server.CreateObject("ADODB.Recordset")

RS.ActiveConnection = oConn

RS.CursorType = adOpenForwardOnly

RS.LockType = adLockReadOnly

RS.Open strQuery



'Displaying the data

While Not (RS.EOF)

  Response.Write RS("name") & "<BR>"

  RS.MoveNext

WEnd



'Close record set

RS.Close

Set RS = Nothing



'Close connection

oConn.Close

Set oConn = Nothing

Once a record set is no longer required it should be closed and deleted promptly. This way you can assure that your code is executed as quickly as possible. The same goes for the database connection (here: oConn) as well of course.

If you require certain data records again later on in your application, you can still copy the data into variables and close the connection at once.

Querying the number of matching data sets

In many cases the total number of matching data sets is required, e. g. "Your search resulted in 312 matches". A "raving" method for achieving this goal is to use dynamic (not ForwardOnly) record sets and the following (bad) code:


              

Do While Not RS.EOF

  i = i + 1

  RS.MoveNext

Loop

RS.MoveFirst

This way the whole result has to be kept in the server's memory . On top of that all data sets are being processed without making any use of the contained data at all. This is not the way to go!

There are two reasonable ways for determining the total number of data sets:

The RS.RecordCount property contains this number after RS.MoveLast has been called. RS.MoveFirst has to be executed again to read out the data in this case. No ForwardOnly cursors can be used and the result has to be kept in the server's memory until the record set has been closed. This method is therefore only suited for a small number of matching results.

The count method of the database is serving our purpose best:

RS.Open "SELECT Count(name) FROM ExampleTable WHERE name = 'John' " RS(0) contains the number of found data sets. This query can be cached by the system so subsequent queries of the actual data sets can be performed efficiently.

Restrictions concerning the embedding of Microsoft Access databases

Due to security reasons it is unfortunately not possible to query a Microsoft Access database through ODBC or OLEDB. If you want to run ASP.NET applications with database access you'll therefore have to make use of the Microsoft SQL server and the corresponding .NET framework classes (System.Data.SqlClient). This option is available in our Microsoft Developer plan without additional costs. In your 1&1 control center you will also find an option for importing your Access database into the MS SQL server.

Print Article

Feedback

Overview	Search
Sitemap	Glossary