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.
, SUM(fdprod.Gas) as Gas
, SUM(fdprod.oil) as Oil
, SUM(fdProd.Water) as Water
, MAX(fdProd.Stamp) as Stamp
tion 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
Provider string: blank