Results 1 to 6 of 6
  1. #1
    jerill is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Nov 2011
    Posts
    8

    Performance issue after upsizing from Access 2003 to sql server 2008R2

    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.

    - 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

  2. #2
    jerill is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Nov 2011
    Posts
    8
    Forget to mention, in Access FOrms, we have access queries and macros.

  3. #3
    RayMilhon is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    Depends on how your Access DB was structured. If It was all in one Local Database moving it to SQL Server Adds Network traffic. My suggestion would be to add a few things. Create SQL Server Views from your access Queries then link those views that puts all of the processing on SQL Server. If your queries are in Access and the data is in SQL Server Access will bring the entire dataset across the network and then apply the criteria to it locally. So say without criteria your query retrieves 30,000 rows but the criteria limits it to 30 rows. Access will bring the entire 30,000 rows across the network and then apply the criteria. Putting the Query into a SQL Server View means only the 30 rows come across the network.

  4. #4
    jerill is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Nov 2011
    Posts
    8
    I guess, that's what is happening. Access is bringing the code, adding filters etc and that's why it is slow.

    I can create sql views for access queries But, 1) I have more than 300 queries, so do you want to create view for each query 2) Lets say i created view, but now how do i make access report / form to point to the view in sql server database.

  5. #5
    RayMilhon is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    Depends on the structure of the queries.

    Say I have the following Access Queries

    1. Select * from tableA Where Closed = 0 and dtfrom between [startdate] and [enddate]
    2. Select * from tableA Where Closed = 0 and dtthru between [startdate] and [enddate]
    3. Select * from tableB Where Name = [username]

    1 and 2 are similar so the view could be structured as follows

    Create View View1
    As
    Select * From TableA Where Closed = 0

    You then would have to link access to the View and Query on the dates

  6. #6
    RayMilhon is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    Just realized I didn't answer 1 question. You would link to the view same as linking to a table. As far as Access is concerned they are the same thing.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-09-2011, 12:08 PM
  2. Replies: 5
    Last Post: 07-17-2011, 08:57 AM
  3. Replies: 1
    Last Post: 07-16-2011, 01:40 PM
  4. Update Query Performance Issue
    By Amber_1977 in forum Queries
    Replies: 2
    Last Post: 12-07-2010, 08:36 AM
  5. access 2003 automation issue
    By BevA in forum Programming
    Replies: 2
    Last Post: 05-01-2006, 08:37 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums