In converting another monolithic app to Access UI + SQL Server backend and trying to squeeze as much performance out of them both as possible, I noticed a weird Access thing when accessing a linked table: even if a particular query has a WHERE clause limiting the resultset by primary key value, MS Access feels the need to fetch all primary key values before requesting just the one record that is needed.
Sample Access query:
Actual queries sent over to SQL Server as seen by Express Profiler and Extended Events:
Would not ordinarily be a problem but with a larger number of records, a form that uses the query being opened as often as it is, and a mobile connection this is quickly becoming a major headache that slows the app down. Happens both with SQL Server Native Client 11.0 and the latest ODBC Driver 17 for SQL Server drivers.
Has anyone else come across this problem before?
Sample Access query:
SQL:
SELECT [Surname]
FROM [Clients]
WHERE [Id] = {guid {826E03DA-4F02-4D27-B85E-07A5F305ECD1}}
Actual queries sent over to SQL Server as seen by Express Profiler and Extended Events:
SQL:
SELECT "Id" ,"dbo"."Clients"."Id" FROM "dbo"."Clients" --um, why are you doing this?
go
declare @p1 int
set @p1=3
exec sp_prepexec @p1 output,N'@P1 uniqueidentifier',N'SELECT "Id","Surname" FROM "dbo"."Clients" WHERE "Id" = @P1','826E03DA-4F02-4D27-B85E-07A5F305ECD1'
select @p1
go
Would not ordinarily be a problem but with a larger number of records, a form that uses the query being opened as often as it is, and a mobile connection this is quickly becoming a major headache that slows the app down. Happens both with SQL Server Native Client 11.0 and the latest ODBC Driver 17 for SQL Server drivers.
Has anyone else come across this problem before?