469,645 Members | 1,941 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

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

Hi,

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.

Finally:
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
it?

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

Your help is highly appreciated!!!

Roi

Nov 17 '05 #1
0 2190

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 gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.