x

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
more ▼

asked Nov 17, 2011 at 06:41 AM in Default

jerill gravatar image

jerill
41 10 14 14

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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][1].

[1]: http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
more ▼

answered Nov 17, 2011 at 07:13 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Nov 17, 2011 at 06:57 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

Hi Jack,

Below are my findings : - It is slow any part of the day. - Only one user is testing now. And that's me. - There are primary keys on all tables. So by default, SQL has indexes defined on these tables with primary keys. And i reconfirmed that. - I tried few of the forms. If table has 500 records, it comes very fast may be in few seconds. - Form which is having performance issue, that table has more than million records. Because we were using same form before in access database,and it use to take less than a minute to fetch the data.

Any other idea ?
Nov 17, 2011 at 07:11 AM jerill
(comments are locked)
10|1200 characters needed characters left

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.html http://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.
more ▼

answered Nov 27, 2011 at 09:31 AM

Banana gravatar image

Banana
21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x734
x593
x251

asked: Nov 17, 2011 at 06:41 AM

Seen: 1469 times

Last Updated: Nov 17, 2011 at 06:41 AM