Solved

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

Posted on 2011-09-13
5
352 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
  • 3
5 Comments
 
LVL 45

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 45

Accepted Solution

by:
aikimark earned 500 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 45

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now