Results 1 to 7 of 7
  1. #1
    Nitin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    3

    MsAccess Performance Issue when connected to Teradata

    We connect MsAccess 2016 on Windows 7 64 bit, to Teradata using ODBC connection for Teradata.
    For querying purpose we use pass through Sql. However, MsAccess is not able to quickly consume the rows returned by Teradata that is causing performance issues.
    To further clarify, when we execute pass through query, Teradata would execute the same and send results(Aroud 2 Million rows) to MsAccess in 40 seconds flat. But MsAccess is not able to quickly consume those rows and display the results.
    Any suggestions, how we can make MsAccess to respond quickly.

    Between we have tried creating the internal tables on Msaccess and then query the same. But it does not help.

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    How many fields are being returned ?

    Is this over a WAN or local network ?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Nitin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    3
    It is over WAN and there are 2 million rows with 10 columns being returned. The latency is not coming from Network. We got that checked.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Moving 2 Million records over a WAN with access will be a problem.
    Guarantee it unless you have a Gigabit + backbone directly to the source.

    The way access retrieves data is a not as simple as you may think, I would have a good read here https://technet.microsoft.com/en-us/...or=-2147217396
    although it's referencing SQL server I think the information will help you.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Nitin is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    3
    Thanks for your response. What we have figured out is over ODBC MsAccess is sending multiple "Select" queries to fetch data. Its like if there are 100 rows in Teradata to be fetched there will be 100 different "Select" statements fired by MsAccess.
    Any thoughts on that?

  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Yes - if you read the article you will see that if this data is displayed on a form or datasheet, access will retrieve the data in chunks for display purposes, if you use a snapshot it will try and retrieve the records in one hit, if the ODBC driver is written that way.

    Do you really need 2 million rows ? Can you not restrict that data set down to something more manageable?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    ODBC MsAccess is sending multiple "Select" queries
    if your sql references local data or uses vba functions it will work that way.

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

Similar Threads

  1. Opening Query performance issue?
    By Kevin Ensign in forum Queries
    Replies: 3
    Last Post: 12-16-2016, 01:58 PM
  2. Linked Table query performance issue
    By patneel in forum Access
    Replies: 1
    Last Post: 07-31-2012, 12:09 PM
  3. Performance issue A2007, W7
    By Minimalist in forum Programming
    Replies: 1
    Last Post: 07-11-2012, 11:17 AM
  4. MSAccess Runtime issue
    By tkanuradha in forum Security
    Replies: 4
    Last Post: 03-21-2011, 11:57 PM
  5. Update Query Performance Issue
    By Amber_1977 in forum Queries
    Replies: 2
    Last Post: 12-07-2010, 08:36 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