Thanks to Lada Simicek for this (and many other) tips.
How much faster is the selection of data from Microsoft Access database using SQL statement than DAO common statements?
Many programmers do not take advantage of using SQL statements when working with DAO databases. This is generally because of the work involved learning of SQL language, compared to using common DAO statements. On the other hand SQL is very much faster and more powerful then DAO statements.
This short example shows how much is SQL statement faster then DAO statement. This program will find each author's name from Biblio.mdb (you should find this file in VB directory) that begins on the letter "S" and whose year of born was after 1930. You can simply compare the speed using SQL statement or DAO statement.
Be sure that in your application directory is located the file Biblio.mdb or change the line:
Set dbData = dbWork.OpenDatabase (App.Path & "Biblio.mdb")
In Project|References select Microsoft DAO 3.51 Object Library or Microsoft DAO 2.51 Object Library.
Add two buttons to the form, and set their Caption properties to the following:
Command1 - DAO
Command2 - SQL
Then, copy this code:
Private dbWork As Workspace
Private dbData As Database
Private dbTabl As Recordset
Private dblTime As Double
Private dbTablSQL As Recordset
Private I as Long
Private Sub Command2_Click()
Debug.Print "_____________________________________________"
'//Start
dblTime = Timer
Set dbTablSQL = dbData.OpenRecordset("SELECT * FROM
" _
& " [Authors] WHERE [Author] Like 'S*' And [Year
Born] > 1930 ")
dbTablSQL.MoveLast
dbTablSQL.MoveFirst
For I = 0 To dbTablSQL.RecordCount - 1
Debug.Print " Au_ID: "
& dbTablSQL.Fields("[Au_ID]") _
& ", Author's
name: " & dbTablSQL.Fields("[Author]") _
& ", Born: "
& dbTablSQL.Fields _
("[Year Born]")
dbTablSQL.MoveNext
Next I
'//Stop
dblTime = (Timer - dblTime)
Debug.Print "SQL time: " & dblTime
dbTablSQL.Close
End Sub
Private Sub Command1_Click()
Debug.Print "_____________________________________________"
'//Start
dblTime = Timer
dbTabl.MoveLast
dbTabl.MoveFirst
For I = 0 To dbTabl.RecordCount - 1
If Left$(dbTabl.Fields("[Author]"),
1) = "S" And _
dbTabl.Fields("[Year Born]")
> 1930 Then
Debug.Print
" Au_ID: " & dbTabl.Fields("[Au_ID]") _
& ",
Author's name: " & dbTabl.Fields("[Author]") _
& ",
Born: " & dbTabl.Fields("[Year Born]")
End If
dbTabl.MoveNext
Next I
'//Stop
dblTime = (Timer - dblTime)
Debug.Print "DAO time: " & dblTime
End Sub
Private Sub Form_Load()
Set dbWork = DBEngine.Workspaces(0)
Set dbData = dbWork.OpenDatabase _
(App.Path & "\Biblio.mdb") '//Set
the path where _
the file Biblio.mdb is located
Set dbTabl = dbData.OpenRecordset("Authors",
_
dbOpenDynaset)
End Sub
Now, try it several times and compare the results.
Comments