Why my code take time 10 Seconds

[Guest]

I need to insert data to table intermod and write code in cal button click
event. Unfortunately It takes time more than 20 seconds. I try many times
and belives that "Insert into...." in M2FSql1 take almost of time.
How to write code to reduce time.


Dim M2FSql1 as String

N=35
For M = 0 To N - 1
For X = M + 2 To N
For Q = 1 To 3

F1=M*N+5
F2=M+1-N*2
OrderN = 2 * Q + 1
cal1 = (Q + 1) * F1 - Q * F2
cal2 = (Q + 1) * F2 - Q * F1


M2FSql1 = "INSERT INTO Intermod (OrderN,IntmodFreq, Freq1, Freq2) "
M2FSql1 = M2FSql1 & "Values(" & OrderN & "," & cal1 & "," & F1 & "," & F2 &
" );"
DoCmd.RunSQL M2FSql1
Next Q
Next X
Next M

 

[Marshall Barton]

I need to insert data to table intermod and write code in cal button click
event. Unfortunately It takes time more than 20 seconds. I try many times
and belives that "Insert into...." in M2FSql1 take almost of time.
How to write code to reduce time.

Dim M2FSql1 as String

N=35
For M = 0 To N - 1
For X = M + 2 To N
For Q = 1 To 3

F1=M*N+5
F2=M+1-N*2
OrderN = 2 * Q + 1
cal1 = (Q + 1) * F1 - Q * F2
cal2 = (Q + 1) * F2 - Q * F1

M2FSql1 = "INSERT INTO Intermod (OrderN,IntmodFreq, Freq1, Freq2) "
M2FSql1 = M2FSql1 & "Values(" & OrderN & "," & cal1 & "," & F1 & "," & F2 &
" );"
DoCmd.RunSQL M2FSql1
Next Q
Next X
Next M

I think this approach might be faster:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = OpenRecordset(Intermod, dbOpenDynaset)
N=35
For M = 0 To N - 1
For X = M + 2 To N
For Q = 1 To 3
rs.AddNew
F1=M*N+5
F2=M+1-N*2
rs!OrderN = 2 * Q + 1
rs!IntmodFreq= (Q + 1) * F1 - Q * F2
' cal2 = (Q + 1) * F2 - Q * F1
rs!Freq1 = F1
rs!Freq2 = F2
rs!Update
Next Q
Next X
Next M

rs.Close : Set rs = Nothing
Set db = Nothing

 

[John Spencer]

Since you are running somethng on the order of 1700-1800 queries in the
nested loops, I think that the performance is fairly good.

Perhaps Marshall's solution will work better for you.

Can I ask how often you need to do this? Might you be better off building
the table once and then if you need a limited set of records use a query to
restrict the set of records returned.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[George Nicholson]

In addition to the other suggestions provided, consider:
1) Under Options>Advanced, check "Open databases using record-level locking'
2) remove table indexes before adding that many records and replace them
afterwards. Otherwise, the index(s) will be updated with every single Insert
or AddNew.