Optimize inserts in Access

[vladk]

Hi,

I need to insert many records into Access table. I create records by building various combinations of the fields. The number of the combinations (the records to be inserted) is 362208. It takes long time to insert all of them one by one.

I am wondering if it's possible to insert entire "recordset" for all combinatoins in a single Insert.

Thank you

vladk


This is the the code I use for now (very slow):

...

Dim objStatesDic As New Dictionary
Dim objProgDic As New Dictionary
Dim objCoverageDic As New Dictionary
Dim objExposureDic As New Dictionary
Dim objQuarterDateDic As New Dictionary

....
'Here I load my dictionaries ...
....

'Here I run nested loops for all combinations:

For Each varStateKey In objStatesDic
For Each varProgKey In objProgDic
For Each varCoverageKey In objCoverageDic
For Each varExposureKey In objExposureDic
For Each varQuarterDateKey In objQuarterDateDic

ExecuteSQLDDL "INSERT INTO " & pstrTable & " VALUES (" & "'" & objStatesDic.Item(varStateKey) & "'" & ", " & _
"'" & objProgDic.Item(varProgKey) & "'" & ", " & _
"'" & objCoverageDic.Item(varCoverageKey) & "'" & ", " & _
"'" & objExposureDic.Item(varExposureKey) & "'" & ", " & _
"#" & objQuarterDateDic.Item(varQuarterDateKey) & "#" & ", " & _
0 & ");"

Next varQuarterDateKey
Next varExposureKey
Next varCoverageKey
Next varProgKey
Next varStateKey

....

Public Sub ExecuteSQLDDL(ByVal pstrSQLString As String)
Const METHOD_NAME As String = "ExecuteSQLDDL"

On Error GoTo MethodExit

Dim objDB As DAO.Database
Dim objQD As DAO.QueryDef

Set objDB = DBEngine.Workspaces(0).Databases(0)
Set objQD = objDB.CreateQueryDef("")

objQD.SQL = pstrSQLString

objQD.Execute

objDB.Close

MethodExit:

If Err.Number <> 0 Then
MsgBox "Error " & CStr(Err.Number) & " in " & METHOD_NAME & vbCr & Err.Description
End If

End Sub

 

[gmmastros]

My Access is rusty, but my T-SQL isn't. So, what follows would certainly work for Microsoft SQL Server. I suggest that you try this in Access to see if you can get it to work. If this works for you, it will be many times faster than your current method.

You apparently have 5 pieces of data and want all combinations of data. If this data was already in a table, you could insert/select using a cross join query.

The basic syntax would be...

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] TableName(Col1, Col2, Col3, Col4, Col5)
[COLOR=blue]Select[/color] Col1, Col2, Col3, Col4, Col5
[COLOR=blue]From[/color]   Table1.Col1
       [COLOR=blue]Cross[/color] [COLOR=blue]Join[/color] Table2.Col2
       [COLOR=blue]Cross[/color] [COLOR=blue]Join[/color] Table2.Col3
       [COLOR=blue]Cross[/color] [COLOR=blue]Join[/color] Table2.Col4
       [COLOR=blue]Cross[/color] [COLOR=blue]Join[/color] Table2.Col5

Since you don't have this data in a table, you could 'manufacture' the table by using Union ALL, like this...

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] TableName(Col1, Col2, Col3, Col4, Col5)
[COLOR=blue]Select[/color] Col1, Col2, Col3, Col4, Col5
[COLOR=blue]From[/color]   (
		[COLOR=blue]Select[/color] 1 [COLOR=blue]As[/color] Col1
		Union All [COLOR=blue]Select[/color] 2
		) [COLOR=blue]As[/color] A
       [COLOR=blue]Cross[/color] [COLOR=blue]Join[/color] (
		[COLOR=blue]Select[/color] 3 [COLOR=blue]As[/color] Col2
		Union All [COLOR=blue]Select[/color] 4
		) [COLOR=blue]As[/color] B
       [COLOR=blue]Cross[/color] [COLOR=blue]Join[/color] (
		[COLOR=blue]Select[/color] 5 [COLOR=blue]As[/color] Col3
		Union All [COLOR=blue]Select[/color] 6
		) [COLOR=blue]As[/color] C
       [COLOR=blue]Cross[/color] [COLOR=blue]Join[/color] (
		[COLOR=blue]Select[/color] 7 [COLOR=blue]As[/color] Col4
		Union All [COLOR=blue]Select[/color] 8
		) [COLOR=blue]As[/color] [COLOR=blue]D[/color]
       [COLOR=blue]Cross[/color] [COLOR=blue]Join[/color] (
		[COLOR=blue]Select[/color] 9 [COLOR=blue]As[/color] Col5
		Union All [COLOR=blue]Select[/color] 10
		) [COLOR=blue]As[/color] E

Again, I know this would work for SQL Server, but I suspect there is also a way to make this work for Access.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I guess my Access is VERY rusty because I've been playing around with this for a little bit. Here is my suggestion.

1. Take each list of items (from your dictionary objects) and create a table in the database.
2. Run a cross join query in Access to insert all the combinations. The query would look like...

Insert Into MainTable(Col1, Col2, Col3, Col4)
Select TableA.Col1, 
       TableB.Col2, 
       TableC.Col3, 
       TableD.Col4,
       TableE.Col5
From   TableA, TableB, TableC, TableD, TableE

Since the tables are not joined together, you will get every possible combination for the data. I suspect that each list is relatively small (10 to 15 items). Doing this for each item would account for 50 to 100 database hits. Then the last one that combines all the data in to the main table.

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vladk]

Hi, gmmastros,

Thank you for your reply. My pieces of data are not in the tables. They are in the dictionaries.

I found the way how to quickly store all combinations in one CSV file. Now, I need to write code to import that CSV file into ACCESS table. If you can help me with this part, I would greatly appretiate it!

Thank you!

vladk

 

[vladk]

I actually was able to export CSV into ACCESS:

DoCmd.TransferText acImportDelim, , pstrTable, MY_CSV_FILE, True

The only problem now is that for some reason, DoCmd replaces all strings that contain just spaces with empty strings, and I apparently don't want it.

vladk