Solved sp_execute Invoked for Every Row Returned by Query (1 Viewer)

PleasantB

Registered User.
Local time
Today, 05:21
Joined
Dec 12, 2018
Messages
37
I've upgraded an Access 2003 application to Access 2016 and migrated it's back-end to SQL Server.
I'm experiencing lockups on a particular form which features ~25K records.
Upon running an SQL Server Profiler trace, I noticed that for every row returned by query, an sp_execute query is also being invoked.

Form Record Source:
SQL:
SELECT PileInfoMain.Batch, PileInfoMain.Stake, PileInfoMain.WasteStream, PileInfoMain.DateStaged, PileInfoMain.SpecialStatus, PileInfoMain.CurrentStatus, PileInfoMain.Comment
FROM PileInfoMain
WHERE (((PileInfoMain.CurrentStatus)="DONE"))
ORDER BY PileInfoMain.Batch;

I tried a few suggestions I found while Googling the issue, to no avail: removing the WHERE clause, removing the ORDER BY clause, and making the query pass-through. Finally, I changed the form's Recordset Type from Dynaset to Snapshot and the sp_execute queries disappeared.

I don't necessarily want to get rid of Dynaset because I want changes made by other users to be reflected without closing and reopening the form.
Is there any way to keep Dynaset without the extraneous sp_execute queries?
Does anyone know why Dynaset causes all of these sp_execute queries?

Thanks in advance!
 

Minty

AWF VIP
Local time
Today, 11:21
Joined
Jul 26, 2013
Messages
10,354
Without trying to sound too blunt, I wouldn't be pulling in 25K records into an editable form. I'm guessing it's a continuous form?
Use a snapshot to present the data and have a method to edit a single record based on which one is selected, this would be the normal way to deal with a large dataset.

Do you have any triggers on the table?
 

PleasantB

Registered User.
Local time
Today, 05:21
Joined
Dec 12, 2018
Messages
37
Thanks for the response.

This is a continuous form but it is not editable. There is a button on each row that opens a single form for editing the record.
When I said "I want changes made by other users to be reflected without closing and reopening the form" I was referring to when another user edits or adds a record via the single form but perhaps Dynaset doesn't provide that anyway.
I may be overthinking this, I haven't worked on Access much in recent years so my knowledge is rusty/foggy.
 

Minty

AWF VIP
Local time
Today, 11:21
Joined
Jul 26, 2013
Messages
10,354
In that case, I would use a snapshot if you are sticking with Access, as it will be much more efficient.
You could force a recordset requery on a timer to keep things fresh.

Spotting edits on records is hard in a snapshot, knowing that records have been added is simpler with a simple recordset count between form opening, and perhaps pressing the button to edit a record you can notify the end-user that data has been added?

The alternative would be SQL view, as that wouldn't normally be editable but should be dynamic. I'm not sure any form will accurately show you changes without a recordset requery.
 

PleasantB

Registered User.
Local time
Today, 05:21
Joined
Dec 12, 2018
Messages
37
I'm going to check with the client to see how important live updates are, it may be that they don't actually care.
Either way I'll stick with Snapshot and implement a mechanism like you suggested for live updating, if need be.

Thanks again for the responses!
 

Users who are viewing this thread

Top Bottom