Spurious 'SELECT [Id] FROM [Table]' (1 Viewer)

davidcie

New member
Local time
Today, 15:48
Joined
Dec 30, 2018
Messages
12
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:
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?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:48
Joined
Jul 9, 2003
Messages
12,110
Has anyone else come across this problem before?
I don't have a lot of experience with SQL Server, I only did one project and that was years ago, and I had a lot of help! So I'm posting this message to bump you up the list so that someone with SQL experience might see it.
 

Minty

AWF VIP
Local time
Today, 14:48
Joined
Jul 26, 2013
Messages
7,529
Normally I use
WHERE ID=0
to open the form with no underlying record, then get the user to select/search for a specific record.
I can't say I've noticed a massive performance hit, and we use Azure so the data isn't vaguely local...

I'm not able to look properly ATM but I can have a poke around tomorrow to see if we are seeing the same behaviour.
 

davidcie

New member
Local time
Today, 15:48
Joined
Dec 30, 2018
Messages
12
Thank you for your input, I'd love to get to the bottom of this.

After some more testing it seems that GUIDs appear to be the source of my problems. I've prepared a simple test case with the same customer data stored in two tables: one with uniqueidentifier as the primary key, and second one where primary key is an int. Opening identical filtered forms based on these two tables results in different queries being sent by the ACE engine to SQL Server as seen by Express Profiler:

SQL:
--Me.RecordSource = "SELECT * FROM dbo_CustomersGuid WHERE [Id] = {guid {5FB625C8-0012-EB11-8127-001C42621548}}"
SELECT "Id" ,"dbo"."CustomersGuid"."Id" FROM "dbo"."CustomersGuid"
go
SELECT CASE DATABASEPROPERTYEX( DB_NAME(), 'Updateability') WHEN 'READ_ONLY' THEN 'Y' ELSE 'N' END
go
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 uniqueidentifier',N'SELECT "Id","Name","Surname"  FROM "dbo"."CustomersGuid"  WHERE "Id" = @P1','5FB625C8-0012-EB11-8127-001C42621548'
select @p1
go

SQL:
--Me.RecordSource = "SELECT * FROM dbo_CustomersInt WHERE [Id] = 907"
SELECT "dbo"."CustomersInt"."Id" FROM "dbo"."CustomersInt" WHERE ("Id" = 907 )
go
declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "Id","Name","Surname"  FROM "dbo"."CustomersInt"  WHERE "Id" = @P1',907
select @p1
go

Hoping I'm just not understanding enough, and there might be a hidden SET ROWCOUNT somewhere in the case of GUIDs, I set up an Access-to-SQL Server decrypting session with Microsoft Message Analyzer to check what's being transferred over the wire. Sadly, it's as bad as it looks; even for ~15k records in my sample database this results in a ~2MB download of unnecessary data.

Not the first time I see GUIDs treated in an unusual way by MS Access, but perhaps there is some workaround..? Other than a secondary keyset that is.
 

davidcie

New member
Local time
Today, 15:48
Joined
Dec 30, 2018
Messages
12
Well, sorry to spam but after writing all the above I had a hunch I could try re-writing the query and... voila, we have a solution to the problem. Basically you have to enclose the GUID in quotes rather than use the Access-specific {guid {}} notation – even though I came across errors before when I tried that in different contexts.

The queries Access produces now are as expected:

SQL:
--Me.RecordSource = "SELECT * FROM dbo_CustomersGuid WHERE [Id] = '{5FB625C8-0012-EB11-8127-001C42621548}'"
SELECT "dbo"."CustomersGuid"."Id" FROM "dbo"."CustomersGuid" WHERE ("Id" = '\5FB625C8-0012-EB11-8127-001C42621548\' )
go
SELECT CASE DATABASEPROPERTYEX( DB_NAME(), 'Updateability') WHEN 'READ_ONLY' THEN 'Y' ELSE 'N' END
go
declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 uniqueidentifier',N'SELECT "Id","Name","Surname"  FROM "dbo"."CustomersGuid"  WHERE "Id" = @P1','5FB625C8-0012-EB11-8127-001C42621548'
select @p1
go

Thank you for participating in this small guid journey and inspiring me to try out different things!
 
Last edited:

Minty

AWF VIP
Local time
Today, 14:48
Joined
Jul 26, 2013
Messages
7,529
That's a really interesting find, thank you for posting back about it.

I've only had limited use of GUID fields, so not experienced the issue.
 

Users who are viewing this thread

Top Bottom