locked
Adding rows in ACCESS database @ very fast speed... RRS feed

  • Question

  • hi,

             In my application... i want to connect to a MS ACCESS 2000 database... Insert some data into the tables... Entries are added at regular intervals (say approximately 1msec for each new entry)...
             Is there is any good document/sample code which explains me this...

             Also for every entry do i have to open the database connection and close it again(since entries are getting added in the database at very high speed)...?

    Thanks in advance,
    IamHuM

    Wednesday, August 5, 2009 12:15 PM

Answers

  • SELECT INTO is quicker. It is designed to select from one table and putting the results in another.

    You mentioned you have your data in a buffer. Can you explain a little more. Give me an example of this buffer of data. Provide a small sample of what you have now please.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Marked as answer by Yichun_Feng Wednesday, August 12, 2009 1:36 AM
    Thursday, August 6, 2009 2:51 PM

All replies

  • Do not open and close it if you are doing bulk inserts. Open -> perform bulk insert -> then close.

    Where is this bulk coming from?

    Couple of options for you:

    1.) Use SqlBulkCopy
    2.) You can use the SqlCommand.Prepare if you are iterating through some DataReader perfroming inserts
    3.) SSIS is extremely fast, but just a tad complex if you have never done it
    4.) SELECT INTO is also quite fast
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Wednesday, August 5, 2009 4:14 PM
  • hi John,

       Actually data is continuosly coming on LAN... i am putting it in buffer and then i am trying to insert the same data into ACCESS database.
    And i am trying to insert one line at a time into the database. So i am not trying to insert in BULK.
       Another thing is i am not using SQL, my database is in Microsoft ACCESS 2000.

       SELECT INTO or INSERT INTO which one is faster...?

       So for inserting into database which option is better,...
             Open -> Insert one entry -> Close (Repeat for each entry)

    Or

             Open when application start -> Insert each entry one by one -> Close when application exists.



    Thanks for the reply,
    IamHuM
            

    Thursday, August 6, 2009 6:14 AM
  • SELECT INTO is quicker. It is designed to select from one table and putting the results in another.

    You mentioned you have your data in a buffer. Can you explain a little more. Give me an example of this buffer of data. Provide a small sample of what you have now please.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Marked as answer by Yichun_Feng Wednesday, August 12, 2009 1:36 AM
    Thursday, August 6, 2009 2:51 PM