devxlogo

Faster client-side ADO 2.1 Recordset with the OPTMIZE dynamic property

Faster client-side ADO 2.1 Recordset with the OPTMIZE dynamic property

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.Recorsetrs.CursorLocation = adUseClientrs.Open "Authors", CONNSTR, adOpenStatic, adLockReadOnly, adCmdText' create a client-side index on the Author fieldrs.Fields("Author").Properties("OPTIMIZE").Value = True' quickly sort on the Author fieldrs.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.

devx-admin

Share the Post: