Speedier alternative for back end.

[ser01]

Hi all,

I have an application that uses access for the front and back end. The application is being used over a small network of only 5 computers. Certain functions of the application, involves running queries on a table with around 200k records on it. This queries need to be ran a lot during the day, and it is painfully slow. The data in the large table, is being imported daily from a text file. So to speed things up, I resorted to storing a bunch of tables that hold pre calculated data. This tables are being generated during import. I know that this is not the right way to do things, and that no calculations should ever be stored. But without it, my users are getting very frustrated waiting around. So my question is, would switching to a different solution such as SQL Server Express edition make things fast enough so that I could go back to having all the calculations done when the data is being viewed/manipulated?
Also the machine that we're using as the server, is running Windows XP profesional. Would that be a problem? What changes could I do to the way the network is set up, as well as the server's hardware in order to speed things up?

Any suggestions would be much appreciated!


Thank you in advance!

[RuralGuy]

Perhaps this link will be helpful: http://www.granite.ab.ca/access/performancefaq.htm

[ssanfu]

My $0.02....

In addition to Allan's comment...

Since Access is a file share app and not a client/server app, when you execute a query on a table with 200k records (from what I understand), Access has to send all 200k records over the LAN to do the filtering/sorting. You cannot unload the record processing onto the BE like you can in a client/server setup.

If your LAN is running at 10 mbps, that is a very clever way to build in a long coffee break. If the LAN is 100 mbps (the normal nowadays) it is still slow, but not so bad. If you spent some money and converted to 1,000 mbps (1 gbps - fiber), you would see the least wait times.

A friend of mine had a copy/printing business running a LAN at 100 mbps. He had to print out CDs of mixed documents and pictures. It took forever to print one CD - over 1/2 hour to transfer the data and print. He went to 1 gbps (fiber) and the time went down to about 7 minutes - the high speed printer was then the bottleneck.

So...
1) Go to fast ethernet (1 gbps fiber) or
2) Convert your prog to client/server (SQL Express?) and off load the queries to the BE using pass-thru queries.

Unfortunately, both options require an outlay of money.

[ser01]

Some great suggestions here, thanks a lot guys. I'll spend some time looking into it.