Time Issue

[sertev]

Hi There.

I am having an issue of time when connecting to an access database with vb.net.

I open the DB, select the RS and then populate a list view with the records from the RS. This whole proccess takes about 10 seconds. When I used to do this from VB 6 it took less the 1 second to do.

Is this normal? Do I have to live with it? Is my code wrong?

Following is my code:

Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
db = New ADODB.Connection()
db.CursorLocation = ADODB.CursorLocationEnum.adUseClient
db.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= '" & currentDir & "';Persist Security Info=False;")
rs = New ADODB.Recordset()
rs = db.Execute("Select id, travelgroup, name,phone1,fax,email from agents")
Dim itmX As ListViewItem
While Not rs.EOF
If Not IsDBNull(rs.Fields(1).Value) Then
itmX = lstAgents.Items.Add(CStr(rs.Fields(1).Value))
itmX.Tag = CStr(rs.Fields(0).Value)
Else
GoTo nextItem
End If
If Not IsDBNull(rs.Fields(2).Value) Then
itmX.SubItems.Add(rs.Fields(2).Value)
End If
If Not IsDBNull(rs.Fields(3).Value) Then
itmX.SubItems.Add(rs.Fields(3).Value)
End If
If Not IsDBNull(rs.Fields(4).Value) Then
itmX.SubItems.Add(rs.Fields(4).Value)
End If
If Not IsDBNull(rs.Fields(5).Value) Then
itmX.SubItems.Add(rs.Fields(5).Value)
End If
nextitem:
rs.MoveNext()
End While
db.close()
rs = Nothing

Any ideas?

Thanx in advance

[Beacon]

Well all that checking for Nulls is time waisting!

Why not:

1) Not allow Nulls in your database! OR
2) Use SELECT ... CASE instead of if...then!


Also that rs.eof loop is a slow way of doing it!

b

[sertev]

How can you do it without using an rs.eof?

[Gaffer]

Well tehre's two things about that statement that slows things down:

rs.EOF has to be checked every pass through the loop. So if only there was a way of determining how many records there are in your recordset, and using a FOR...NEXT loop...





Basically put you Recordcount into a variable. Then:

For i = 0 to iRec  'wher iRec is you recordcount
 
Next i

Also, avoid using WHILE if you can - again the program has to keep checking if that WHILE criteria is true for each loop.

It'll save you time, but I suspect you IsDBNull function, along with Beacon's suggestions are more at fault...




...oh and I don't know how essntial that GOTO statement is, but I'd try and lose it if you can...