SQL bottleneck, how to fix
- by masfenix
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
