Access vs. SQL Server for online database

Thanks, everybody, for your help! I suspect we'll be converting to SQL Server soon.

Below is a link to Tech Note regarding Access which should be helpful if you go that route. Regarding using Access for a high volume web based application, please see the first paragraph of this Tech Note.

http://www.adobe.com/go/tn_17034

If you can get away with using 3 digit zip code areas for some of your partners, that might cut down on the total number of records in your database. Also, in addition to indexing, indexing, indexing, be aware that the "distinct" keyword is a pretty big resource hog as well. Might be best to stay away from that one.

Not sure if I remember if Access supports stored procedures or not, but any queries that you can precompile on the database side will speed things up a bit.

Of course, you could just explain to him that downloading a 20,000 record access file, waiting for it to finish, editing it, uploading it back to the server, and waiting for it to finish is not exactly a very effective work flow. You could switch everything over to MS SQL and then just give him a bound data grid into the data in CF if he really wants to manipulate the records like that.

But then again, I know how these things go. My condolences.

The database was actually running fine before adding the new records. It's a zip code radius search with a twist... The companies that participate in this portal web site purchase a certain mile radius to be displayed within, which is also tied in to the search results, so that adds fun to the mix as well. The queries are complicated, and there is no way around that - the criteria for the search results is complicated. The only LIKE conditions are when performing a search search by city/state combo, not zip code. They run pretty quickly in Access as a stand-alone, it's just on the server that it's running slower.

An occasional "compact and repair" of the Access database can make a big difference as well.

You are probably pushing the limit on database size. Three things come to mind. Are all columns used in the WHERE clauses indexed? This could make a big performance difference. Does the where clause have IN or LIKE conditions? They can slow down the processing speed. Finally, can some simple table maintenance SQL be written to eleminate the need to download the database for desktop manipulation? If so, this could ease the pain of the move to SQL Server.

Of course it would hypothetically run faster on a dedicated server.

SQL Server has the potential to be faster. A well designed Access database can be every bit as fast as a poorly designed SQL Server database, though.

If the customer insists on sticking with Access, I would say that the first course of action would be to ensure that the Access database is as optimized as possible. Note that if the results still warrant moving to SQL Server, a well designed Access database should be trivially easy to move up to SQL Server. A poorly designed Access database can be a nightmare.

Bottom line, I do not think you are wasting your time in optimizing your Access database, making use of cached queries, etc. etc.