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
