Query Timeout expired

[Bitsqueezer]

Hallo,

ich habe gerade mal ein paar Tests gemacht mit Access und SQL Server.
Im Anhang findest Du die Ergebnisse.

Sie dürfen in diesem Board keine Dateianhänge sehen.

Es war kein Problem, parallel zum geöffneten Formular einen UPDATE per SSMS durchzuführen.

Treiber für die Verbindung: ODBC 17 for SQL Server.

Ich habe das Formular jeweils per verlinkter Tabelle (also editierbar in Access) und auch als PT-Query (also read-only) verwendet - parallels UPDATE kein Problem. Auch per Access-Formular, kein Problem.

Auch wenn ich Deinen VBA-Code verwende (mit der Abwandlung, CurrentDb.OpenRecordset auf die PT-Abfrage, aber es ging ja um das ".Clone"), kein Problem.

Manko Deiner Version: Du erstellst eine Kopie des Recordsets (das passiert lokal auf Deinem Rechner), dann weist Du diese dem Formular-Recordset zu. Wenn Du jetzt einen Filter setzt im Formular, dann wird dieser auf das Recordset angewendet. Wenn Du den Filter aufheben willst per Navigationsleiste, wird der Filter aber nicht mehr entfernt - das Recordset hat nun keine Verbindung zum Backend mehr, neue Daten können nicht abgefragt werden.
Dabei darf man nicht vergessen, daß das Formular-Recordset-Objekt ein Wrapper ist, der sowohl ADO- als auch DAO-Recordsets annehmen kann. Es ist also kein "normales" Recordset-Objekt.

Die Daten im Anhang sind aus den SQL Server XEvents entnommen, was die neue Version des SQL Profilers darstellt, einfach gesagt. Also Mitschnitt, was passiert im Hintergrund zwischen Access und SQL Server.

Hier kann man sehen, wie Access die verschiedenen Szenarien abhandelt (Datenbank ist die Standard-Chinook-Demodatenbank für SQL Server, Tabelle "Invoice", mit "Id" als PK).

Verlinkt man die Tabelle direkt in Access und nimmt diese als Formularquelle, verwendet Access diese Abfrage auf dem Server:

Code [Auswählen] Erweitern

SELECT "Id" ,"CustomerId" ,"InvoiceDate" ,"BillingAddress" ,"BillingCity" ,"BillingState" ,"BillingCountry" ,"BillingPostalCode" ,"Total"  FROM "dbo"."Invoice"

Es werden also nur die Spalten, die in den Metadaten der verlinkten Tabelle gespeichert werden (lokal in Access) auf der SQL Server Tabelle abgefragt.

Interessant ist, wie Access nun die Filterung durchführt (hier auf "enthält Faria" in "BillingAddress"):

Code [Auswählen] Erweitern

SELECT "dbo_Invoice"."Id" FROM "dbo"."Invoice" "dbo_Invoice" WHERE ("BillingAddress" LIKE '%Faria%' )
Access fragt nicht die Tabelle ab und holt sich dann die nötigen per WHERE. Stattdessen fragt Access nur nach allen Werten der Spalte "Id" (also PK), auf die das WHERE zutrifft.

Danach wird eine Ausführung vorbereitet für eine System-SP:

Code [Auswählen] Erweitern

declare @p1 int
set @p1=7
exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "Id","CustomerId","InvoiceDate","BillingAddress","BillingCity","BillingState","BillingCountry","BillingPostalCode","Total"  FROM "dbo"."Invoice"  WHERE "Id" = @P1',5
select @p1

Und dann geht das Lazy Loading los, was im Anhang ab Zeile 54 für die DAO-Abfrage-Version enthalten ist, aber im Prinzip genauso auch bei Direktverlinkung passiert.

Unterschied ist: Bei Tabellenverlinkung hat man ja keine Sortierung angegeben, daher ist die Sortierung der PK. Entsprechend ist das Lazy Loading einfacher, weil nicht zuerst die Id-Spalte ermittelt werden muß, sondern einfach eine PK-ID, bis zu der gelesen werden soll (Beispiel):

Code [Auswählen] Erweitern

exec sp_execute 1,5
exec sp_execute 1,15
exec sp_execute 1,50
exec sp_execute 1,90
...
Was hier ausgegeben wird, hängt direkt vom Formular ab: In einem Endlosformular werden zunächst die Datensätze gelesen, die man aufgrund der Anzahl Datensätze (Platz im Formular) direkt sehen kann plus ggf. ein paar mehr. Ab dann alle paar Sekunden die nächsten paar.

Scrollt man mit dem Scrollbalken oder springt man an das Ende, ist es wie bei "MoveLast", die Datensätze werden nun sofort angefordert (beim Scrollen aber auch nur soweit, wie man gescrollt hat).

Also so kann man festhalten, daß es auch bei 100 Datensätzen zu Lazy Loading kommt, weil Access das einfach immer so handhabt, egal wieviele Datensätze nach der Anzahl im Formular noch kommen.

Bei Filterung/Sortierung muß Access hier darüber hinaus auch die IDs erst ermitteln und verwendet dann die IDs, um die Gesamtdaten auszugeben:
Im Beispiel im Anhang in der Query unten:

Code [Auswählen] Erweitern

exec sp_execute 7,79,199,213,228,237,251,253,330,364,371
exec sp_execute 7,372,420,421,25,63,86,87,96,223,226
exec sp_execute 7,282,340,110,183,184,214,229,241,360,408
exec sp_execute 7,446,27,30,262,356,403,414,28,45,73
exec sp_execute 7,135,165,269,326,387,33,49,178,250,259

"7" ist dabei das Handle für diese spezielle Abfrage, die mit dem Prepare erstellt wurde. Dahinter die IDs in der Reihenfolge der Sortierung und Menge wie angefordert.

Am Timestamp kann man sehen, wieviel Zeit zwischen jeder Abfrage liegt:

Code [Auswählen] Erweitern

2025-04-22 12:02:26.3063588
2025-04-22 12:02:43.3057814
2025-04-22 12:02:54.8721028
2025-04-22 12:03:09.1540780
2025-04-22 12:03:22.1853937

Beim Scrollen dagegen (also wie bei MoveLast):

Code [Auswählen] Erweitern

2025-04-22 12:04:50.5943679
2025-04-22 12:04:50.6266118
2025-04-22 12:04:50.6269551
2025-04-22 12:04:50.6273435
2025-04-22 12:04:50.6492832
2025-04-22 12:04:50.6496318
2025-04-22 12:04:50.6499214

Wenn man im Formular die Sperrungen auf "Alle Datensätze" einstellt (gebundene verlinkte Tabelle), meckert der ODBC-Treiber und sagt, das sei nicht möglich. Stellt man auf "Bearbeiteten Datensatz", geht es ohne Probleme, ebenso wie "Keine Sperrung".
Dennoch ignoriert Access "Bearbeiteter Datensatz", es wird in beiden Fällen optimistisches Locking verwendet, also paralleler UPDATE in SSMS auch bei begonnenem Datensatz problemlos möglich. Erst beim Speichern sagt Access natürlich, daß der Datensatz von einem anderen User (SSMS) verändert wurde. Also eindeutig optimistisches Locking.
(In beiden Fällen läßt Access hier aber auch nicht "eigenen Datensatz speichern" zu.)

PT-Query:
Interessant ist, daß Access den Inhalt der PT-Query ganz offensichtlich komplett ignoriert. Es analysiert also nicht, was hier abgefragt wird, es gibt nur die Einstellung in der Abfrage, ob Datensätze zurückgegeben werden oder nicht. Daher wird hier die "SELECT *"-Abfrage 1:1 an SQL Server weitergegeben und nicht, wie man es eigentlich machen sollte, eine konkrete Feldliste verwendet.
Im Unterschied zu einer verlinkten Tabelle/Abfrage hat es sicherlich den Vorteil, daß man bei Änderungen im Backend das Frontend bei so einer Abfrage nicht anpassen muß, allerdings auch keine Kontrolle, welche Felder geladen werden und ob die Feldnamen noch übereinstimmen mit dem, was man im Frontend braucht. Sollte man also, wie immer, besser vermeiden.

Aber viel wichtiger: Auch die Art der Filterung verändert sich. Während Access bei verlinkten Tabellen/Abfragen den Aufbau der Tabelle genau kennt und die Filter an SQL Server anpaßt und weitergibt, gibt es keinen solchen Automatismus bei PT-Queries!
Das bedeutet: Wenn ich einen Filter bei Verlinkung einsetze, geschieht die Filterung direkt auf dem SQL Server. Das mag nicht optimal gestaltet sein und eine SP oder UDF mit einem Filter wäre hier vermutlich ein wenig schneller, aber immerhin kommen nur die Datensätze zurück, die ich haben möchte.

Bei der PT-Query dagegen, wenn man hier nicht selbst einen WHERE- bzw. ORDER BY-String einbaut, werden immer alle Datensätze geladen. Verwendet man dann den Formularfilter, werden die Datensätze wiederum erneut komplett geladen und lokal im Recordset gefiltert.

Fazit aus allen Tests ist aber, daß es in keinem der Szenarios zu irgendwelchen Lock-Problemen kam. Datensätze konnten zu jeder Zeit von beiden Seiten (Access und SSMS) bearbeitet und gespeichert werden (natürlich nicht bei den PT-Abfragen, da ja read-only, aber auch kein Locking bei geöffnetem Formular mit PT-Abfrage).
Auch nicht, obwohl und solange Access alle paar Sekunden per Lazy Loading neue Datensätze angefordert hat.

Man kann also festhalten: MoveLast ist nicht notwendig, um Lockingprobleme zu verhindern, es hilft lediglich, das Lazy Loading abzuschalten, um z.B. die Anzahl Datensätze richtig anzuzeigen bzw. direkt zum Ende scrollen zu können (statt "Scrollbarsprüngen", auch und besonders bei Komboboxen/Listboxen, wo man statt "MoveLast" den Effekt durch Abruf von "ListCount" erzeugen kann).
Es erhöht aber auch die Netzwerklast, weil dann immer alle Datensätze heruntergeladen werden - bei jedem User. Lazy Loading dagegen lädt im Beispiel gerade mal 10 Datensätze auf einmal und wartet dann einige Sekunden, was bei Parallelbetrieb deutlich weniger Last erzeugt - und, wie man sieht, keine Datensätze dabei gesperrt werden. Auch nicht bei "Bearbeiteten Datensatz sperren"-Einstellung.

Die Netzwerklast ist gleichermaßen erhöht, wenn man einfach nur "SELECT * FROM Tabelle" verwendet, ohne einzuschränken, welche Datensätze oder welche Sortierung. Filterungen passieren dann nur lokal. Änderung des Filters muß ggf. keine Datensätze erneut abrufen, in meinen Tests jedoch hat Access die PT-Abfrage vor Filteränderung einfach erneut ausgeführt.

Test war A2013 auf lokalem SQL Server 2022 Developer Edition, ODBC 17-Treiber mit System-DSN.

Nach diesen Tests kann man also nur vermuten, daß im verwendeten VBA-Code, dem verwendeten Treiber oder sonstigen parallel laufenden Clients das Problem mit den Locks entsteht.
Ich würde hier also davon absehen, PT-Queries zu verwenden, wenn man deren SQL nicht an die Situation anpaßt. Am empfehlenswertesten ist eine DAO-Abfrage auf eine verlinkte Tabelle oder, wenn es möglich ist, auf eine verlinkte View, die im Code der View ggf. bereits passende Vorfilterung durchführt (bitte keine Sortierung in Views einbauen, die ist nicht garantiert) und darauf dann eine DAO-Query (also lokal).

Im Formular kann man dennoch "Snapshot" einstellen, was die Daten schneller herunterlädt und genau wie bei einer PT-Abfrage das Formular read-only macht. Zusätzlich kann man noch die "Allow..."-Settings des Formulares anpassen, wenn man bestimmte Änderungen generell verbieten will (AllowEdits auf False etwa läßt den Inhalt aller Felder nicht editieren, Snapshot dagegen schon und sagt erst beim Speichern, daß das nicht änderbar ist).

Gruß

Christian

[Stele4]

Hallo!
Meinen herzlichen Dank fuer das Interesse und den ausserordentlichen Aufwand.
Ich habe bestimmt nicht die Haelfte von eurem Disput verstanden, aber Einiges konnte ich doch mitnehmen und in mein Weltbild einbauen.

Ein Tutor wies mich darauf hin, dass das Recordset der View aufgrund ihrer Komplexitaet nicht schreibbar ist.
Er hat eine Kopie und konnte es mir demonstrieren.
Ich hatte es nicht so eingeschaetzt. Hatte ich das in dem Hin und Her nicht getestet? Wie erklaere ich das im Forum?

Mit dem Wissen wollte ich heute endlich vorankommen. Doch die Tabelle (Teil der View) liess sich nicht schreiben, sobald das Formular geoeffnet wurde.
Wurde das Formular geschlossen, liessen sich Tabelle und View(!) schreiben.

Ich habe in Access ein neues Formular auf Basis der eingebundenen View generieren lassen und geoeffnet.
Die Tabelle in SSMS liess sich schreiben.
Der Vergleich aller Eigenschaften der Formulare brachte keine Differenz.
Dann habe ich die gebundenen Textfelder aus dem Kopfbereich des alten Formulars in den Kopfbereich des neuen Formulars kopiert.
Die Tabelle in SSMS liess sich jetzt nicht mehr schreiben.
Alles noch mal von vorn. Alles gut.
Nun habe ich die gebundenen Textfelder einzeln kopiert und immer wieder getestet, um den Fehler zu finden.
Die Tabelle in SSMS liess sich immer schreiben.

Jetzt habe ich ein identisches Formular, das wie erwartet funktioniert. ...WTF...

Dank und Gruss
Stele

[Bitsqueezer]

Hallo,

wobei Du noch zwischen View und Query unterscheiden mußt (obwohl beides natürlich irgendwie das gleiche ist). "View" sagt man beim SQL Server und "Query" bei Access.
Views können auf dem SQL Server erstellt werden und wie eine Tabelle verlinkt werden. Zusätzlich können Views und Tabellen sowohl lokal wie auch auf dem Server verbunden werden mit einer neuen Abfrage. Das hat alles so seine Fürs und Widers.
Gerade bei Views mußt Du aufpassen: Access erkennt nicht immer beim Einbinden, welches Feld als eindeutiges Feld zu verwenden ist (also als PK, was aber nicht der PK in der View sein muß, sondern nur ein Feld in der View, das eindeutig sein muß). Access fragt dann beim Einbinden nach einem passenden Feld. Kann man auch per Code machen. Aber wenn man das verpaßt, ist die verlinkte View u.U. nicht mehr beschreibbar, auch wenn sie es auf dem Server ist.

Gruß

Christian

[Stele4]

Hallo Bitsqeezer!
Guter Hinweis! Ist auf der ToDo-Liste. Danke!

Kleiner Nachtrag:
Das neue Formular war noch nicht ganz identisch.
Im Endlosformular soll der aktive Datensatz farblich markiert werden.
Es wurde mit einem Textfeld und 'Conditional Formatting' realisiert.
Dem Textfeld wird im Formularereignis 'Form_Current' die [ID] zugewiesen.
Jetzt zittert das Formular bei jedem Zeilenwechsel und eine Aenderung in einem Feld wird nicht mehr uebernommen, sondern fuehrt zu Timeouts. Auch bei folgenden Klicks.

Immerhin ein reproduzierbarer Zusammenhang.
Ich verzichte auf das Feature und blende die Datensatzmarkierer ein.

Gruss
Stele

[Bitsqueezer]

Hallo,

was immer Du mit "Zittern" meinst. Aber natürlich bringt jede grafische Spielerei Performance-Verluste vor allem beim Scrollen in Endlosformulare.
Einen Einfluß von CF auf Speicherung der Inhalte sehe ich aber nicht, da gibt es normalerweise keinen Zusammenhang.

Gruß

Christian