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.

See also  Comparing different methods of testing your Infrastructure-as-Code

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist