Office Forum
www.Office-Loesung.de
Access :: Excel :: Outlook :: PowerPoint :: Word :: Office :: Wieder Online ---> provisorisches Office Forum <-
DLookup() versus OpenRecordset
zurück: MDB speichern, obwohl geöffnet weiter: Abfrage greift auf Formular zu, funktioniert aber nur ohne Unbeantwortete Beiträge anzeigen
Neues Thema eröffnen   Neue Antwort erstellen     Status: Tutorial Facebook-Likes Diese Seite Freunden empfehlen
Zu Browser-Favoriten hinzufügen
Autor Nachricht
lothi
c#, .Net


Verfasst am:
04. März 2004, 11:36
Rufname:
Wohnort: Birgisch

DLookup() versus OpenRecordset - DLookup() versus OpenRecordset

Nach oben
       

Hallo

Wenn ich darf hätte ich da noch ein Tip zu den Aggregatsfunktionen in Access.
Die Aggregatsfunktionen wie DLookup(), DSum(), DMax() u.s.w sind bei grossen Datenmengen sehr langsam.
Sämmtliche Aggregatsfunktionen kann man auch mit SQL ausführen die auch wesentlich schneller sind.
Code:
'In ein Modul diesen Code kopieren z.B modFunktionen
'Aufruf der Funktion:
'fcDomWert("FeldinTabelle","Tabelle","Kriterium",DomFunktion)
'Kriterium und DomFunktion sind optional.
'Wird keine der Domfunktionen angegeben wird Standartmässig DLookup genommen.

Option Compare Database
Option Explicit

Public Enum ltDomWert
    ltDLookup = 0
    ltDCount = 1
    ltDMax = 2
    ltDMin = 3
    ltDFirst = 4
    ltDLast = 5
    ltDSum = 6
    ltDAvg = 7
End Enum

Function fcDomWert(Expression As String, Domain As String, _
                   Optional Criteria As String, _
                   Optional ltDomArt As ltDomWert = ltDLookup) As Variant
    Dim strSQL  As String
    Dim rs      As DAO.Recordset
   
    Select Case ltDomArt
      Case 0: strSQL$ = "SELECT " & Expression$ & " FROM " & Domain$
      Case 1: strSQL$ = "SELECT COUNT(" & Expression$ & ") FROM " & Domain$
      Case 2: strSQL$ = "SELECT MAX(" & Expression$ & ") FROM " & Domain$
      Case 3: strSQL$ = "SELECT MIN(" & Expression$ & ") FROM " & Domain$
      Case 4: strSQL$ = "SELECT FIRST(" & Expression$ & ") FROM " & Domain$
      Case 5: strSQL$ = "SELECT LAST(" & Expression$ & ") FROM " & Domain$
      Case 6: strSQL$ = "SELECT SUM(" & Expression$ & ") FROM " & Domain$
      Case 7: strSQL$ = "SELECT AVG(" & Expression$ & ") FROM " & Domain$
    End Select
    If Nz(Criteria$, "") <> "" Then strSQL$ = strSQL$ & " WHERE " & Criteria$
    Set rs = CurrentDb.OpenRecordset(strSQL$, dbOpenForwardOnly)
    If rs.EOF Then
        fcDomWert = Null
      Else
        fcDomWert = rs.Fields(0)
    End If
'    Debug.Print fcDomWert 'Nur zum Testen
    rs.Close
    Set rs = Nothing
End Function
{SUM bei Case 3 in MIN geaendert! s.u. Edit by Willi Wipp}
_________________
Gruss Lothi, der Bastler
Feedback ist die beste Möglichkeit mir zu sagen ob die Antwort geholfen hat!
AC2002, WinXP, Office XP
Willi Wipp
Moderator


Verfasst am:
06. März 2004, 11:59
Rufname:
Wohnort: Raum Wiesbaden


Re: DLookup() versus OpenRecordset - Re: DLookup() versus OpenRecordset

Nach oben
       

Hi Lothi, sehr schoener Ansatz.

{Dieser Beitrag nimmt das Thema aus den unbeantworteten Themen heraus}
borstel
Im Profil kannst Du frei den Rang ändern


Verfasst am:
10. März 2004, 18:49
Rufname:

AW: DLookup() versus OpenRecordset - AW: DLookup() versus OpenRecordset

Nach oben
       

Hallo Lothi,
habe deinen code gleich erfolgreich getestet.
eine tolle lösung, für die ich dich bewundere.
gruß borstel
Willi Wipp
Moderator


Verfasst am:
15. Jun 2004, 18:37
Rufname:
Wohnort: Raum Wiesbaden

Wann macht es Sinn DLookup durch OpenRecordset zu ersetzen ? - Wann macht es Sinn DLookup durch OpenRecordset zu ersetzen ?

Nach oben
       

Ab wann macht es Sinn DLookup durch OpenRecordset zu ersetzen?

Man kann das zwar nicht so Pauschal beantworten,
da es stark von der Strucktur und der "Ordnung" der Daten abhaengt.
Aber ich gebe hier trotzdem Mal ein kleines Beispiel fuer die Performance-Abhaengigkeit:

Als groben Richtwert:
DS-Anzahl < 1000: DLookup ist schneller (Milli- bis Mikro-Sekunden-Bereich)
DS-Anzahl > 1000: Recordset schneller

Beispiel mit "einfachen" Tabellen (DeineTabellexx):
DeineID: Autowert
DeineZahl: Zahl (Long) {=DeineID}
DeinText: Text (50) {"Text " & Format(DeineZahl, "000000")}
DeinMemo: Memo {"Memo " & Format(DeineZahl, "000000")}
Anzahl der DS: 30, 300, 3.000, 30.000, 300.000
Code:
' Menue: Extras -> Verweise Microsoft DAO 3.xx Object Library muss aktiv sein!
Option Compare Database
Option Explicit

Public Enum ltDomWert
    ltDLookup = 0
    ltDCount = 1
    ltDMax = 2
    ltDMin = 3
    ltDFirst = 4
    ltDLast = 5
    ltDSum = 6
    ltDAvg = 7
End Enum

Public Function ZeitMessenDLookup()
    Dim by      As Byte
    Dim lDummy  As Long
    Dim l       As Long
    Dim lMax    As Long
    Dim lTab(4) As Long
    Dim lZahl   As Long
    Dim dOld    As Double
    Dim sSQL    As String
    Dim sTab    As String
    Dim rs      As DAO.Recordset

    lTab&(0) = 30                                               'Anzahl der DS
    lTab&(1) = 300
    lTab&(2) = 3000
    lTab&(3) = 30000
    lTab&(4) = 300000
    lMax& = 100                                           'Anzahl der Zugriffe
    For by = 0 To 4
        sTab$ = "DeineTabelle" & lTab&(by)
        Debug.Print "Tabelle: " & sTab$
        dOld# = Timer
        For l& = 0 To lMax&
            lZahl& = Rnd * lTab&(by)
            If lZahl& = 0 Then lZahl& = 1
            lDummy& = DLookup("DeineZahl", sTab$, "DeineID=" & lZahl&)
        Next l&
        Debug.Print "DLookup: " & Format$(Timer - dOld#, "0.00000")
        dOld# = Timer
        For l& = 0 To lMax&
            lZahl& = Rnd * lTab&(by)
            If lZahl& = 0 Then lZahl& = 1
            sSQL$ = "SELECT DeineZahl FROM " & sTab$ & _
                   " WHERE DeineID = " & lZahl&
            lDummy& = CurrentDb.OpenRecordset(sSQL$, dbOpenForwardOnly)(0)
        Next l&
        Debug.Print "Direkt:  " & Format$(Timer - dOld#, "0.00000")
        dOld# = Timer
        For l& = 0 To lMax&
            lZahl& = Rnd * lTab&(by)
            If lZahl& = 0 Then lZahl& = 1
            lDummy& = fcDomWert("DeineZahl", sTab$, "DeineID=" & lZahl&)
        Next l&
        Debug.Print "DomWert: " & Format$(Timer - dOld#, "0.00000")
        sSQL$ = "SELECT DeineZahl FROM " & sTab$
        Set rs = CurrentDb.OpenRecordset(sSQL$)
        dOld# = Timer
        For l& = 0 To lMax&
            lZahl& = Rnd * lTab&(by)
            If lZahl& = 0 Then lZahl& = 1
            rs.Filter = "DeineID = " & lZahl&
            lDummy& = rs.Fields(0)
        Next l&
        Debug.Print "Open-RS: " & Format$(Timer - dOld#, "0.00000")
        rs.Close
        Set rs = Nothing
    Next by
End Function

Public Function fcDomWert(Expression As String, Domain As String, _
                          Optional Criteria As String, _
                          Optional ltDomArt As ltDomWert = ltDLookup) As Variant
    Dim strSQL  As String
    Dim rs      As DAO.Recordset
   
    Select Case ltDomArt
      Case 0: strSQL$ = "SELECT " & Expression$ & " FROM " & Domain$
      Case 1: strSQL$ = "SELECT COUNT(" & Expression$ & ") FROM " & Domain$
      Case 2: strSQL$ = "SELECT MAX(" & Expression$ & ") FROM " & Domain$
      Case 3: strSQL$ = "SELECT MIN(" & Expression$ & ") FROM " & Domain$
      Case 4: strSQL$ = "SELECT FIRST(" & Expression$ & ") FROM " & Domain$
      Case 5: strSQL$ = "SELECT LAST(" & Expression$ & ") FROM " & Domain$
      Case 6: strSQL$ = "SELECT SUM(" & Expression$ & ") FROM " & Domain$
      Case 7: strSQL$ = "SELECT AVG(" & Expression$ & ") FROM " & Domain$
    End Select
    If Nz(Criteria$, "") <> "" Then strSQL$ = strSQL$ & " WHERE " & Criteria$
    Set rs = CurrentDb.OpenRecordset(strSQL$, dbOpenForwardOnly)
    If rs.EOF Then
        fcDomWert = Null
      Else
        fcDomWert = rs.Fields(0)
    End If
'    Debug.Print fcDomWert 'Nur zum Testen
    rs.Close
    Set rs = Nothing
End Function

'##############################################
'Ausgabe im Debug-Fenster:

Tabelle: DeineTabelle30
DLookup: 0,17141
Direkt:  0,18109
DomWert: 0,18859
Open-RS: 0,00031
Tabelle: DeineTabelle300
DLookup: 0,19172
Direkt:  0,21166
DomWert: 0,20869
Open-RS: -0,00131
Tabelle: DeineTabelle3000
DLookup: 0,43009
Direkt:  0,43269
DomWert: 0,43138
Open-RS: 0,00137
Tabelle: DeineTabelle30000
DLookup: 2,51309
Direkt:  2,50300
DomWert: 2,55469
Open-RS: 0,00069
Tabelle: DeineTabelle300000
DLookup: 30,79366
Direkt:  25,72622
DomWert: 26,96016
Open-RS: 0,00116
{SUM bei Case 3 in MIN geaendert! s.u. Edit by Willi Wipp}
_________________
Eine kurze Rueckmeldung waere nett
SL Willi Wipp

(Anleitung fuer das Anhaengen von Dateien: Klicke links auf [www], Gaeste muessen sich dafuer anmelden)


Zuletzt bearbeitet von Willi Wipp am 13. Mai 2008, 13:03, insgesamt 3-mal bearbeitet
dot
Access-Fortgeschrittener


Verfasst am:
16. Jun 2004, 08:20
Rufname:
Wohnort: Schwabenland

A97 kein ENUM? - A97 kein ENUM?

Nach oben
       

Hiho

Wollte die Funktion in meine DB einbauen, jetzt habe ich aber das Problem, daß A97 kein ENUM kennt.
Gibt es hier ein anderes Schlüsselwort?

Danke

dot
Twisted Evil Twisted Evil Twisted Evil

_________________
Nichts auf der Welt ist so gerecht verteilt wie der Verstand. Denn jedermann ist überzeugt, dass er genug davon habe.
Descartes
Willi Wipp
Moderator


Verfasst am:
16. Jun 2004, 08:35
Rufname:
Wohnort: Raum Wiesbaden

Ohne Enum für die DomArt - Ohne Enum für die DomArt

Nach oben
       

Hi dot,

Ohne ENUM (dient nur zur Anzeige in der Konstantenliste => Auswahl-Erleichterung)
koennte das Ganze fuer A97 z.B. so aussehen
Code:
' Menue: Extras -> Verweise Microsoft DAO 3.xx Object Library muss aktiv sein!
Option Compare Database
Option Explicit

Public Function fcDomWert97(Expression As String, Domain As String, _
                            Optional Criteria As String, _
                            Optional bytDomArt As Byte = 0) As Variant
    Dim strSQL  As String
    Dim rs      As DAO.Recordset
   
    Select Case bytDomArt
      Case 0: strSQL$ = "SELECT " & Expression$ & " FROM " & Domain$
      Case 1: strSQL$ = "SELECT COUNT(" & Expression$ & ") FROM " & Domain$
      Case 2: strSQL$ = "SELECT MAX(" & Expression$ & ") FROM " & Domain$
      Case 3: strSQL$ = "SELECT MIN(" & Expression$ & ") FROM " & Domain$
      Case 4: strSQL$ = "SELECT FIRST(" & Expression$ & ") FROM " & Domain$
      Case 5: strSQL$ = "SELECT LAST(" & Expression$ & ") FROM " & Domain$
      Case 6: strSQL$ = "SELECT SUM(" & Expression$ & ") FROM " & Domain$
      Case 7: strSQL$ = "SELECT AVG(" & Expression$ & ") FROM " & Domain$
    End Select
    If Nz(Criteria$, "") <> "" Then strSQL$ = strSQL$ & " WHERE " & Criteria$
    Set rs = CurrentDb.OpenRecordset(strSQL$, dbOpenForwardOnly)
    If rs.EOF Then
        fcDomWert97 = Null                   'Nach Hinweis von Dot korrigiert!
      Else
        fcDomWert97 = rs.Fields(0)           'Nach Hinweis von Dot korrigiert!
    End If
'    Debug.Print fcDomWert97 'Nur zum Testen
    rs.Close
    Set rs = Nothing
End Function
{SUM bei Case 3 in MIN geaendert! s.u. Edit by Willi Wipp}
_________________
Eine kurze Rueckmeldung waere nett
SL Willi Wipp

(Anleitung fuer das Anhaengen von Dateien: Klicke links auf [www], Gaeste muessen sich dafuer anmelden)


Zuletzt bearbeitet von Willi Wipp am 23. Jan 2006, 13:37, insgesamt 4-mal bearbeitet
dot
Access-Fortgeschrittener


Verfasst am:
16. Jun 2004, 09:08
Rufname:
Wohnort: Schwabenland

AW: DLookup() versus OpenRecordset - AW: DLookup() versus OpenRecordset

Nach oben
       

Danke, jetzt gehts auch in Access97 Very Happy

dot Twisted Evil Twisted Evil Twisted Evil

_________________
Nichts auf der Welt ist so gerecht verteilt wie der Verstand. Denn jedermann ist überzeugt, dass er genug davon habe.
Descartes
Willi Wipp
Moderator


Verfasst am:
14. März 2006, 15:42
Rufname:
Wohnort: Raum Wiesbaden


Re: Ohne Enum für die DomArt (IV) - Re: Ohne Enum für die DomArt (IV)

Nach oben
       

Willi Wipp am 05. Okt 2004 um 16:25 hat folgendes geschrieben:
Nachfragen zum Thema bitte hier DLookup() versus OpenRecordset {Nachgefragt} stellen.

Hi Folks,

aufgrund eines Problemes im Thema spalten in listenfeld addieren habe ich die Funktion noch etwas angepasst.
Code:
' Menue: Extras -> Verweise Microsoft DAO 3.xx Object Library muss aktiv sein!
Option Compare Database
Option Explicit

Public Function fcDomWert97(Expression As String, Domain As String, _
                            Optional Criteria As String, _
                            Optional bytDomArt As Byte = 0) As Variant
    Dim strSQL  As String
    Dim rs      As DAO.Recordset
   
    Select Case bytWert
      Case 0: strSQL$ = "SELECT " & Expression$
      Case 1: strSQL$ = "SELECT COUNT(" & Expression$ & ")"
      Case 2: strSQL$ = "SELECT MAX(" & Expression$ & ")"
      Case 3: strSQL$ = "SELECT MIN(" & Expression$ & ")"
      Case 4: strSQL$ = "SELECT FIRST(" & Expression$ & ")"
      Case 5: strSQL$ = "SELECT LAST(" & Expression$ & ")"
      Case 6: strSQL$ = "SELECT SUM(" & Expression$ & ")"
      Case 7: strSQL$ = "SELECT AVG(" & Expression$ & ")"
    End Select
    strSQL$ = strSQL$ & " FROM (" & Domain$ & ")"
    If Nz(Criteria$, "") <> "" Then strSQL$ = strSQL$ & " WHERE " & Criteria$
    Set rs = CurrentDb.OpenRecordset(strSQL$, dbOpenForwardOnly)
    If rs.EOF Then
        fcDomWert97 = Null                   'Nach Hinweis von Dot korrigiert!
      Else
        fcDomWert97 = rs.Fields(0)           'Nach Hinweis von Dot korrigiert!
    End If
'    Debug.Print fcDomWert97 'Nur zum Testen
    rs.Close
    Set rs = Nothing
End Function
So sollte im Gegensatz zu den Aggregatfunktionen fuer Domaenen
auch die Verwendung von SQL-Code als Domain funktionieren.
Code:
' Menue: Extras -> Verweise Microsoft DAO 3.xx Object Library muss aktiv sein!
Option Compare Database
Option Explicit

Public Enum ltDomWert
    ltDLookup = 0
    ltDCount = 1
    ltDMax = 2
    ltDMin = 3
    ltDFirst = 4
    ltDLast = 5
    ltDSum = 6
    ltDAvg = 7
End Enum

Function fcDomWert(Expression As String, Domain As String, _
                   Optional Criteria As String, _
                   Optional ltDomArt As ltDomWert = ltDLookup) As Variant
    Dim strSQL  As String
    Dim rs      As DAO.Recordset
   
    Select Case ltDomArt
      Case 0: strSQL$ = "SELECT " & Expression$
      Case 1: strSQL$ = "SELECT COUNT(" & Expression$ & ")"
      Case 2: strSQL$ = "SELECT MAX(" & Expression$ & ")"
      Case 3: strSQL$ = "SELECT MIN(" & Expression$ & ")"
      Case 4: strSQL$ = "SELECT FIRST(" & Expression$ & ")"
      Case 5: strSQL$ = "SELECT LAST(" & Expression$ & ")"
      Case 6: strSQL$ = "SELECT SUM(" & Expression$ & ")"
      Case 7: strSQL$ = "SELECT AVG(" & Expression$ & ")"
    End Select
    strSQL$ = strSQL$ & " FROM (" & Domain$ & ")"
    If Nz(Criteria$, "") <> "" Then strSQL$ = strSQL$ & " WHERE " & Criteria$
    Set rs = CurrentDb.OpenRecordset(strSQL$, dbOpenForwardOnly)
    If rs.EOF Then
        fcDomWert = Null
      Else
        fcDomWert = rs.Fields(0)
    End If
'    Debug.Print fcDomWert 'Nur zum Testen
    rs.Close
    Set rs = Nothing
End Function

_________________
Eine kurze Rueckmeldung waere nett
SL Willi Wipp

(Anleitung fuer das Anhaengen von Dateien: Klicke links auf [www], Gaeste muessen sich dafuer anmelden)
Neues Thema eröffnen   Neue Antwort erstellen Alle Zeiten sind
GMT + 1 Stunde

Diese Seite Freunden empfehlen

Seite 1 von 1
Gehe zu:  
Du kannst Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum nicht posten
Du kannst Dateien in diesem Forum herunterladen

Verwandte Themen
Forum / Themen   Antworten   Autor   Aufrufe   Letzter Beitrag 
Keine neuen Beiträge Access Tabellen & Abfragen: db.OpenRecordset 7 goffee 359 02. Sep 2013, 11:15
KlausMz db.OpenRecordset
Keine neuen Beiträge Access Tabellen & Abfragen: Projektplanung mit Access - Zirkularer Bezug bei DLookUp 1 ---markus--- 516 01. Jul 2012, 14:43
Marmeladenglas Projektplanung mit Access - Zirkularer Bezug bei DLookUp
Keine neuen Beiträge Access Tabellen & Abfragen: Dlookup gibt keinen Wert zurück 31 licherman. 1333 19. Jan 2012, 11:44
Gast Dlookup gibt keinen Wert zurück
Keine neuen Beiträge Access Tabellen & Abfragen: DLOOKUP funktioniert nicht mit deutscher Ländereinstellung 5 Smartie256 300 04. Jun 2010, 10:50
Smartie256 DLOOKUP funktioniert nicht mit deutscher Ländereinstellung
Keine neuen Beiträge Access Formulare: DLookUp "Textfeld Steuerelementinhalt" Inhalt aus 11 ottelo 2721 08. Dez 2009, 19:41
KlausMz DLookUp "Textfeld Steuerelementinhalt" Inhalt aus
Keine neuen Beiträge Access Tabellen & Abfragen: parameterabfrage mit Dlookup? 0 Yak 606 13. Jan 2009, 15:39
Yak parameterabfrage mit Dlookup?
Keine neuen Beiträge Access Tabellen & Abfragen: Ausgabe von Abfragewert in Textfeld mit DLookUp 4 Onkel Uli 2667 27. Dez 2008, 00:02
Onkel Uli Ausgabe von Abfragewert in Textfeld mit DLookUp
Keine neuen Beiträge Access Tabellen & Abfragen: Verknüpfung von 2 Kombinationsfeldern mit Dlookup 2 Boulardier 510 22. Nov 2007, 15:09
Nouba Verknüpfung von 2 Kombinationsfeldern mit Dlookup
Keine neuen Beiträge Access Formulare: dlookup - 2 Felder aktualisieren, aber wie? 1 Lemming1 711 23. Jul 2007, 15:49
Lemming1 dlookup - 2 Felder aktualisieren, aber wie?
Keine neuen Beiträge Access Programmierung / VBA: DLookup und Ufo's 2 krokodog 501 21. Nov 2006, 16:20
krokodog DLookup und Ufo's
Keine neuen Beiträge Access Programmierung / VBA: DLookup bringt bei nichtübereinstimmung Fehlermeldung wie... 3 schwarzeneber 700 24. Okt 2006, 08:03
schwarzeneber DLookup bringt bei nichtübereinstimmung Fehlermeldung wie...
Keine neuen Beiträge Access Tabellen & Abfragen: Sortierung einer Tabelle mit "OpenRecordset" 9 Di 1852 24. Okt 2006, 00:48
Nouba Sortierung einer Tabelle mit "OpenRecordset"
 

----> Diese Seite Freunden empfehlen <------ Impressum - Besuchen Sie auch: Microsoft-Excel Diagramme