Linking SQL Server view into Access db: performance question

Question

When you attach a SQL Server view as an MS Access linked table, you are asked for a unique identifier. During recent trials, I noticed that on several occasion, NOT defining the unique identifier has the effect that the linked table opens much more quickly (you don't need a stopwatch, you can really SEE it).

So I am asking the experts if there is an explanation to this, and what is the rule: define or not define an primary key for the attached view ?

My comparisons where made on the same machine, same Access 2007 db, same views, same driver (SQL Server 10), same SQL Server 2008 server.
For my case, I don't need to update the linked tables (which are SQL views).

Answer 1

Access only needs a unique identifier to be able to perform updates. If you don't define the unique identifier (and you don't need to), it isn't keeping track of them, so I guess it's faster. I'm not sure why the difference is so noticeable. How wide (columns and bytes) was the unique identifier you were picking on the slow version?

Answer 2

I replicated your results on my test machine, and even ran profiler to see if I could figure it out. I connected to a view twice, once with a unique identifer specified and one without. Same results as you.

Profile was not very illuminating; for the UNindexed view, it was a standard SELECT columnList FROM view. For the Indexed view, it only specified the key column (even though the results clearly show all columns).

The only thing that I can think of is that since you are specifying a unique key in Access, the data set must be completely pulled into memory in order for Access to associate the key column on the server with the local index. If there's no local index, then there's no need for that association to occur.