Sql server vs MS Access performance

Question

I have an existing application which uses Sql Server 2005 as a back-end. It contains huge records, I need to join tables which contain 50K-70K. Client m/c is a lower hardware.

So, can I improve its performance by using MS Access as a back-end? I also need to search operation on an Access file. So, which one is better for performance?

Querying Access is better than querying SQL in lower h/w?

Answer 1

Because SQL Server does run as a separate process, caches results, uses ram and processing power when not being queried, etc., IF the other computer has very little RAM or a very slow processor (or perhaps even more importantly a single-core processor), I could see a situation where SQL Server is actually SLOWER than MS Access use.

Without information about your hardware setup, approximately what percentage of your application relies on querying the database, etc., I'm not sure this question can be easily answered.

MS SQL Server 2005 Express requires at least 512 MB RAM (see http://www.microsoft.com/sqlserver/2005/en/us/system-requirements.aspx), so if your lower-end hardware doesn't have at least 512MB, I would certainly choose MS Access over SQL Server.

I should also add that you may want to consider SQLite (see http://www.sqlite.org/) which should be significantly less overhead than MS SQL Server. I'm not certain how it would stack up against MS Access use over something like Jet. My gut instinct is that it would perform better with less overhead.