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

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
LVL 50
Dale FyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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.

Dale FyeAuthor Commented:

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:

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?
Gustav BrockCIOCommented:
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.

Dale FyeAuthor Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeAuthor Commented:
Found solution on another resource.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.