0

I've created a linked server for an Access database from MSSQL that takes a very long time to return rows. I'm using SQL Server 2005 and the Access database is 2003. A select statement in SSMS of 8 columns and 80,000 rows takes 60s to run.

I gave SQL authenticated credentials ('sa' user) when creating the linked server so I don't think it has to do with permissions, and I'm unsure of what the problem is. I get the exact same running time using OPENQUERY as when I don't. Any guidance to why a simple query would take so long to run is very much appreciated.

My query.

SELECT * FROM AccessDB12...AP
shadowjfaith
  • 932
  • 8
  • 14
  • Without seeing the query, it's tough to say. However, when you use a linked server to reference a query, the optimizer has a tough time building an efficient plan. It essentially has to do a table scan; if you have multiple linked server references, you'll have multiple table scans. – Stuart Ainsworth Aug 01 '14 at 19:27
  • @StuartAinsworth I added the query to the question, just a simple select from one table. – shadowjfaith Aug 01 '14 at 19:30
  • Is the Access database on the SQL Server machine? – Pieter Geerkens Aug 01 '14 at 20:49
  • @PieterGeerkens It is. Everything in my case is local. – shadowjfaith Aug 01 '14 at 20:58
  • @shadowjfaith: It would seem that you have a hidden cursor somewhere then causing repeated trips to ACCESS. Have you tried uploading the entire ACCESS table into a SQL temporary table and then using that in your query? – Pieter Geerkens Aug 01 '14 at 21:01
  • @PieterGeerkens I haven't tried that, but data that has been transferred over from Access runs fine within SQL Server. Our process now to transfer data from the Access database takes hours, so I'm trying to use Linked Servers to make things go a bit quicker. How can I go about checking and fixing a hidden cursor? – shadowjfaith Aug 01 '14 at 21:07
  • @shadowjfaith: Look for an occurrence of either keyword CURSOR or WHILE, or any of the operators <, <=, >=, >, <> or !=. One reference here:http://www.sqlservercentral.com/articles/T-SQL/61539/ – Pieter Geerkens Aug 01 '14 at 21:10
  • @PieterGeerkens I don't have any of those. The query listed above is it, just a select of data, no conditions added. – shadowjfaith Aug 01 '14 at 21:25

0 Answers0