sql per VBA ändern

[Jens Wiedmann]

Hallo,

kann man eigentlich den SQL String einer Abfrage irgendwie per VBA ändern?

Der Hintergrund ist der, dass ich einige Abfragen verändern muss und damit
ich nicht jede Abfrage per Hand öffnen muss um die Änderungen vorzunehmen,
hab ich mir überlegt ob man irgendwie per Script alle Abfragen öffnen,
verändern und dann wieder schließen kann. Bei Formularen und Berichten
klappt das ja wunderbar nur bei Abfragen komm ich nicht klar.

Dank schon mal für eure Antwort.

Gruß

Jens

[Karl Donaubauer]

Post by Jens Wiedmann
kann man eigentlich den SQL String einer Abfrage irgendwie per VBA
ändern? ....

Currentdb.QueryDefs("DeineAbfrage").SQL = "SELECT..."

s.a. <F1> zur SQL-Eigenschaft

[Jens Wiedmann]

Hallo Karl

Post by Karl Donaubauer
Currentdb.QueryDefs("DeineAbfrage").SQL = "SELECT..."
s.a. <F1> zur SQL-Eigenschaft

Wunderbar, genau das hab ich gesucht. Irgendwie zeigt mir meine oh das nicht
an, aber es gibt ja noch das Internet :-)
Danke für die Hilfe.

Gruß Jens

[Gunter Avenius]

Hallo Jens,

Post by Jens Wiedmann
kann man eigentlich den SQL String einer Abfrage irgendwie per VBA ändern?

CurrentDb.QueryDefs("DeineAbfrage").SQL= "SELECT * From DeineTabelle"

[Henry Habermacher [MVP Access]]

Hallo Jens

Post by Jens Wiedmann
Der Hintergrund ist der, dass ich einige Abfragen verändern muss und
damit ich nicht jede Abfrage per Hand öffnen muss um die Änderungen
vorzunehmen, hab ich mir überlegt ob man irgendwie per Script alle
Abfragen öffnen, verändern und dann wieder schließen kann. Bei
Formularen und Berichten klappt das ja wunderbar nur bei Abfragen
komm ich nicht klar.

z.B. so: (ungetestet)

Dim db as Database
dim qdf as Querydef
set db = currentdb
for each qdf in db.Querydefs
if left(qdf.Name, 4) <> "~sq_" then
qdf.SQL = replace(qdf.SQL, "DeineTabelle", "DeineTabelle2")
end if
Next
set qdf = nothing
set db = nothing

Und schon ist in allen Abfragen DeineTabelle in DeineTabelle2 geändert.

Anmerkung: die Abfragen "~sq_" musst Du ausschliessen. Diese erstellt Access
aufgrund der ControlSource und Recordsource von anderen Objekten (Forms,
Reports, Controls, ...) selber. Diese darfst Du nicht ändern, sonst kann es
zu unvorhersehbarem Verhalten kommen.

Gruss
Henry

[Jens Wiedmann]

Hallo Henry

Post by Henry Habermacher [MVP Access]
z.B. so: (ungetestet)
Dim db as Database
dim qdf as Querydef
set db = currentdb
for each qdf in db.Querydefs
if left(qdf.Name, 4) <> "~sq_" then
qdf.SQL = replace(qdf.SQL, "DeineTabelle", "DeineTabelle2")
end if
Next
set qdf = nothing
set db = nothing

Kann ich leider nicht anwenden, da meine Abfragen unterschiedlich geändert
werden, ich kann nicht alle zusammenfassen. Trotzdem danke werde es mir für
die Zukunft mal versuchen zu merken :-)

Gruß

Jens

[Ekkehard Böhme]

Hallo Henry,

Post by Henry Habermacher [MVP Access]
Dim db as Database
dim qdf as Querydef
set db = currentdb
for each qdf in db.Querydefs
if left(qdf.Name, 4) <> "~sq_" then
qdf.SQL = replace(qdf.SQL, "DeineTabelle", "DeineTabelle2")

Wird die Abfrage da auch gleich kompiliert ?

Post by Henry Habermacher [MVP Access]
end if
Next
set qdf = nothing
set db = nothing

mfg
ekkehard böhme

[Henry Habermacher [MVP Access]]

Hallo Ekehard

Post by Ekkehard Böhme

Post by Henry Habermacher [MVP Access]
qdf.SQL = replace(qdf.SQL, "DeineTabelle", "DeineTabelle2")

Wird die Abfrage da auch gleich kompiliert ?

Kaum, dazu musst Du diese zuerst mal öffnen, danach sollte sie jedoch dann
kompiliert sein. Das ist IMNSHO jedoch nicht notwendig, das manuell zu
machen. Das läuft einmal automatisch beim ersten Öffnen der Abfrage und ist
bei heutigen Maschinen kaum mehr spürbar.

Gruss
Henry

[Ekkehard Böhme]

Hallo Henry,

Post by Henry Habermacher [MVP Access]
Hallo Ekehard

Post by Ekkehard Böhme

Post by Henry Habermacher [MVP Access]
qdf.SQL = replace(qdf.SQL, "DeineTabelle", "DeineTabelle2")

Wird die Abfrage da auch gleich kompiliert ?

Kaum, dazu musst Du diese zuerst mal öffnen, danach sollte sie jedoch dann
kompiliert sein. Das ist IMNSHO jedoch nicht notwendig, das manuell zu
machen. Das läuft einmal automatisch beim ersten Öffnen der Abfrage und
ist bei heutigen Maschinen kaum mehr spürbar.

O.K. Meine Frage war dahingehend gestellt, das gespeicherte Abfragen ja
schneller sind als zugewiesene (RowSource/Kombifeld) SQL-Strings.
Dachte also statt SQL-String die neue Abfrage zuweisen.
Habe bei mir den Fall, das die Abfrage als Kriterium einen Verweis auf ein
Formcontrol hat. Wenn ich die Abfrage für verschiedene Formulare benutzen
will müsste ich sie ändern (wie oben beschrieben). Ich benutze jetzt
"Kombi.Rowsource = SQL-String", was deutlich länger dauert (Tabelle hat 1,2
Mio. Datensätze).
Ich werd das aber Anfang der Woche mal ausprobieren.
Danke erstmal für Deine Erläuterungen.
mfg
ekkehard böhme

[Klaus Oberdalhoff [MVP]]

Hi,

Post by Ekkehard Böhme
O.K. Meine Frage war dahingehend gestellt, das gespeicherte Abfragen
ja schneller sind als zugewiesene (RowSource/Kombifeld) SQL-Strings.
Dachte also statt SQL-String die neue Abfrage zuweisen.
Habe bei mir den Fall, das die Abfrage als Kriterium einen Verweis
auf ein Formcontrol hat. Wenn ich die Abfrage für verschiedene
Formulare benutzen will müsste ich sie ändern (wie oben beschrieben).
Ich benutze jetzt "Kombi.Rowsource = SQL-String", was deutlich länger
dauert (Tabelle hat 1,2 Mio. Datensätze).

und einfach die ganze Abfrage löschen und neu erzeugen ?
Das müsste doch dann schneller sein, oder ?
(ich mach das manchmal so) A la

Function CreateQuery(strSQL As String, Optional Queryname As String =
"qrySorting") As Boolean

Dim dbs As Database
Dim qdf As QueryDef

On Error GoTo CreateQuery_Error

Set dbs = CurrentDb
If ObjectExists("Query", Queryname) Then
DoCmd.DeleteObject acQuery, Queryname
End If
Set qdf = dbs.CreateQueryDef(Queryname, strSQL)

DoEvents

CreateQuery = True
On Error GoTo 0
Exit Function

CreateQuery_Error:

' MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
CreateQuery of Modul DataFunctions"
CreateQuery = False

End Function
--
mfg

Klaus Oberdalhoff(Access MVP) ***@gmx.de

Ich beantworte keine NG-Fragen und -Nachfragen per Mail!
Newbie-Info: http://www.doerbandt.de/Access/Newbie.htm
KnowHow-mdb: http://www.freeaccess.de
Sofern Access 200x bitte beachten:
http://www.freeaccess.de/MS-Access-Artikel.asp?ID=99

[Henry Habermacher [MVP Access]]

Hallo Ekkehard

Post by Ekkehard Böhme
O.K. Meine Frage war dahingehend gestellt, das gespeicherte Abfragen
ja schneller sind als zugewiesene (RowSource/Kombifeld) SQL-Strings.

Wie kommst Du darauf? Sobald die zugewiesene kompiliert ist (was auch diese
nach dem ersten Aufruf ist), ist diese gleich schnell, wie eine
gespeicherte. Diese wird ja dann auch in eine ~sq_ Abfrage abgelegt.

Post by Ekkehard Böhme
Habe bei mir den Fall, das die Abfrage als Kriterium einen Verweis
auf ein Formcontrol hat. Wenn ich die Abfrage für verschiedene
Formulare benutzen will müsste ich sie ändern (wie oben beschrieben).
Ich benutze jetzt "Kombi.Rowsource = SQL-String", was deutlich länger
dauert (Tabelle hat 1,2 Mio. Datensätze).

Das hängt vermutlich eher mit dem SQL String zusammen. Zudem ist es wohl
nicht sinnvoll 1.2 Mio Datensätze in einer Combo darzustellen (geht nämlich
gar nicht).

Gruss
Henry

[Peter Doering]

Hallo Henry,

Post by Henry Habermacher [MVP Access]

Post by Ekkehard Böhme
Habe bei mir den Fall, das die Abfrage als Kriterium einen Verweis
auf ein Formcontrol hat. Wenn ich die Abfrage für verschiedene
Formulare benutzen will müsste ich sie ändern (wie oben beschrieben).
Ich benutze jetzt "Kombi.Rowsource = SQL-String", was deutlich länger
dauert (Tabelle hat 1,2 Mio. Datensätze).

Das hängt vermutlich eher mit dem SQL String zusammen. Zudem ist es wohl
nicht sinnvoll 1.2 Mio Datensätze in einer Combo darzustellen (geht nämlich
gar nicht).

Jo, aber da gibt's noch den Workaround von Allen Browne ;-)

http://allenbrowne.com/ser-32.html

Gruss - Peter

[Ekkehard Böhme]

Hallo Peter,

Post by Peter Doering

Post by Henry Habermacher [MVP Access]

Post by Ekkehard Böhme
Habe bei mir den Fall, das die Abfrage als Kriterium einen Verweis
auf ein Formcontrol hat. Wenn ich die Abfrage für verschiedene
Formulare benutzen will müsste ich sie ändern (wie oben beschrieben).
Ich benutze jetzt "Kombi.Rowsource = SQL-String", was deutlich länger
dauert (Tabelle hat 1,2 Mio. Datensätze).

Das hängt vermutlich eher mit dem SQL String zusammen. Zudem ist es wohl
nicht sinnvoll 1.2 Mio Datensätze in einer Combo darzustellen (geht nämlich
gar nicht).

Jo, aber da gibt's noch den Workaround von Allen Browne ;-)
http://allenbrowne.com/ser-32.html

Brauch ich wohl nicht, da Missverständnis (siehe Antwort an Henry).
Trotzdem Danke
mfg
ekkehard böhme

[Ekkehard Böhme]

Hallo Henry,

Post by Henry Habermacher [MVP Access]

Post by Ekkehard Böhme
O.K. Meine Frage war dahingehend gestellt, das gespeicherte Abfragen
ja schneller sind als zugewiesene (RowSource/Kombifeld) SQL-Strings.

Wie kommst Du darauf? Sobald die zugewiesene kompiliert ist (was auch
diese nach dem ersten Aufruf ist), ist diese gleich schnell, wie eine
gespeicherte. Diese wird ja dann auch in eine ~sq_ Abfrage abgelegt.

Versteh ich jetzt nicht. Die Abfrage/Zuweisung an Kombi wird doch immer nur
einmal (das erstemal?) aufgerufen.
Ich frag jetzt mal anders.
Ich habe also ein, von einem anderen Feld abhängiges, Kombifeld.
Nach einer Aktualisierung des anderen Feldes hab' ich doch die folgenden
Möglichkeiten die Rowsource des Kombis zuzuweisen.
1.
Me!MeinKombi.Rowsource = qry_MeineAbfrage (gespeichert: SELECT * FROM
MeineTabelle WHERE einFeld=[Forms]![MeinForm]![anderesFeld];)
Das geht ganz flott.

2.
Me!MeinKombi.Rowsource = SELECT * FROM MeineTabelle WHERE
einFeld=Me!anderesFeld;
Das dauert deutlich länger.

3.
qdf.SQL = SELECT * FROM MeineTabelle WHERE einFeld=Me!anderesFeld;
Me!MeinKombi.Rowsource = qdf
Das werde ich nachher mal testen.

Post by Henry Habermacher [MVP Access]

Post by Ekkehard Böhme
Habe bei mir den Fall, das die Abfrage als Kriterium einen Verweis
auf ein Formcontrol hat. Wenn ich die Abfrage für verschiedene
Formulare benutzen will müsste ich sie ändern (wie oben beschrieben).
Ich benutze jetzt "Kombi.Rowsource = SQL-String", was deutlich länger
dauert (Tabelle hat 1,2 Mio. Datensätze).

Das hängt vermutlich eher mit dem SQL String zusammen. Zudem ist es wohl
nicht sinnvoll 1.2 Mio Datensätze in einer Combo darzustellen (geht
nämlich gar nicht).

Da hast Du mich wohl falsch verstanden. Nicht die Abfrage (Kombi) stellt
soviele DS dar, sondern die dahinterstehende Tabelle.

Dank erstmal.
mfg
ekkehard böhme

[Peter Doering]

Hallo,

Post by Ekkehard Böhme

Post by Henry Habermacher [MVP Access]

Post by Ekkehard Böhme
O.K. Meine Frage war dahingehend gestellt, das gespeicherte Abfragen
ja schneller sind als zugewiesene (RowSource/Kombifeld) SQL-Strings.

Wie kommst Du darauf? Sobald die zugewiesene kompiliert ist (was auch
diese nach dem ersten Aufruf ist), ist diese gleich schnell, wie eine
gespeicherte. Diese wird ja dann auch in eine ~sq_ Abfrage abgelegt.

Versteh ich jetzt nicht. Die Abfrage/Zuweisung an Kombi wird doch immer nur
einmal (das erstemal?) aufgerufen.

Auf die Gefahr, dass das im Thread schon diskutiert worden ist: Du kannst
in Kombi_Enter jedesmal die Rowsource setzen.

Post by Ekkehard Böhme
Ich frag jetzt mal anders.
Ich habe also ein, von einem anderen Feld abhängiges, Kombifeld.
Nach einer Aktualisierung des anderen Feldes hab' ich doch die folgenden
Möglichkeiten die Rowsource des Kombis zuzuweisen.
1.
Me!MeinKombi.Rowsource = qry_MeineAbfrage (gespeichert: SELECT * FROM
MeineTabelle WHERE einFeld=[Forms]![MeinForm]![anderesFeld];)
Das geht ganz flott.

ok.

Post by Ekkehard Böhme
2.
Me!MeinKombi.Rowsource = SELECT * FROM MeineTabelle WHERE
einFeld=Me!anderesFeld;

Die Syntax hast du stark vereinfacht (so dass es nie laufen wuerde), aber
ich unterstelle mal, dass du's im Original richtig machst. Trotzdem
vorsichtshalber:

Me!MeinKombi.Rowsource = "SELECT * FROM MeineTabelle " & _
"WHERE einFeld= " & Me!anderesFeld & ";"

Post by Ekkehard Böhme
Das dauert deutlich länger.

Du weist eine /neue/ Abfrage zu, deren Ausfuehrungsplan erst erstellt
werden muss (was normalerweise beim Speichern der Abfrage passiert),
deshalb dauert's laenger.

Post by Ekkehard Böhme
3.
qdf.SQL = SELECT * FROM MeineTabelle WHERE einFeld=Me!anderesFeld;
Me!MeinKombi.Rowsource = qdf
Das werde ich nachher mal testen.

Me!MeinKombi.RowSource = qdf.SQL

Rest siehe 2.

Post by Ekkehard Böhme

Post by Henry Habermacher [MVP Access]

Post by Ekkehard Böhme
Habe bei mir den Fall, das die Abfrage als Kriterium einen Verweis
auf ein Formcontrol hat. Wenn ich die Abfrage für verschiedene
Formulare benutzen will müsste ich sie ändern (wie oben beschrieben).
Ich benutze jetzt "Kombi.Rowsource = SQL-String", was deutlich länger
dauert (Tabelle hat 1,2 Mio. Datensätze).

Das hängt vermutlich eher mit dem SQL String zusammen. Zudem ist es wohl
nicht sinnvoll 1.2 Mio Datensätze in einer Combo darzustellen (geht
nämlich gar nicht).

Da hast Du mich wohl falsch verstanden. Nicht die Abfrage (Kombi) stellt
soviele DS dar, sondern die dahinterstehende Tabelle.

Trotzdem, wenn die Bedingungen stimmen, koennen theoretisch 1.2 Mio DS
zurueckgegeben werden, oder?

Ansonsten wuerde ich noch eine 4. Moeglichkeit in Erwaegung ziehen. Da Jet
bei der Ausfuehrung wegen der Bedingung auf das Formular zugreifen muss,
ist es langsamer als ohne. Probier mal, den Wert des Feldes in eine Tabelle
zu schreiben und per Join zu verknuepfen. Das muesste etwas schneller sein.
Beispiel

CurrentDB.Execute "INSERT INTO HilfsTab ( Feld1 ) VALUES (" & _
Me!anderesFeld.Value & ");", dbFailOnError

und dann in der Abfrage:

SELECT *
FROM MeineTabelle AS T1
INNER JOIN HilfsTab AS T2 ON T1.einFeld = T2.Feld1;

Die Rowsource der Kombi enthaelt dann nur den Namen der Abfrage.

HilfsTab musst einen eindeutigen Schluessel auf [Feld1] haben,
MeineTabelle.einFeld muss aus Performancegruenden ebenfalls einen (nicht
eindeutigen) haben. Vor dem Fuellen von HilfsTab musst die Tabelle leeren:

CurrentDB.Execute "DELETE FROM HilfsTab;", dbFailOnError

Gruss - Peter

[Ekkehard Böhme]

Post by Jens Wiedmann
Hallo,

Post by Ekkehard Böhme

Post by Henry Habermacher [MVP Access]

Post by Ekkehard Böhme
O.K. Meine Frage war dahingehend gestellt, das gespeicherte Abfragen
ja schneller sind als zugewiesene (RowSource/Kombifeld) SQL-Strings.

Wie kommst Du darauf? Sobald die zugewiesene kompiliert ist (was auch
diese nach dem ersten Aufruf ist), ist diese gleich schnell, wie eine
gespeicherte. Diese wird ja dann auch in eine ~sq_ Abfrage abgelegt.

Versteh ich jetzt nicht. Die Abfrage/Zuweisung an Kombi wird doch immer nur
einmal (das erstemal?) aufgerufen.

Auf die Gefahr, dass das im Thread schon diskutiert worden ist: Du kannst
in Kombi_Enter jedesmal die Rowsource setzen.

Ja, mach ich ja auch (jeweils abhängig von der Eingabe in ein anderes Feld.

Post by Jens Wiedmann

Post by Ekkehard Böhme
Ich frag jetzt mal anders.
Ich habe also ein, von einem anderen Feld abhängiges, Kombifeld.
Nach einer Aktualisierung des anderen Feldes hab' ich doch die folgenden
Möglichkeiten die Rowsource des Kombis zuzuweisen.
1.
Me!MeinKombi.Rowsource = qry_MeineAbfrage (gespeichert: SELECT * FROM
MeineTabelle WHERE einFeld=[Forms]![MeinForm]![anderesFeld];)
Das geht ganz flott.

ok.

Post by Ekkehard Böhme
2.
Me!MeinKombi.Rowsource = SELECT * FROM MeineTabelle WHERE
einFeld=Me!anderesFeld;

Die Syntax hast du stark vereinfacht (so dass es nie laufen wuerde), aber
ich unterstelle mal, dass du's im Original richtig machst. Trotzdem

Ja, ist im Original alles richtig.

Post by Jens Wiedmann
Me!MeinKombi.Rowsource = "SELECT * FROM MeineTabelle " & _
"WHERE einFeld= " & Me!anderesFeld & ";"

Post by Ekkehard Böhme
Das dauert deutlich länger.

Du weist eine /neue/ Abfrage zu, deren Ausfuehrungsplan erst erstellt
werden muss (was normalerweise beim Speichern der Abfrage passiert),
deshalb dauert's laenger.

Ja, weiss ich. Deshalb ja meine Frage wie sich 3. verhält. Was ich ja, wie
gesagt erst noch ausprobieren muss.

Post by Jens Wiedmann

Post by Ekkehard Böhme
3.
qdf.SQL = SELECT * FROM MeineTabelle WHERE einFeld=Me!anderesFeld;
Me!MeinKombi.Rowsource = qdf
Das werde ich nachher mal testen.

Me!MeinKombi.RowSource = qdf.SQL
Rest siehe 2.

Post by Ekkehard Böhme

Post by Henry Habermacher [MVP Access]

Post by Ekkehard Böhme
Habe bei mir den Fall, das die Abfrage als Kriterium einen Verweis
auf ein Formcontrol hat. Wenn ich die Abfrage für verschiedene
Formulare benutzen will müsste ich sie ändern (wie oben beschrieben).
Ich benutze jetzt "Kombi.Rowsource = SQL-String", was deutlich länger
dauert (Tabelle hat 1,2 Mio. Datensätze).

Das hängt vermutlich eher mit dem SQL String zusammen. Zudem ist es wohl
nicht sinnvoll 1.2 Mio Datensätze in einer Combo darzustellen (geht
nämlich gar nicht).

Da hast Du mich wohl falsch verstanden. Nicht die Abfrage (Kombi) stellt
soviele DS dar, sondern die dahinterstehende Tabelle.

Trotzdem, wenn die Bedingungen stimmen, koennen theoretisch 1.2 Mio DS
zurueckgegeben werden, oder?

Nee, dann müsste ja in allen 1,2Mio. DS der Wert des Kriterium gleich sein.
Die Abfrage filtert immer nur nach einem Feld (Wert).

Post by Jens Wiedmann
Ansonsten wuerde ich noch eine 4. Moeglichkeit in Erwaegung ziehen. Da Jet
bei der Ausfuehrung wegen der Bedingung auf das Formular zugreifen muss,
ist es langsamer als ohne. Probier mal, den Wert des Feldes in eine Tabelle
zu schreiben und per Join zu verknuepfen. Das muesste etwas schneller sein.
Beispiel
CurrentDB.Execute "INSERT INTO HilfsTab ( Feld1 ) VALUES (" & _
Me!anderesFeld.Value & ");", dbFailOnError
SELECT *
FROM MeineTabelle AS T1
INNER JOIN HilfsTab AS T2 ON T1.einFeld = T2.Feld1;
Die Rowsource der Kombi enthaelt dann nur den Namen der Abfrage.

Probier ich auch noch aus.

Post by Jens Wiedmann
HilfsTab musst einen eindeutigen Schluessel auf [Feld1] haben,
MeineTabelle.einFeld muss aus Performancegruenden ebenfalls einen (nicht
CurrentDB.Execute "DELETE FROM HilfsTab;", dbFailOnError

Danke soweit.
mfg
ekkehard böhme

[Josef Poetzl]

Hallo!

Post by Ekkehard Böhme

Post by Henry Habermacher [MVP Access]

Post by Ekkehard Böhme
O.K. Meine Frage war dahingehend gestellt, das gespeicherte Abfragen
ja schneller sind als zugewiesene (RowSource/Kombifeld) SQL-Strings.

Wie kommst Du darauf? Sobald die zugewiesene kompiliert ist (was auch
diese nach dem ersten Aufruf ist), ist diese gleich schnell, wie eine
gespeicherte. Diese wird ja dann auch in eine ~sq_ Abfrage abgelegt.

Versteh ich jetzt nicht. Die Abfrage/Zuweisung an Kombi wird doch immer nur
einmal (das erstemal?) aufgerufen.
Ich frag jetzt mal anders.
Ich habe also ein, von einem anderen Feld abhängiges, Kombifeld.
Nach einer Aktualisierung des anderen Feldes hab' ich doch die folgenden
Möglichkeiten die Rowsource des Kombis zuzuweisen.
1.
Me!MeinKombi.Rowsource = qry_MeineAbfrage (gespeichert: SELECT * FROM
MeineTabelle WHERE einFeld=[Forms]![MeinForm]![anderesFeld];)
Das geht ganz flott.
2.
Me!MeinKombi.Rowsource = SELECT * FROM MeineTabelle WHERE
einFeld=Me!anderesFeld;
Das dauert deutlich länger.
3.
qdf.SQL = SELECT * FROM MeineTabelle WHERE einFeld=Me!anderesFeld;
Me!MeinKombi.Rowsource = qdf
Das werde ich nachher mal testen.

Zuerst einmal etwas Grundsätzliches:
- Ein Index auf das Feld "einFeld" ist eingerichtet?
- Verwendet statt "SELECT * ..." lieber "SELECT FeldA, FeldB, ..."

Da Du schreibst, dass Pkt. 2 deutlich länger als Pkt. 1 dauert: ich
habe die Erfahrung gemacht, dass gespeicherte Abfragen erst bei
komplexeren SQL-Anweisungen (mehrere joins udgl.) einen erkennbaren
Geschwindigkeitsvorteil bringen.

Du könntest einmal mit dem Access-Showplan überprüfen, ob die Abfrage
/Rushmore-optimiert/ ist.

BTW: Verwendest Du überhaupt ein mdb-Backend?

mfg
Josef

[Ekkehard Böhme]

Hallo Josef,

Post by Josef Poetzl
Hallo!

Post by Ekkehard Böhme

Post by Henry Habermacher [MVP Access]

Post by Ekkehard Böhme
O.K. Meine Frage war dahingehend gestellt, das gespeicherte Abfragen
ja schneller sind als zugewiesene (RowSource/Kombifeld) SQL-Strings.

Wie kommst Du darauf? Sobald die zugewiesene kompiliert ist (was auch
diese nach dem ersten Aufruf ist), ist diese gleich schnell, wie eine
gespeicherte. Diese wird ja dann auch in eine ~sq_ Abfrage abgelegt.

Versteh ich jetzt nicht. Die Abfrage/Zuweisung an Kombi wird doch immer nur
einmal (das erstemal?) aufgerufen.
Ich frag jetzt mal anders.
Ich habe also ein, von einem anderen Feld abhängiges, Kombifeld.
Nach einer Aktualisierung des anderen Feldes hab' ich doch die folgenden
Möglichkeiten die Rowsource des Kombis zuzuweisen.
1.
Me!MeinKombi.Rowsource = qry_MeineAbfrage (gespeichert: SELECT * FROM
MeineTabelle WHERE einFeld=[Forms]![MeinForm]![anderesFeld];)
Das geht ganz flott.
2.
Me!MeinKombi.Rowsource = SELECT * FROM MeineTabelle WHERE
einFeld=Me!anderesFeld;
Das dauert deutlich länger.
3.
qdf.SQL = SELECT * FROM MeineTabelle WHERE einFeld=Me!anderesFeld;
Me!MeinKombi.Rowsource = qdf
Das werde ich nachher mal testen.

- Ein Index auf das Feld "einFeld" ist eingerichtet?

Ja.

Post by Josef Poetzl
- Verwendet statt "SELECT * ..." lieber "SELECT FeldA, FeldB, ..."

Mach ich auch so. Hatte es für das Posting etwas verkürzt (siehe auch
Antwort an Peter)

Post by Josef Poetzl
Da Du schreibst, dass Pkt. 2 deutlich länger als Pkt. 1 dauert: ich
habe die Erfahrung gemacht, dass gespeicherte Abfragen erst bei
komplexeren SQL-Anweisungen (mehrere joins udgl.) einen erkennbaren
Geschwindigkeitsvorteil bringen.
Du könntest einmal mit dem Access-Showplan überprüfen, ob die Abfrage
/Rushmore-optimiert/ ist.

Hab' ich mich noch nicht damit beschäftigt. Muss ich erst mal suchen.
Ansonsten ist das eine ganz einfache Auswahlabfrage, die 7 von 10 Feldern
einer Tabelle ausgibt, gefiltert auf ein Feld.
Wüsste nicht, was es da zu optimieren gibt.
Ausserdem ist ja die Abfrage (gespeichert) nicht die Bremse, sondern die
gleiche Auswahl als SQL-String (Me!Kombi.RowSource="SELECT ...)

Post by Josef Poetzl
BTW: Verwendest Du überhaupt ein mdb-Backend?

Jau.

Danke soweit.
mfg
ekkehard böhme

[Josef Poetzl]

Hallo!

Ekkehard Böhme schrieb:
[Abfrage für Zugriff auf Tabelle mit > 1Mio DS optimieren]

Nun habe ich selbst einmal ein wenig getestet.
(Ich testete die Ausführungszeit einer Recordset-Abfrage auf eine Tab
mit 2 Mio. DS - geöffnet als Snapshot; FE/BE-Trennung, FilterFeld mit
Index, zurückgelieferte Anzahl an DS = 3000)

Folgende Varianten kamen unter die Lupe:
A) gespeicherte Abfrage:
SELECT Feld1, Feld2 FROM Tabelle Where FilterFeld=123

B) SQL-Anweisung (SQL-Syntax wie bei Variante A)

C) gespeicherte Abfrage:
SELECT Feld1, Feld2 FROM Tabelle Where FilterFeld=FilterWert()
FilterWert() = VBA-Funktion die den Wert 123 liefert.

D) gespeicherte Abfrage (die von Peter erwähnte Variante)
SELECT Feld1, Feld2 FROM Tabelle INNER JOIN FilterWertTab
ON Tabelle.FilterFeld = FilterWertTab.WertFeld

E) die Variante A, aber direkt über das BE geöffnet
(Abfrage liegt im BE)

F) die Variante B, aber direkt über das BE geöffnet

G) die Variante D, aber direkt über das BE geöffnet
(Abfrage liegt im BE)


Das Ergebnis:
Die Laufzeiten aller Varianten außer D und G lagen Bereich von 20 bis
30 ms. Für D und G wurden mehr als 2 sec benötigt.
Interessant ist, dass es kaum einen Unterschied ausmachte, ob die
verknüpfte Tabelle im FE bzw die Tabelle direkt im BE verwendet wird.

zur Variante von Peter:
Ich hätte nicht vermutet, dass ein INNER JOIN soviel ausmacht.
Und daher habe ich auch einmal outer join versucht. =>
Laufzeit im Bereich 20-30 ms!

Also:
SELECT Feld1, Feld2 FROM Tabelle INNER JOIN FilterWertTab
ON Tabelle.FilterFeld = FilterWertTab.WertFeld
=> > 2000 ms

SELECT Feld1, Feld2 FROM Tabelle RIGHT JOIN FilterWertTab
ON Tabelle.FilterFeld = FilterWertTab.WertFeld
=> ~20 ms

mfg
Josef

[Peter Doering]

Hallo Josef,

Post by Josef Poetzl
[...]
[...]
D) gespeicherte Abfrage (die von Peter erwähnte Variante)
SELECT Feld1, Feld2 FROM Tabelle INNER JOIN FilterWertTab
ON Tabelle.FilterFeld = FilterWertTab.WertFeld
Die Laufzeiten aller Varianten außer D und G lagen Bereich von 20 bis
30 ms. Für D und G wurden mehr als 2 sec benötigt.
Interessant ist, dass es kaum einen Unterschied ausmachte, ob die
verknüpfte Tabelle im FE bzw die Tabelle direkt im BE verwendet wird.
Ich hätte nicht vermutet, dass ein INNER JOIN soviel ausmacht.
Und daher habe ich auch einmal outer join versucht. =>
Laufzeit im Bereich 20-30 ms!

Hast du das jeweils einmalig oder mehrmals hintereinander probiert? Es kann
durchaus sein, dass das erstemal laenger dauert und wenn alles brav im
Speicher liegt, superschnell laeuft.

(Obligatorische Fragen ;-):
- Wieviele Saetze enthaelt FilterWertTab?
- Index auf WertFeld ist vorhanden, oder?

Gruss - Peter

[Josef Poetzl]

Hallo!

Post by Peter Doering

Post by Josef Poetzl
[...]
[...]
D) gespeicherte Abfrage (die von Peter erwähnte Variante)
SELECT Feld1, Feld2 FROM Tabelle INNER JOIN FilterWertTab
ON Tabelle.FilterFeld = FilterWertTab.WertFeld
Die Laufzeiten aller Varianten außer D und G lagen Bereich von 20 bis
30 ms. Für D und G wurden mehr als 2 sec benötigt.
Interessant ist, dass es kaum einen Unterschied ausmachte, ob die
verknüpfte Tabelle im FE bzw die Tabelle direkt im BE verwendet wird.
Ich hätte nicht vermutet, dass ein INNER JOIN soviel ausmacht.
Und daher habe ich auch einmal outer join versucht. =>
Laufzeit im Bereich 20-30 ms!

Hast du das jeweils einmalig oder mehrmals hintereinander probiert? Es kann
durchaus sein, dass das erstemal laenger dauert und wenn alles brav im
Speicher liegt, superschnell laeuft.

Mehrmals hintereinander; Varianten untereinander getauscht; DB
mehrmals komprimiert; unabhängig vom Test ein Recordset geöffnet
gehalten; ... ;-)

Post by Peter Doering
- Wieviele Saetze enthaelt FilterWertTab?

Exakt einen.

Post by Peter Doering
- Index auf WertFeld ist vorhanden, oder?

Aber natürlich. ;-)


Jetzt habe ich es mir einmal genauer im _JET_;-)-Showplan angesehen.
Dabei fand ich etwas, was ich nicht verstehe.

Folgendes tritt bei inner join auf:
- Inputs to Query -
Table 'tabFilterWert'
Database 'F:\Test\testBE.mdb'
Table 'tTestDaten'
Database 'F:\Test\testBE.mdb'
Using index 'fiGruppe'
Having Indexes:
fiGruppe 2097136 entries, 1947 pages, 512 values
which has 1 column, fixed
- End inputs to Query -

01) Inner Join table 'tabFilterWert' to table 'tTestDaten'
using index 'tTestDaten!fiGruppe'
join expression "tabFilterWert.FilterWert=tTestDaten.fiGruppe"


=> Index wird verwendet. So sollte es sein.

Nun start ich diese Abfrage über OpenRecordset(...)
- Inputs to Query -
Table 'tabFilterWert'
Database 'F:\Test\testBE.mdb'
Table 'tTestDaten'
Database 'F:\Test\testBE.mdb'
- End inputs to Query -

01) Sort table 'tabFilterWert'
02) Inner Join table 'tTestDaten' to result of '01)'
using temporary index
join expression "tTestDaten.fiGruppe=tabFilterWert.FilterWert"


Das verstehe ich nicht. Warum wird hier ein "temporary index"
verwendet?
Das Problem bei inner join tritt nur dann auf, wenn ich per Snapshot
öffne - bei Dynaset wird der Index verwendet.
(Ich testete aber mit Snapshot, da ich das Verhalten eines
Kombinationsfeldes nachempfinden wollte. - und Kombifelder verwenden
imo Snapshot, oder?)

db.OpenRecordset("qGeschwindigkeitsTest_INNERJOIN", dbOpenDynaset)
=> Index wird genutzt

db.OpenRecordset("qGeschwindigkeitsTest_INNERJOIN", dbOpenSnapshot)
=> Index wir nicht genutzt

Und Warum?



Bei outer join sieht das Ergebnis im showplan gleich aus - egal ob
über das Datenbankfenster geöffnet oder per OpenRecordset.
Table 'tTestDaten'
Database 'F:\Test\testBE.mdb'
Using index 'fiGruppe'
Having Indexes:
fiGruppe 2097136 entries, 1947 pages, 512 values
which has 1 column, fixed
Table 'tabFilterWert'
Database 'F:\Test\testBE.mdb'
- End inputs to Query -

01) Outer Join table 'tabFilterWert' to table 'tTestDaten'
using index 'tTestDaten!fiGruppe'
join expression "tabFilterWert.FilterWert=tTestDaten.fiGruppe"



mfg
Josef

[Peter Doering]

Hallo Josef,

Post by Josef Poetzl

Post by Josef Poetzl
[...]
D) gespeicherte Abfrage (die von Peter erwähnte Variante)
SELECT Feld1, Feld2 FROM Tabelle INNER JOIN FilterWertTab
ON Tabelle.FilterFeld = FilterWertTab.WertFeld
[...]

Jetzt habe ich es mir einmal genauer im _JET_;-)-Showplan angesehen.
Dabei fand ich etwas, was ich nicht verstehe.
[...]
- Inputs to Query -
[...]
Using index 'fiGruppe'
- End inputs to Query -
=> Index wird verwendet. So sollte es sein.
Nun start ich diese Abfrage über OpenRecordset(...)
- Inputs to Query -
[...]
01) Sort table 'tabFilterWert'
02) Inner Join table 'tTestDaten' to result of '01)'
using temporary index
[...]
Das Problem bei inner join tritt nur dann auf, wenn ich per Snapshot
öffne - bei Dynaset wird der Index verwendet.

Ok, bei Snapshot wird beim Oeffnen immer das komplette Recordset gelesen
(Snapshot eben), waehrend bei Dynaset nur den Index liest, die anderen
Felder koennten sich zur Laufzeit ja noch aendern. Neue DS oder Loeschungen
sind davon natuerlich ausgeschlossen.

Post by Josef Poetzl
(Ich testete aber mit Snapshot, da ich das Verhalten eines
Kombinationsfeldes nachempfinden wollte. - und Kombifelder verwenden
imo Snapshot, oder?)

Gerade getestet, also ...
- mehrspaltiges Kombifeld aufgeklappt,
- die zugrundeliegende Tabelle geaendert,
- danach nochmal aufgeklappt -> in den Spalten wurden die
neuen Werte angezeigt.

==> Kombifeld benutzt Dynaset.

Das Feld habe ich dazwischen weder verlassen noch Requery etc. gemacht.

Post by Josef Poetzl
db.OpenRecordset("qGeschwindigkeitsTest_INNERJOIN", dbOpenDynaset)
=> Index wird genutzt
db.OpenRecordset("qGeschwindigkeitsTest_INNERJOIN", dbOpenSnapshot)
=> Index wir nicht genutzt
Und Warum?

Vermutlich, weil er sowieso alles lesen muss, s.o.

Post by Josef Poetzl
Bei outer join sieht das Ergebnis im showplan gleich aus - egal ob
über das Datenbankfenster geöffnet oder per OpenRecordset.

Dafuer hab ich z. Zt. keine Erklaerung.

Gruss - Peter

[Peter Doering]

Post by Josef Poetzl
[...]
Du könntest einmal mit dem Access-Showplan überprüfen, ob die Abfrage

^^^^^^ Jet
;-)

Link zur Anleitung im DL-Bereich der http://www.dbdev.de/org

Gruss - Peter

[Peter Doering]

Post by Peter Doering
Link zur Anleitung im DL-Bereich der http://www.dbdev.de/org

Und dieser Link funktioniert sogar:
http://www.dbdev.org
;-)

Gruss - Peter

[Ekkehard Böhme]

Post by Henry Habermacher [MVP Access]

Post by Peter Doering
Link zur Anleitung im DL-Bereich der http://www.dbdev.de/org

http://www.dbdev.org
;-)
Gruss - Peter

Danke; schau ich mir an.

[Henry Habermacher [MVP Access]]

Hallo Josef

Post by Josef Poetzl
- Ein Index auf das Feld "einFeld" ist eingerichtet?
- Verwendet statt "SELECT * ..." lieber "SELECT FeldA, FeldB, ..."

Jep, sehr wichtiger Hinweis. Wird ja wohl nur eine beschränkte Anzahl von
Feldern im Combi gebraucht.

Post by Josef Poetzl
Da Du schreibst, dass Pkt. 2 deutlich länger als Pkt. 1 dauert: ich
habe die Erfahrung gemacht, dass gespeicherte Abfragen erst bei
komplexeren SQL-Anweisungen (mehrere joins udgl.) einen erkennbaren
Geschwindigkeitsvorteil bringen.
Du könntest einmal mit dem Access-Showplan überprüfen, ob die Abfrage
/Rushmore-optimiert/ ist.

Vielleicht noch einen Hinweis, bevor er lange sucht.
Eine Erläuterung (Whitepaper) dazu gibt's im Download Bereich des dbdev.org
"JetShowPlan zur Performance Untersuchung und Verbesserung"

Gruss
Henry

[Henry Habermacher [MVP Access]]

Haloo Ekkehard

Post by Ekkehard Böhme
Me!MeinKombi.Rowsource = qry_MeineAbfrage (gespeichert: SELECT * FROM
MeineTabelle WHERE einFeld=[Forms]![MeinForm]![anderesFeld];)
Das geht ganz flott.

Undglücklich. Schneller würde es wohl gehen, wenn Du:

Me!DeinKombi.Rowsource = "SELECT * FROM MeineTabelle WHERE einFeld = '" &
Me!anderesFeld & "'"

Verwenden würdest. Damit ersparst Du dem Parser einen Rückgriff auf das
Formular machen zu müssen. Wer kriegt dann gleich einen fixen Wert und muss
diesen nicht zuerst noch auswerten.

Post by Ekkehard Böhme
2.
Me!MeinKombi.Rowsource = SELECT * FROM MeineTabelle WHERE
einFeld=Me!anderesFeld;
Das dauert deutlich länger.

Analog, siehe oben.

Post by Ekkehard Böhme
3.
qdf.SQL = SELECT * FROM MeineTabelle WHERE einFeld=Me!anderesFeld;
Me!MeinKombi.Rowsource = qdf
Das werde ich nachher mal testen.

Auch hier solltest Du gleich den Formularwert übergeben, statt diesen als
Variable zu übergeben, zudem wird es mit Me! nicht gehen, Du musst das
Formular explizit verwenden.

Post by Ekkehard Böhme

Post by Henry Habermacher [MVP Access]
Das hängt vermutlich eher mit dem SQL String zusammen. Zudem ist es
wohl nicht sinnvoll 1.2 Mio Datensätze in einer Combo darzustellen
(geht nämlich gar nicht).

Da hast Du mich wohl falsch verstanden. Nicht die Abfrage (Kombi)
stellt soviele DS dar, sondern die dahinterstehende Tabelle.

Und das Feld, über das die Where-Bedingung läuft, ist indexiert?

Gruss
Henry

[Ekkehard Böhme]

Hallo Henry, Peter und Josef,
Dank Euch allen für Eure Bemühungen.
Klappt jetzt auch angemessen schnell, und lag tatsächlich einem fehlenden
Index.
Fragt mich nicht warum mir das nicht aufgefallen ist.
mfg
ekkehard böhme