?
Solved

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

Posted on 2011-09-13
5
Medium Priority
?
368 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

649 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