472,135 Members | 1,392 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,135 software developers and data experts.

Querying MS Access database using .NET's OLEDB is very slow


I'm having a bit of a problem with a small application I wrote in C#,
which uses an Access database (mdb file) for storing financial data.

After looking for a similiar topic and failing to find one, I'm posting
the question in hope some one of you guys out there will have the
answer for me...!

I'll start with what I have, then I'll continue to the problem itself.

What I have:
I have an Access database file, with 3 tables: a primary one, which
holds the data, and 2 "lookup" tables for sub-categories in the main
table. They are linked programmatically and not via some relashionship
definition in the Access file itself.
This file is used by a C# application I wrote as a front-end which
connects to the mdb file and displays data from it in a DataGrid
components (among other things). The link to the database is done via
the OleDbConnection, OleDbAdapter, etc. objects.

The problem:
Querying the main table (called 'MoneyData') is extremely slow (a
simple 'SELECT * FROM MoneyData' takes 12.3 seconds with 205 records)
the first time, and slow the rest of the times (approx. 5 sec doing
some other kind of SELECT on the table).
I am requerying the database to get results according to dates (i.e
'SELECT * FROM MoneyData WHERE Month(aDate)=3 AND ...' ).

Tests I've done:
I tried running the code on another machine, where I got some better
results, but still unsatisfaying. The first query took 4.5 seconds and
the rest took approx 1.1 sec (which is acceptable, but still slow for
the amount of data in the table).
I tried adding an Index on the Date field and compacting the database
file, but it remained the same.
I also ran the simple SELECT query and some more complex ones inside
the Access interface, to see if the problem is with the mdb file or the
query itself, but there it worked very fast. no delay at all between
the query and the results.

Since performing UPDATE/INSERT/DELETE commands seems to be working
fine, I assume there's something wrong only with the SELECT command.
The method responsible for it is the OleDbAdapter.Fill() method, as far
as I understand. Could it be that the problem is there? Is there a good
way to test it? and if so, and the problem is there, how can I replace

Technical info:
I'm running XP Pro, SP2, with .NET 1.1 SP1
MS Access version: 2002 Pro

Your help is highly appreciated!!!


Nov 17 '05 #1
0 2396

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

63 posts views Thread by Jerome | last post: by
7 posts views Thread by Dan V. | last post: by
6 posts views Thread by Greg | last post: by
3 posts views Thread by Mike Wilson | last post: by
1 post views Thread by Abareblue | last post: by
11 posts views Thread by CM Manager via DotNetMonster.com | last post: by
9 posts views Thread by Wayne Smith | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.