Can migration from MS Access to SQL Server or … make database faster? [closed]

Posted on

Question :

A company asked me to migrate their database from MS Access to SQL Server or MySQL because they think that on those engines their database will run faster. But I wonder if their problem isn’t because of their database design rather than MS Access.

I think that their database is smaller than 1GB large and it will remain always smaller than 10GB. Let me be pessimist 100GB! I’m afraid that the migration doesn’t accelerate their query execution time and they would remain unsatisfied. I’d like to know if their slow interaction could be because of Access or I should contact consult them to continue working with access and just change the designs, optimize network and the programs?

In which case should I think migrations towards non relational databases like MongoDB?

Answer :

Access and SQL Server differ in a fundamental way. In Access all processing of the data is done by the client(s), while in SQL Server all processing (before delivering the query result) is done by the central server process.

That has several performance implications. In Access the client must transfer over the network all the data that is needed to produce the query result. Also, you don’t have exclusive access to the data file on the network drive which limits the caching options on the client (it cannot easily know if the data has been modified in the meantime).

SQL Server takes exclusive access to it’s (local or semi-local) datafiles and thus can cache both reads and (partially) writes, it can optimize it’s file access by removing as much random access as possible and it needs only to transfer the query result over the network to the client, which is often much smaller than the data needed to produce it, especially when using aggregates like SUM or COUNT.

On the other hand, while SQL Server can and does optimize effectively, it has do all the heavy lifting of multiple user access within the physical constraints of the server processor(s) and memory, while Access ‘only’ needs to synchronize the physical data access and offloads all calculation work onto the client machine.

So, as others wrote here, if you will see performance gains or not depends on the OS and network protocol to access the Acess-DB-File(s), the stats of the SQL Server you would put in place, the number and activity of the users and the nature of the queries they perform. Maybe you have enough information to make an educated guess based on this information.

We can’t really answer whether you should consider other platforms such as mongodb – we know nothing about the application or its data and whether such a platform would be appropriate.

I can tell you that SQL Server has a lot more features geared toward performance and tuning than Access will ever have. Access is pretty simplistic and, while it can function as a multi-user database, it is not a true RDBMS and really is meant for the desktop space.

Whether those features in SQL Server will make your company’s specific application any faster, again, shrug, we just don’t know.

Once you have migrated to SQL Server it becomes easier to improve the database performance. For instance you can start to use SQL Profiler to identify which queries are running slowly. Knowing these queries you can then review the SQL Execution plan. This will also suggest indexes that will improve performance.

You could try and performance tune an access database it is much easier in sql server and therefore worth upgrading.

Like Aaron said, we don’t know anything about the application or data (or number of users, or the hardware running the database, or…). I will say that in my experience, SQL Server is much faster and more robust than Access for things like web applications. Whether that means your application will run faster, well. Not enough information.

Leave a Reply

Your email address will not be published. Required fields are marked *