how to optimize RS for speed, suggestions requested

[Mike Scirocco]

I have a large table of IP Addresses that I resolve the host names for.
There are over 300,000 entries in the table. I found the code I'm using
online, I'm okay with VB6 but I'm a beginner with Access and I'm hoping
that I can get some suggestions on how to speed up this code.

Thanks,
Mike

Dim dbsCurrent As Database
Dim qdfPassThrough As QueryDef, qdfLocal As QueryDef
Dim rs As Recordset, strMessage As String
'Open a database from which QueryDef objects can be created
Set dbsCurrent = OpenDatabase("db2.mdb")
'Create pass-through query to retrieve data from a SQL Server DB
Set qdfPassThrough = dbsCurrent.CreateQueryDef("AllTitles")
qdfPassThrough.SQL = "SELECT * FROM IPAddressList"
qdfPassThrough.ReturnsRecords = True
'Create temp QueryDef object to get data from pass-through query
Set qdfLocal = dbsCurrent.CreateQueryDef("")
qdfLocal.SQL = "SELECT TOP 5 title FROM AllTitles"
Set rs = qdfLocal.OpenRecordset()
With rs
Do While Not .EOF
If Len(rs("IPAddress")) > 0 Then
rs("HostName") = GetHostNameFromIP(rs("IPAddress"))
End If
.MoveNext
Loop
.Update
DoEvents
.Close
End With
'Delete pass-through query
dbsCurrent.QueryDefs.Delete "AllTitles"
dbsCurrent.Close

 

[Douglas J. Steele]

I'm not sure you can do much to speed it up: I suspect it's the
GetHostNameFromIP call that's taking the lion's share of the time, and
there's not a heck of a lot that you can do to speed that up as far as I
know.

One comment, though. Are you hoping to update the HostName field in your
table with that code? If so, it needs some corrections.

With rs
Do While Not .EOF
If Len(rs("IPAddress")) > 0 Then
.Edit
rs("HostName") = GetHostNameFromIP(rs("IPAddress"))
.Update
End If
.MoveNext
Loop
DoEvents
.Close
End With

BTW,

rs("HostName") = GetHostNameFromIP(rs("IPAddress"))

could also be written as

!HostName = GetHostNameFromIP(!IpAddress)

(and that might make a very slight difference in speed)

 

[Mike Scirocco]

I'm not sure you can do much to speed it up: I suspect it's the
GetHostNameFromIP call that's taking the lion's share of the time, and
there's not a heck of a lot that you can do to speed that up as far as I
know.

One comment, though. Are you hoping to update the HostName field in your
table with that code? If so, it needs some corrections.

With rs
Do While Not .EOF
If Len(rs("IPAddress")) > 0 Then
.Edit
rs("HostName") = GetHostNameFromIP(rs("IPAddress"))
.Update
End If
.MoveNext
Loop
DoEvents
.Close
End With

BTW,
rs("HostName") = GetHostNameFromIP(rs("IPAddress"))
could also be written as
!HostName = GetHostNameFromIP(!IpAddress)
(and that might make a very slight difference in speed)

Hi Douglas,

Thank you very much for the tips, I'll use ! whenever possible, and
optimize the GetHostNameFromIP code.

Mike

 

[David F Cox]

At first glance you are retrieving all of the records then throwing away all
except the top 5
and retrieving all of the reords and then only dealing with those with
Len(rs("IPAddress")) > 0
I do not have time now to check my first impression, my hope is I have it
wrong.

The general point of a pass through query is to do as much selection and
grouping of records as possible so that only the information that you
actually require is sent.

 

[Douglas J. Steele]

Actually, while the name of the query is "qdfPassThrough", it's not a
pass-through query. In order to be a pass-through query, the Connect
property would have to be set.

Mike: is that the issue? Are you querying against a linked table, rather
than using a pass-through?

 

[Mike Scirocco]

Actually, while the name of the query is "qdfPassThrough", it's not a
pass-through query. In order to be a pass-through query, the Connect
property would have to be set.

Mike: is that the issue? Are you querying against a linked table, rather
than using a pass-through?

Sorry, I uploaded the wrong version of the code. I downloaded and tried
several examples and mistakenly posted the wrong one, apologies for your
wasted time. This is what I'm using now:

Private Sub Command1_Click()
Dim WorkBase As Database
Dim WorkRS1 As Recordset, rs As Recordset
Dim SQL As String, ctr As Long, s1 As String
Set WorkBase = CurrentDb
SQL = "Select * from IPAddressList"
Set WorkRS1 = WorkBase.OpenRecordset(SQL)
StopLooking = False
With WorkRS1
.MoveFirst
ctr = 0
Do While Not .EOF
ctr = ctr + 1
If IsNull(!HostName) Then GoTo doit
If IsEmpty(!HostName) Then GoTo doit
If Trim$(!HostName) = "" Then GoTo doit
GoTo skip
doit:
.Edit
s1 = GetHostNameFromIP(!IPAddress)
If Trim$(s1) <> "" Then
!HostName = s1
Else
!HostName = "(couldn't resolve)"
End If
.Update
lblRecordNumber.Caption = ctr
DoEvents
If StopLooking Then Exit Sub
skip:
Debug.Print ctr
.MoveNext
Loop
DoEvents
End With
SocketsCleanup
End Sub

 

[David W. Fenton]

One comment, though. Are you hoping to update the HostName field
in your table with that code? If so, it needs some corrections.

With rs
Do While Not .EOF
If Len(rs("IPAddress")) > 0 Then
.Edit
rs("HostName") =
GetHostNameFromIP(rs("IPAddress"))
.Update
End If
.MoveNext
Loop
DoEvents
.Close
End With

BTW,

rs("HostName") = GetHostNameFromIP(rs("IPAddress"))

could also be written as

!HostName = GetHostNameFromIP(!IpAddress)

(and that might make a very slight difference in speed)

Why do you need to walk a recordset to do that? Why not just use an
UPDATE query?

 

[Douglas J. Steele]

Why do you need to walk a recordset to do that? Why not just use an
UPDATE query?

Very true, David. That's what happens when you only read the problem, and
not the context! <g>