Office: (Office 2010) Berechnung Arbeitstage dauert sehr lange

Hallo ,

Nutze folgende Funktion um die Netto Arbeitstage in einer Abfrage zu berechnen:

Code:

Aufgerufen habe ich das so:

CalcDays(Datum();[Termin_Ende])


Nun habe ich eine Tabelle mit knapp 100.000 DS . Die Abfrage dauert so ca 20 Sek. dann ist das Ergebnis da.
Aber der Export der Abfrage nach Excel dauert ewig, so ca. 30 MIn

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "DataSource", "C:Test.xlsb", , "Abfrage1"

Es liegt aber an den Spalten , wo über die Funktion die Nettoarbeitstage berechnet werden.
Mache ich die paar Spalten raus, ist der Export ziemlich schnell.

Gibt es zu so einer Funktion noch eine Alternative, die schneller gehen würde ?

Danke

Kai

:)

 

Probier mal, ob folgendes schneller ist, und ob das gleiche Ergebnis rauskommt:
Code:

 

Ja, die gibt es.

Du kennst vielleicht einen Kalender, gerne aus Papier, wo man einfach daraufschaut und auf einen Blick weiß, wo da Wochenenden und Feiertage u.a. liegen. So ein Kalender kommt mit einer einmaligen Berechnung aus und ist dann fixiert, so dass man einfach nachsehen kann. Üblicherweise ändert sich ein Kalender in den überschaubaren Zeiträumen auch nicht, so dass man nicht wirklich die Notwendigkeit einer ständigen Neuberechnung hat.

Gleiches kann man mit einer Kalendertabelle in einer Abfrage umsetzen.
Mit dem vorhandenen Entwicklergeschick wird ein Nachschlagen in einer geplanten Tabelle deutlich schneller laufen als ein jeweiliges Neuberechnen jedes Einzelwertes, zumal jenes noch per Schleife und in VBA (für SQL erst einmal ein externes Objekt) passiert.

Code:

 

\@Kai_W
normalerweise berücksichtigt man ja auch Feiertage.
Das wäre dann auch der Haupteinsatzzweck einer Kalendertabelle.

Wenn es wirklich nur um SA+SO geht, müsste die Berechnung per Funktion auch jeder Kalendertabelle überlegen sein.

readonly hat ja schon einen recht performanten Weg gezeigt.
Der ließe sich sicher noch etwas verbessern -
allein schon die Übergabe byRef (aus einem Objekt heraus) ist ungünstig.
Die For Schleife könnte man auch ersetzen und schon aber der Übergabe Long statt Date verwenden.
Aber da geht es dann schon um den Millisekunden Bereich.

Ist aus meiner Sicht aber verschenkte Zeit,
da das für die Praxis kaum relevant sein dürfte.

 

Echt? Die 100k-fache Wiederholung einer Funktion mit dem "uneffektiven" VBA ist nativem SQL mit Indexnutzung überlegen?

Mir fehlt da der Glaube.

 

Bei ByRef hast du recht, wenn die Funktion in einer Abfrage aufgerufen wird, daher in ByVal umgeändert.

Die For-Schleife hat mindestens 0 und maximal 6 Durchläufe. Eine Alternative als Formel, welche die Tage Mo-Fr aus diesen 0 bis maximal 6 Tagen herausrechnet wüsste ich nicht, da der Beginn der Resttage in der Woche verschieden sein kann und auch die Anzahl der Resttage.

Bei Übergabe Long statt Date dürfte die Auswirkung kaum messbar sein, daher Date belassen wegen besserer Übersichtlichkeit.

Statt Datediff zu verwendern, sondern einfach nur zu rechnen (lngEndeNeu - lngStartNeu) hat die Geschwindigkeit auch nochmal erhöht.

--------------------------

Mir ist aber aufgefallen, dass die Funktion in #2 in manchen Fällen (auch wenn die Uhrzeiten in Von- und Bis-Datum gleich sind) falsche Ergebnisse liefert: z. B. Von 01.01.2020 12:00:00 bis 31.12.2020 12:00:00 -> 261 = Falsch … Richtig wäre 262

Ich habe daher in der nachfolgenden Funktion für die Tageberechnung die Uhrzeiten entfernt (Fix(…)):
Code:

 

Wohl mehr das Wissen.
Da hilft wohl nur ein Test.
Jetzt müsstest du nur noch einen effizienten VBA Code schreiben.

 

Nein, MUSS ich NICHT.
Im tiefen Wasser setze ich eher auf Flossen statt auf Spikes.

Grundlagen - SQL ist leicht (0) - Vorspiel

Bei dem "Ergebnis da" solltest Du mal überprüfen, ob Du schon zum letzten Datensatz springen kannst oder ob Du nur erste Ergebniszeilen siehst. Beim Export muss nun mal die Abfrage vollständig ausgeführt werden. Die Differenz von 20 Sekunden auf 30 Minuten nur wegen des Exports an sich ist drastisch zu viel.

 

Klar müsstest du das.
Um das zu Beurteilen musst du von beiden Varianten wissen,
wie viel Aufwand es für den Prozessor bedeutet.
Das weißt du aber weder vom SQL noch vom VBA.
Dir fehlt also völlig die Basis für eine Beurteilung.

 

Danke für die Blumen (von einem ganz und gar Selbstunkritischen vom hohen Berge).

Eine 30-Minuten-Abfrage kannst Du mit nur ein paar VBA-Spielereien in etwas Brauchbares eindampfen?

Dafür erstelle ich mir keine eigene Umgebung, aber bei im Thema Beteiligten gibt es ja schon entsprechende DB's, sogar eine Realumgebung.

 

Also meine Funktion (aus Beitrag #6) braucht für 100.000 Aufrufe im ungünstigsten Fall (mit jeweils 6 Resttagen in der Funktion) ca. 0,26 Sekunden auf meinem System:

Je weniger Resttage ( 5, 4, 3, 2, 1 oder 0 Resttage in der Funktion), desto schneller das Ergebnis:

Die Geschwindigkeit hängt nicht von der Differenz Von- und Bis-Datum ab, sondern von den Resttagen, welche in der Funktion (Beitrag #6) ermittelt werden (min. 0, max. 6 Tage).

-------------------

Zum Vergleich, die Funktion vom TE (aus Beitrag #1) braucht ca. 17 Sekunden (Beispiel mit 61 Tagen Differenz):

Je größer Von- und Bis-Datum differieren, desto langsamer ist die Funktion vom TE (Beitrag #1).

(Gemessen im VBA-Direktbereich)

 

\@readonly,
hab deine Funktion mal getestet.


Gesamtdauer für 100.000 Durchläufe mit wechselndem Datum etwa 65 Millisekunden.

Hab mal den Teil getauscht, der vermutlich die meiste Zeit kostet.
Dann waren es noch im Schnitt 28 Millisekunden.
Aufgerufen habe ich mit Date - Der Aufruf mit Variant kostet noch etwa 10 Millisekunden extra.

Code:

 

\@markusxy

Backslashes solltest du verdoppeln, ansonsten fehlen diese im Beitrag.

Deine Funktion ist zwar schneller, gibt aber falsche Zahlen aus!?

 

Ich nehme aber an, du hast den Fehler bemerkt.
Getestet habe ich es so wie du es jetzt sehen kannst.

 

Hallo @Kai_W,
nachfolgend eine noch schnellere Lösung, die ohne For-Next-Schleife auskommt:
Code: