Home » Tip Bank » Security » Visual Basic

Language: VB6,VBS
Expertise: Intermediate

Jun 10, 2000

ADO 2.1 added a new, important dynamic property to the Field object, the OPTIMIZE property. If you have a client-side Recordset and you set this property to True, ADO will create an Index for the specified field, and will then automaticaly use that index for any Find, Sort, and Filter operations on the Recordset:

Const CONNSTR = "Provider=Microsoft.Jet.OLEDB.4;Data Source=C:\Biblio.mdb"
Dim rs As New ADODB.Recorset

rs.CursorLocation = adUseClient
rs.Open "Authors", CONNSTR, adOpenStatic, adLockReadOnly, adCmdText
' create a client-side index on the Author field
rs.Fields("Author").Properties("OPTIMIZE").Value = True
' quickly sort on the Author field
rs.Sort = "Author"

The local index will exist until you close the client-side Recordset. Some informal benchmarks show that you can speed up sort operation by a factor of 10-20 or more, depending on the size of the Recordset. Of course, creating a local index takes some time, so this technique is convenient especially if you are going to do a lot of searching, filtering, and sorting.