SQL bottleneck, how to fix

Question

This is related to my previous thread: http://stackoverflow.com/questions/3069806/sql-query-takes-about-10-20-minutes

However, I kinda figured out the problem. The problem (as described in the previous thread) is not the insert (while its still slow), the problem is looping through the data itself Consider the following code:

 Dim rs As DAO.Recordset
Dim sngStart As Single, sngEnd As Single
Dim sngElapsed As Single



Set rs = CurrentDb().QueryDefs("select-all").OpenRecordset
MsgBox "All records retreived"

sngStart = Timer
Do While Not rs.EOF
    rs.MoveNext
Loop
sngEnd = Timer
sngElapsed = Format(sngEnd - sngStart, "Fixed") ' Elapsed time.

MsgBox ("The query took " & sngElapsed _
    & " seconds to run.")

As you can see, this loop does NOTHING. You'd expect it to finish in seconds, however it takes about 857 seconds to run (or 15 minutes). I dont know why it is so slow. Maybe the lotusnotes sql driver?

any other ideas? (java based solution, any other solution)

What my goal is: To get all the data from remote server and insert into local access table

Answer 1

My recommendation is that you create a Pass-Through query that will get the data from the remote server. Then create a Make Table query that uses the aforementioned query as its source. Your function then would be simplified to a call to this second query.

Answer 2

The loop isn't doing "nothing" it's calling MoveNext, which is potentially doing A LOT.

Answer 3

This document has some information about performance tuning in NotesSQL. If you aren't already, select your data from Notes Views instead of Notes Forms. NotesSQL will then leverage the indexes within the views for faster queries. You may need to create the view in the Notes database, but the performance benefit will make it worthwhile.