Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: VB6,VBS
Expertise: Intermediate
Jun 10, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.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.
Francesco Balena
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date