0

I have an Access 2010 .accdb front end with a linked table to a view in SQL Server (2008 R2). Surprisingly, opening it in Access takes around 5-10 seconds while executing a select in SSMS doesn't finish in 3 minutes (after that I just cancel the execution). What can be the cause?

EDIT: My mistake: What makes it unbearably slow is that I am trying to select only the first (TOP 1 *) record. Now I can keep on with my work and the question is only academic: I thought that selecting one record would be faster than selecting the whole recordset. What can be the cause?

I see it doesn't happen only to me: Why select Top clause could lead to long time cost

Community
  • 1
  • 1
Narciandi
  • 1
  • 1

1 Answers1

0

When you open the table in Access, Access requests the first records (at least as many to fill the first screen), and then continues to load more records in the background.

Simply doing a SELECT TOP 1 * FROM someTable in SSMS should always be fast if the table has a primary key.

Only if you add an ORDER BY clause on an non-indexed column, performance will suffer because the whole table must be scanned.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • The select is performed on a view with the structure "A left join (B union C)". Table A has no indexes at all (not even PK), but also I didn't write any ORDER BY clasue explicitly. – Narciandi Feb 24 '16 at 11:37
  • Aha. Well, what is "TOP 1" on a table without defined order (since it has no PK) even supposed to mean? Every table should have a PK, period. :) – Andre Feb 24 '16 at 11:49
  • "SELECT TOP 1 * FROM A" does work properly, the problem is when asked on the view. I will try to do some cleaning, add PK and let you know the result :) – Narciandi Feb 24 '16 at 13:17