?
Solved

VBS / MC Access / ADO & Importing Records (Slow - needs performance improvement)

Posted on 2011-09-13
5
Medium Priority
?
367 Views
Last Modified: 2012-05-12
I have been using this code snippit for a while in various forms and has always worked just fine. I've run across a need to import ~250,000 records each day into the same DB and have adapted by VBS to accommodate. The import works just fine with one minor exception - the time to reload the data is ~10-12 minutes (multi-processor PC with far too much RAM). I'd love a suggestion on how to improve the import time as I know the next project will be importing 1-2 million records. I do have another VBS which compacts the DB after the reload process is run. :)

Data file is in a simple comma delimited format:

TSM_SERVER, START_TIME, END_TIME, ACTIVITY, NUMBER, ENTITY, COMMMETH, ADDRESS, SCHEDULE_NAME, EXAMINED, AFFECTED, FAILED, BYTES, IDLE, MEDIAW, PROCESSES, SUCCESSFUL, VOLUME_NAME, DRIVE_NAME, LIBRARY_NAME, LAST_USE, COMM_WAIT, NUM_OFFSITE_VOLS
TSMDEV01,8/10/2011 00:00,8/10/2011 00:00,BACKUP,3926496,LS_DVMSSQL03,Tcp/Ip,10.130.133.2:3387,,0,3,0,1353678,11,0,1,YES,,,,,0,
TSMDEV01,8/10/2011 00:00,8/10/2011 00:09,BACKUP,3926495,LS_DVMSSQL03,Tcp/Ip,10.130.133.2:3386,,0,3,0,5540196489,1,0,1,YES,,,,,391,
TSMDEV01,8/10/2011 00:04,8/10/2011 00:04,BACKUP,3926558,LS_DCHISWSHSQ01,Tcp/Ip,10.130.133.105:56610,,0,3,0,8663995,1,0,1,YES,,,,,0,
TSMDEV01,8/10/2011 00:05,8/10/2011 00:05,BACKUP,3926571,LS_DCHISWSHSQ01,Tcp/Ip,10.130.133.105:56617,,0,3,0,1438821,0,0,1,YES,,,,,0,
TSMDEV01,8/10/2011 00:06,8/10/2011 00:06,BACKUP,3926592,LS_DCHISWSHSQ01,Tcp/Ip,10.130.133.105:56619,,0,3,0,4938592,0,0,1,YES,,,,,1,

Open in new window

Here is the VBS:

Option Explicit

Dim adoCSVConnection, adoCSVRecordSet, strPathToTextfile
Dim strCSVFile, adoJetConnection, adoJetCommand, strDBPath
Dim objScript, strPathToMDB, strTempDB

Const adCmdText24 = &H0001


' Specify path to CSV file.
strPathToTextFile = "D:\testing "

' Specify CSV file name.
strCSVFile = "Summary.csv"

' Specify Access database file.
strDBPath = "D:\testing\tsm.mdb"

' Open connection to the CSV file.
Set adoCSVConnection = CreateObject("ADODB.Connection")
Set adoCSVRecordSet = CreateObject("ADODB.Recordset")


' Open CSV file with header line.
adoCSVConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strPathtoTextFile & ";" & _
    "Extended Properties=""text;HDR=YES;FMT=Delimited"""

adoCSVRecordset.Open "SELECT TSM_SERVER, START_TIME, END_TIME, ACTIVITY, [NUMBER], ENTITY, COMMMETH, ADDRESS, SCHEDULE_NAME, EXAMINED, AFFECTED, FAILED, BYTES, IDLE, MEDIAW, PROCESSES, SUCCESSFUL, VOLUME_NAME, DRIVE_NAME, LIBRARY_NAME, LAST_USE, COMM_WAIT FROM " & strCSVFile, adoCSVConnection

' Open connection to MS Access database.
Set adoJetConnection = CreateObject("ADODB.Connection")
adoJetConnection.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);" _
    & "FIL=MS Access;DriverId=25;DBQ=" & strDBPath & ";"
adoJetConnection.Open

' ADO command object to insert rows into Access database.
'Set adoJetCommand = New ADODB.Command
Set adoJetCommand = CreateObject("ADODB.Command")
Set adoJetCommand.ActiveConnection = adoJetConnection
adoJetCommand.CommandType = adCmdText24

adoJetCommand.CommandText = "delete * from Summary"
adoJetCommand.Execute

' Read the CSV file.
Do while adoCSVRecordset.EOF=False
    ' Insert a row into the Access database.


    adoJetCommand.CommandText = "INSERT INTO Summary " _
        & "( TSM_SERVER, START_TIME, END_TIME, ACTIVITY, [NUMBER], ENTITY, COMMMETH, ADDRESS, SCHEDULE_NAME, EXAMINED, AFFECTED, FAILED, BYTES, IDLE, MEDIAW, PROCESSES, SUCCESSFUL, VOLUME_NAME, DRIVE_NAME, LIBRARY_NAME, LAST_USE, COMM_WAIT) " _
        & "VALUES (" _
            & "'" & adoCSVRecordset(0) & "', " _
            & "'" & adoCSVRecordset(1) & "', " _
            & "'" & adoCSVRecordset(2) & "', " _
            & "'" & adoCSVRecordset(3) & "', " _
            & "'" & adoCSVRecordset(4) & "', " _
            & "'" & adoCSVRecordset(5) & "', " _
            & "'" & adoCSVRecordset(6) & "', " _
            & "'" & adoCSVRecordset(7) & "', " _
            & "'" & adoCSVRecordset(8) & "', " _
            & "'" & adoCSVRecordset(9) & "', " _
            & "'" & adoCSVRecordset(10) & "', " _
            & "'" & adoCSVRecordset(11) & "', " _
            & "'" & adoCSVRecordset(12) & "', " _
            & "'" & adoCSVRecordset(13) & "', " _
            & "'" & adoCSVRecordset(14) & "', " _
            & "'" & adoCSVRecordset(15) & "', " _
            & "'" & adoCSVRecordset(16) & "', " _
            & "'" & adoCSVRecordset(17) & "', " _
            & "'" & adoCSVRecordset(18) & "', " _
            & "'" & adoCSVRecordset(19) & "', " _
            & "'" & adoCSVRecordset(20) & "', " _
            & "'" & adoCSVRecordset(21) & "');"


    adoJetCommand.Execute
    adoCSVRecordset.MoveNext
Loop

' Clean up.
adoCSVRecordset.Close
adoCSVConnection.Close

msgbox("DONE!" & ": " & now())

adoJetConnection.Close

Open in new window

0
Comment
Question by:haverkampf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 36535567
If you can manually import the CSV directly into the access database cleanly, then you should be able to automate the direct import of the CSV data via Access automation (DoCmd.TransferText method).  Doing a manual test will also give you the ability to measure the time it takes to import the data.

How big is your Summary table?
How big is your Access database?
How often do you repair and compact the database?
What indexes are defined on the Summary table?

========
Improving your current code would require the use of transactions.  You would 'batch' the updates into ~500 record blocks between Commit points.
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 36535595
One alternative is to attach the CSV file to the access database and execute a single Insert SQL to append the CSV attached-table rows to the Summary table.

While this approach should be faster, you would have to test this manually to ensure that your CSV data mapped correctly to the Summary table.
0
 

Author Comment

by:haverkampf
ID: 36535932
After a bit of noodling around - in Access, I added a link table to the data and then ran this slightly modified VBS to import the data. Import time was reduced from 12mins for 280,000 records to under 12 seconds.

Option Explicit

Dim adoCSVConnection, adoCSVRecordSet, strPathToTextfile
Dim strCSVFile, adoJetConnection, adoJetCommand, strDBPath
Dim objScript, strPathToMDB, strTempDB

Const adCmdText2455 = &H0001


' Specify Access database file.
strDBPath = "D:\testing\tsm.mdb"

' Open connection to MS Access database.
Set adoJetConnection = CreateObject("ADODB.Connection")
adoJetConnection.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);" _
    & "FIL=MS Access;DriverId=25;DBQ=" & strDBPath & ";"
adoJetConnection.Open

' ADO command object to insert rows into Access database.

Set adoJetCommand = CreateObject("ADODB.Command")
Set adoJetCommand.ActiveConnection = adoJetConnection
adoJetCommand.CommandType = adCmdText2455

adoJetCommand.CommandText = "delete * from Summary"
adoJetCommand.Execute

    ' Insert a row into the Access database.


    adoJetCommand.CommandText = "INSERT INTO Summary " _
        & "( TSM_SERVER, START_TIME, END_TIME, ACTIVITY, [NUMBER], ENTITY, COMMMETH, ADDRESS, SCHEDULE_NAME, EXAMINED, " _
        & "AFFECTED, FAILED, BYTES, IDLE, MEDIAW, PROCESSES, SUCCESSFUL, VOLUME_NAME, DRIVE_NAME, LIBRARY_NAME, LAST_USE, COMM_WAIT) " _
        & " SELECT TSM_SERVER, START_TIME, END_TIME, ACTIVITY, [NUMBER], ENTITY, COMMMETH, ADDRESS, SCHEDULE_NAME, EXAMINED, AFFECTED, " _
        & " FAILED, BYTES, IDLE, MEDIAW, PROCESSES, SUCCESSFUL, VOLUME_NAME, DRIVE_NAME, LIBRARY_NAME, LAST_USE, COMM_WAIT FROM SummaryLnk ; "

    adoJetCommand.Execute


' Clean up.

adoJetConnection.Close

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 36536059
Thanks for the points.

You might want to time the "Delete *" operation to see what part of that 12 seconds is consumed by this part of the process.  If non-trivial, you could play with deleting the Summary table and change the Insert into a make table query.

If you do that, please post your timing results in this thread.

12 seconds is what I would expect for a quarter million row import.

good luck.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question