Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-07-14
5
Medium Priority
?
709 Views
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
0
Comment
Question by:Dale Fye
  • 3
  • 2
5 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40194782
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.

/gustav
0
 
LVL 49

Author Comment

by:Dale Fye
ID: 40194903
Gustav,

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?
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40195095
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.

/gustav
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 0 total points
ID: 40204057
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.
0
 
LVL 49

Author Closing Comment

by:Dale Fye
ID: 40211096
Found solution on another resource.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

810 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