Hi, I have existing application which uses Sql server 2005 as a backend. It contains huge records, I need to join tables which contain 50K-70K. Client m/c is lower hardware.
So, can I improve its performance by using MS Access as a backend? I also need to search operation on access file. So, which one is better for performance.
Querying access is better than querying sql in lower h/w?
SQL Server will always give you better performance because the query is executed on the server. Access on the back-end won't help because your client application will need to pull all the data from the tables, and then perform the join locally.
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.
70,000 records is really not that big for SQL server (or access for that matter). I would echo what has already been said and say that all things being equal SQL server will out perform Access.
I would go back to your query and look at the execution plan to see why it is so slow, maybe missing indexes, out of date statistics or a whole host of other reasons could explain your current performance problems.
SQL server also gives you the option of using materialised views to help with performance. The trade of is slower insert/update/delete performance but if you read more than you write it might be worth it.
I think Albert Kallal's comment is right, and the fact is that if you have a single-user app running on a single workstation (Access client with SQL Server running on the same workstation as a client), it will quite often be slower than if the setup on that workstation were Access client to Jet/ACE back end on the same machine. SQL Server adds a lot of overhead that delivers no benefit when there is no network in between the client and the SQL Server.
The performance equation flips when there's a network involved, even for a single-user app. If the Access client runs on a workstation, and the SQL Server on a server on the other end of a network connection (even a fast one), it will likely be faster than if the data is stored in a Jet/ACE file on a file server.
But it's not a given, in my opinion. It depends entirely on the engineering of the application and the excellence of the schema.