Results 1 to 5 of 5

Stored Procedures Slower in Access than Management Studio

  1. #1
    mancusor is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Location
    New York
    Posts
    2

    Stored Procedures Slower in Access than Management Studio

    I don't know why some stored procedures run more slowly when I call them from Access rather than SQL Management Studio.

    I have setup a Data Source using the SQL Server driver.

    As an aside, I am having the same problem in Asp.Net



    Does anyone have any ideas?

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,738
    I assume Access is not running on the server, which would mean that any data returned by the SP has to travel over the wire with Access. It doesn't with SSMS (presuming it is on the server).
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    mancusor is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Location
    New York
    Posts
    2
    Both Access and SSMS are running on my PC and calling to a remote database.

  4. #4
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    596
    If the database is on the server, the data will have to be pulled over the network, no matter where SSMS is installed. Some possibilities to increase the speed :
    - Try to limit the amount of data you pull over when opening the form. Never pull over the complete table content, but open with an empty form and populate it trough a search action.
    - Try to avoid the use of subforms
    - Check your indexes and keys
    - work with pass-through queries and let the server execute the query

  5. #5
    turbofish is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    25
    There is a good chance that the issue has to do with parameter sniffing. You will need to take a look at the execution plan to be sure

    Here are a couple of good links that will describe it in detail

    https://www.brentozar.com/archive/20...in-sql-server/
    This link will be a good place to start - they have a way of making the complex easy to understand

    http://www.sommarskog.se/query-plan-mysteries.html
    Is another great place to look. In fact, the title of this page is Slow in the Application, Fast in SSMS

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

Similar Threads

  1. Replies: 3
    Last Post: 10-14-2017, 04:01 AM
  2. IntelliSense is not working in Management Studio 2012
    By adnancanada in forum SQL Server
    Replies: 0
    Last Post: 09-29-2016, 07:58 AM
  3. IntelliSense (SQL Server Management Studio)
    By adnancanada in forum Queries
    Replies: 0
    Last Post: 08-04-2016, 10:42 PM
  4. Dance Studio Management Database
    By advomystics in forum Database Design
    Replies: 5
    Last Post: 01-28-2016, 10:10 PM
  5. Replies: 1
    Last Post: 06-19-2015, 07:42 AM

Posting Permissions

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