Dear members,
I am new here and also rather new in the world of Access. Recenty in my work I have taken over some tasks that require Access and I have a few questions regarding performance. Hope somebody can help me out here.
The issue is being able to filter quickly through a database table of around 1,2 million loanparts (parts of a mortgages) with about 30 fields. The filtering is most often a borrower ID and sometimes the loanpart ID. On average a borrower has around 3 loanparts. The issue is that I find that performance is rather terrible: filtering for a single borrower ID takes around 5 minutes before Access presents the loan parts belonging to the borrower and its fields.
We use an Access DB that is hosted on a share drive on a network and quite new laptops (win7, 32 bit, 4GB). The tables are generally linked tables (linked to csv files).
Now I wonder:
+ Is this performance normal?
+ What is the bottleneck: share drive, network, laptops, linked data versus imported data?
+ Is there an impact if the same DB holds other tables, objects etc.?
+ Can there be something like e.g. a borrower ID is seen as a text field (in stead of a number) that is impacting performance?
Hope you can help me out. Thanks very much.