Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    2

    Unanswered: Inserting 1 million records takes 1 hr +

    Hello,

    I am currently developing a windows based application which needs to process millions of records. The input to the application is a binary file. I preferred access database for performing logical operations like AND/OR in the data.

    Inserting 1 million records in Access db is taking more than 1 hr. It has a bad performance. Is there any method to improve the performance while inserting the records?

    I am inserting the records 1 by 1.

    Thanks in advance.

    regards,
    Vignesh

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Is the access file on your local hard drive, or on a file server? Writing records into an access datafile over the network IS very slow - I've seen it take 300 times longer over the network.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'd have to agree there. Accessing/writing large amounts of data in a network environment can take unusually large amounts of time with Access.

    Can the data be processed with an import spec?

    Can the data be copied locally and processed locally to dodge network lag?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Mar 2008
    Posts
    2

    Re: Inserting 1 million records takes 1 hr +

    The database (mdb file) is stored in the local drive and not in the network. but I am still unable to guess the rootcause of the problem.

    regards,
    Vignesh

  5. #5
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Re

    clean your objects
    and See if your virtual mem. is adequate
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rhythm_vignesh
    I am currently developing a windows based application which needs to process millions of records.
    Quite possible that Access (read JET) is not the appropriate database engine in that case. How many is "millions"? How many per week and will you be clearing out any data or will it be culminative? What sort of data - are these wide (i.e. lots of columns) records or are there any large text\ memo fields? No BLOBS I hope....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would try to link to the file using a File Specification and then either import the data or append the data. I have done both processes (1 record at a time inserting and linking and appending/importing) and by far the importing the data using TransferText is better than a recordset and an update.

    If you can't link to theh file directly, I would think adding a procedure to convert the file to an ASCII file, then linking would improve the speed.

    The other question I have is what is ideal performance? 30 minutes, 30 seconds. My experience has been, 1 million records is going to take closer to 30 minutes than 30 seconds in Access.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •