Improve ACCESS databse performance RRS feed

  • Question

  • Hi,

     In my application i have to use ACCESS 2007 & this is my first application with database. I am using VS2005 C#(.Net 2.0).

     I have doubt in few things if somebody help me with this then it will be great.
      - My application will run continuisly one PC and it will store data in ACCESS file. ACCESS 2007 has a limitation of 4GB so when the file size goes above 3GB i'll create a new file. I have to store data continuosly in the database.
    So my question is, shall i use continuous connection with database and update the ACCESS database directly whenever required OR use the dataset to read the ACCESS data modify it and store it back in ACCESS file. Which is faster in terms of application performance.

     Is there any good document which explains me how to improve ACCESS performance.

    Thanks in advance,

    Saturday, December 5, 2009 9:37 AM


All replies

  • Hi IamHuM,

    Considering the data table can be really huge, I suspect loading the whole data table into DataSet directly will cause some memory issues like memory overflow.  Can you just load the data rows that you want and then update the data table?   For new data tables, I recommend you set a limitation of the data rows each time we need to handle. 

    Besides, could you please provide us more background information of such a scenario?

    Best Regards,
    Lingzhi Sun

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, December 8, 2009 2:00 PM
  • Keep  your updateQuery in Access DB itself( create UpdateQuery/InsertQuery). Call it via OleDbCommand Object ExecuteNonQuery. Pass only the elements which are modified. And always make sure that your  access db in not fragmented. Use database compact utility.
    Thanks Mike --------Please mark as answer if it is useful----------
    Thursday, December 10, 2009 6:13 PM