We help IT Professionals succeed at work.

VBA SQL DBF Query - How can I speed this up?

I have a VBA script that does a SQL query of a Visual Fox Pro (DBF) file. I have to use wildcards in the query. The query runs really slow (approx 40 seconds). Any ideas on how to speed things up. See attached code. If I specify a single record, results are almost instant. Since I dont have the ability to add a key field to the DBF file, I fear I am out of luck getting any more speed.

Dim con_var As String
var1 = "AB"
var = "______________" & var1 & "%"
 
Set cont = CreateObject("ADODB.Connection")
Set rcont = CreateObject("ADODB.Recordset")
constr = "Driver={Microsoft Visual FoxPro Driver}"
constr = constr & ";UID="
constr = constr & ";SourceDB=\\server\path"
constr = constr & ";SourceType=DBF"
constr = constr & ";Exclusive=No"
constr = constr & ";BackgroundFetch=No"
constr = constr & ";Collate=Machine"
constr = constr & ";Null=No"
constr = constr & ";Deleted=Yes"
constr = constr & ";Mode=Read;"
cont.Connectionstring = constr
cont.Open
 
c1SQL = "select keys, field1, field2 "
c1SQL = c1SQL & "from 'datafile' "
c1SQL = c1SQL & "where keys like '"
c1SQL = c1SQL & var
c1SQL = c1SQL & "'"
 
rcont.CursorLocation = 3
DoEvents
rcont.Open c1SQL, cont, 3, 3
rcont.fields("keys").Properties("optimize").Value = True
rcont.Sort = "keys"
rcont.movefirst
cr = 2

Open in new window

Comment
Watch Question

Manager, IT
CERTIFIED EXPERT
Commented:
Only index could help you in this situation.. but as you are using wildcards, index wouldn't be of this much help...
You may see some speed gain from using the OLE DB Provider for VFP rather than the much older VFP ODBC driver.

Microsoft OLE DB Provider for Visual FoxPro
http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en
index it on substr(keys,15)

and

where clause should be substr(key,15) = ""' & var & "'"

use = and not LIKE. And don't use the wildcards. Underscore or percentage.

i am assuming 14 underscores. otherwise change the index key and the where clause.

Author

Commented:
Not sure I follow you on the index suggestion? I dont have the option of changing anything in the datafile setup. The substr suggestion in the query was helpful. Not sure it made things any faster, but the code looks cleaner.
Sorry. I thought you could create the index. You can try to get the index created from whereever you got the database from. It will make a huge difference to the speed of your select. If you need, I can attach an exe that will create the index for you. Though from your code, it looks like you have readonly access to the tables. In which case nothing can be done.

Author

Commented:
Yes. It is read only. I sort of figured I was stuck here. Just looking for any way to speed things up.