Solved

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

Posted on 2011-09-13
5
366 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 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

688 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