Performance issue after upsizing from Access 2003 to SQL Server 2008 R2
Hi, I am having really hard time after upsizing from Access 2003 to SQL Server 2008 R2. I had Access 2003 and database. Previously when I ran Access forms, they were very fast but after upsizing it, it takes 10 times more time to refresh. - I upsized using upsizing wizard from access 2003 to sql server 2008 r2. I did checked everything like ( table relationship, link tables etc ) while upsizing it. We have access queries and macros in access forms / reports. - So why is this performance issue now after upsizing it. Am I missing something ? - Do I need to create some odbc etc connection on client machine - Do I need to again create relationship diagram in sql server database. ( i already had in access and i checked that option to upsize it as well ) - What is causing performance issue ? Any idea
Access applications can sometimes handle table/data access very poorly. They can hold locks on whole tables and cause other issues. Have you identified what parts of the application are causing the issues? Does it occur when there are only 1 or 2 users or does it need 10 or 20 before it slows? Is it at certain times of day? Once you can answer some of those then you may need to address issues in the Access forms and reports or look at the database. For sure there will be things to do in the database as the upsizing process doesnt really consider indexing at all. It is likely the missing indexes dmv(s) will guide you to improvments in that area (sys.dm_db_missing_index_details). You will also gain from moving any data access code into stored procedures over having it in Access.
SQL Server is not, unfortunately, a magic bullet. The wizard just moves your stuff up into SQL Server, but it doesn't set it up in a way that is functional within SQL Server. The structures and code within SQL Server that work best are not the same as the structures and code within Access. So a query that might run well enough in Access can seriously stink in SQL Server. Now that you've made the move, you will have to start to adjust your structures and code to perform well. This means identifying the primary pain points, understanding why they are performing slow, and figuring out what to do to fix them. I'd start by using the Dynamic Management Objects. You can query sys.dm_exec_query_stats. That DMO has aggregate performance metrics for all queries currently in cache. That will tell you what is being called the most and running the slowest. You can combine it with sys.dm_exec_sql_text to understand what query is being called and sys.dm_exec_query_plan to see the execution plan. From there, things get hard. As an introduction to the topic, I'd suggest reading Gail Shaw's article on how to identify the slowest running queries on [Simple-Talk]. :
As Grant pointed out, moving data isn't a magic bullet and the finding that performance has decreased after the move is more likely the expectation, rather than the exception especially when Access application wasn't designed with server-client architecture from scratch. One major factor is that data is no longer local so Access has to use up network traffic to pull data and if you request Access to evaluate something that cannot be expressed in standard SQL, Access will have no choice but to pull down everything and perform the evaluation locally to satisfy your requests. This is discussed in details in those articles:
http://www.utteraccess.com/forum/Beginning-Sql-Server-Deve-t1732935.htmlhttp://www.utteraccess.com/forum/Beginner-s-Guide-ODBC-t1843709.html (especially in the section "Jet and ODBC; how to use Jet intelligently" Both articles also include additional links discussing optimizing Access applications with a server-based backend.