SQL Server query of Linked Server tables (from Access) takes forever to run

Posted on 2014-07-14
Last Modified: 2014-07-22
I'm migrating an application that has had both front-end and back-end in Access to SQL Server.

Most of the tables in the Access BE files have been (or will be) migrated to SQL Server.  However, one of the external data sources that I pull data from is still going to be a set of Access data files my client receives from an external application.  

I have created linked servers to these Access database (.mdb) files.  When I do a simple query against one of these linked Access tables, it generally runs quickly.  However, I'm finding is that more complex queries run against these linked Access databases take significantly longer to run in SQL Server than in Access.  

As an example, this query took about 2 seconds to run from Access, and returned about 62000 rows.  But in SQL Server, it took, well, I stopped it after 10 minutes with no records returned.

select fdprod.EntityID
, fdprod.docDate
, SUM(fdprod.Gas) as Gas
, SUM(fdprod.oil) as Oil
, SUM(fdProd.Water) as Water
, MAX(fdProd.Stamp) as Stamp
from FIELD_DIRECT_FDProd...SumDailyProduction as FDProd
WHERE fdprod.Stamp >= '2014-04-14'
GROUP BY FDProd.EntityID, fdprod.docdate

I'm wondering whether there is something more that I need to be doing with regard to my linked servers settings.

Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider
Product Name: Access
DataSource: \\myNetworkpath\fdprod.mdb
Provider string: blank
linked server options
Question by:Dale Fye (Access MVP)
    LVL 48

    Expert Comment

    by:Gustav Brock
    I have found these Linked Server tables to operate very slowly for anything else than simple selects. Don't know why.
    I ended up just importing the data to (temp) tables in SQL Server, then querying these.

    It was SQL Server 2005 but I don't think it has changed much.

    LVL 47

    Author Comment

    by:Dale Fye (Access MVP)

    I'm even having trouble with that.  I've got an Access table with 1.6M records that Access loads in seconds, but which I generally query by date > date() -90

    When I tried to do a simple:

    SELECT *
    INTO #TempProd
    FROM Field_Direct_FDProd...sumDailyProduction

    it ran for over 7 minutes before I stopped it.  I'm going to try creating the temptable with all the apropriate fields and then import into the temptable, rather than trying to create the temp table as in a "make table" query.  Is there a functionality like OpenRowSet for importing from Excel that I could use with Access tables?
    LVL 48

    Expert Comment

    by:Gustav Brock
    Well, my tables were much smaller, so I cannot tell.

    I found it quite difficult to locate information about the Linked Server option, indeed regarding Access files. Most seem to use it to link a spreadsheet or so.

    LVL 47

    Accepted Solution

    Have read in several other forums that this is a common issue when reading from linked Access tables.  I ended up modifying my code to simply import the tables into temporary tables in the SQL server and do all of the leg hard work in SQL Server.
    LVL 47

    Author Closing Comment

    by:Dale Fye (Access MVP)
    Found solution on another resource.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now