I have a large access database (2003) which contains approx 3,500,000 records with 11 fields in one table. It contains one minute data for the currency pair EURUSD. It will obvioulsy continue to get bigger.
When I query the data for a certain period of time say 3 months last year & ordered it can take quite a while to retreieve the data which is uploaded into a chart in a ASP.NET web application with C# as the script langauge.
I have been told that indexing & partition could improve the performance. I know how to index but partitioning I do not know. Is there a limit to how many times you partition a table? Can you partition on dates? Also would I need to change my query if I partition my table?