painfully sloooooooooooooowwwwww

[tractorvix]

Hi all,

I'm having to use linked excel tables as the inputs for my database and am then trying to normalise the data so that I can perform the necessary calculations etc. It wasn't too bad when it was all stored on my local machine, but now it's on the server, it's soooooo slow (around 30 minutes). Does anyone have any ideas / suggestions for ways in which this could be improved?

Thanks
Vicky

Private Sub UpdateMarginManagement()
Dim rs As DAO.Recordset
Dim x As Integer
Dim sql As String
InputTable = "MM Scenario" & S
StartPoint = 4
OutputTable = "tblNMM"
FieldName = "MM"
y = CurrentDb.TableDefs(InputTable).Fields.Count
Set rs = CurrentDb.OpenRecordset(InputTable)

If rs.EOF And rs.BOF Then
Exit Sub
End If
rs.Move 1
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE " & OutputTable & ".*" & _
"FROM " & OutputTable & ";"

Do Until rs.EOF
For x = StartPoint + 1 To y - 1
sql = "INSERT INTO " & OutputTable & "(ProdXrefID, Scenario, dateval, " & FieldName & ")" & _
" VALUES(" & rs(StartPoint) & "," & S & ",'" & rs(x).Name & _
"'," & rs(x) & ")"

DoCmd.RunSQL sql
Next x
rs.MoveNext
Loop


rs.Close
Set rs = Nothing
DoCmd.SetWarnings True



End Sub

 

[SkipVought]

Import the data rather than link?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Golom]

It's probably happening because Access is a file server system which means that every interaction with the database will move all the data required over the network. In this case, the entire "OutputTable" is sent from server to client and then from client to server every time you issue the insert statement.

You may want to look at the dbOptimisticBatch LockEdit option for opening a recordset and the dbUpdateBatch Type for the Update statement. Do your updates on a recordset.

When you are doing Batch Updates the changes are buffered locally and you can send the updates only when all changes are complete.

 

[SeeThru]

Also look to see if there are unneccesary indexes - this can really slow a database.

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[mp9]

May find some other general tips for performance at

http://www22.brinkster.com/accessory/wip.shtml