Search code examples
c#databasems-accessjet

Best way to process a large database?

Background:

I have one Access database (.mdb) file, with half a dozen tables in it. This file is ~300MB large, so not huge, but big enough that I want to be efficient. In it, there is one major table, a client table. The other tables store data like consultations made, a few extra many-to-one to one fields, that sort of thing.

Task:

I have to write a program to convert this Access database to a set of XML files, one per client. This is a database conversion application.

Options:

(As I see it)

  1. Load the entire Access database into memory in the form of List's of immutable objects, then use Linq to do lookups in these lists for associated data I need.

    • Benefits:
      • Easy parallelised. Startup a ThreadPool thread for each client. Because all the objects are immutable, they can be freely shared between the threads, which means all threads have access to all data at all times, and it is all loaded exactly once.
    • (Possible) Cons:
      • May use extra memory, loading orphaned items, items that aren't needed anymore, etc.

  2. Use Jet to run queries on the database to extract data as needed.

    • Benefits:
      • Potentially lighter weight. Only loads data that is needed, and as it is needed.
    • (Possible) Cons:
      • Potentially heavier! May load items more than once and hence use more memory.
      • Possibly hard to paralellise, unless Jet/OleDb supports concurrent queries (can someone confirm/deny this?)

  3. Some other idea?

What are StackOverflows thoughts on the best way to approach this problem?

Solution

  • From the sounds of this, it would be a one-time operation. I strongly discourage the actual process of loading the entire setup into memory, that just does not seem like an efficient method of doing this at all.

    Also, depending on your needs, you might be able to extract directly from Access -> XML if that is your true end game.

    Regardless, with a database that small, doing them one at a time, with a few specifically written queries in my opinion would be easier to manage, faster to write, and less error prone.