Wat zou het snelst zijn?

[Interface]

Voor een personeelswerktijdenoverzicht ben ik een bestand aan het bouwen. Nu moet ik uit een tabel een set met gegevens ophalen.

Nu is dit op zich geen probleem, het is voor mij mogelijk om de gegevens uit de tabel te halen, maar ik vraag mij af wat sneller is. Het is namelijk voor de eindgebruiker van belang dat dit snel gaat.

Ik vroeg me daarom af wat sneller zou werken, het bouwen van een SQL commando in combinatie met een ADO verbinding of gewoon door goed gedeclareerde arrays met goed geschreven lussen?

Ik zie hier ook wel eens methodes voorbij komen met een combinatie van filter(join(worksheetfunction.transpose(...))), nu ben ik met de laats genoemde methode niet heel erg ervaren, dus elke hulp is welkom!

Bijgevoegd een bestand met daarin een tabblad tbTime als tabel met daarin data van medewerkers met begin en eindtijden. Daarbij in hetzelfde werkboek een tabblad Resultaat waar een format staat van hoe de gegevens opgehaald moeten worden. Daarbij is er ook nog een form aanwezig om SQL mee te testen, misschien is dit handig voor diegene die voor de SQL-query wil gaan.

Ik hoop dat ik een leuke toepasbare methode voorbij zie komen waar ik nog wat van kan leren!

 

[wampier]

Daar je presentatie relatief statisch lijkt te zijn, is het waarschijnlijk het makkelijkst om de data via procedures te behandelen. Overigens mis je nog een optie en dat is het gebruik van autofilter op de data. Door een filter toe te passen op het weeknummer kun je de te behandelen data eenvoudig verkleinen op een zeer efficiënte manier.

Uiteindelijk moet je toch aan de slag met de dataset om de presentatie te regelen. Voordeel van SQL zou zijn dat je een geordende dataset kunt maken gesorteerd op naam en dag en daarmee kan werken zonder steeds excel aan te spreken. Om daar echt voordeel uit te halen moet je wel een behoorlijk grote lijst hebben in je originele tabel

 

[Interface]

dank wampier

Ik denk dat beide methode hun voor en nadelen hebben.

Hoe bedoel je de autofilter gebruiken op basis van weeknummer? De data set zoals deze nu in [tbTime$] staat, is altijd een volle week (van Ma-Zo) uit een andere database. Deze worden ge�mporteerd met een SELECT *, dus alle kolommen in die tabel. Het weeknummer heb ik dan eigenlijk ook niet nodig en zou ik dus ook kunnen verwijderen (daar ik een isweeknummer functie ingebouwd heb).

Ik had eigenlijk gehoopt op een opzetje zoals ik in mij vraag al had gesteld met de functie(s) filter(join(*)). In ieder geval bedankt!

 

[Interface]

hoe kan dit?

Zojuist twee functies gebouwd, ik vroeg me af wat sneller zou zijn, de ingebouwde Join functie van VBa of zelf een functie schrijven.

Tot mij verbazing is een zelfgeschreven nog sneller ook... Heeft iemand een idee waarom(misschien kan het nog sneller) dit zo is, je zou zeggen dat UDF's langzamer zijn dan build in functies...

Function testJoin()

Dim varArray() As Variant
Dim varTime As Variant
Dim strTemp As String
Dim begin   As Double
Dim eind As Double

begin = Timer

With Sheets(1)
    varTime = .UsedRange
    ReDim varArray(UBound(varTime) - 1)
    For i = 1 To UBound(varTime)
        strTemp = vbNullString
        For j = 1 To 10
            strTemp = strTemp & "|" & varTime(i, j)
        Next j
        varArray(i - 1) = strTemp
    Next i
End With

eind = Timer

testJoin = eind - begin

End Function

Function testJoin2()

Dim varArray() As Variant
Dim varTime As Variant
Dim begin   As Double
Dim eind As Double

begin = Timer

With Sheets(1)
    varTime = .UsedRange
    ReDim varArray(UBound(varTime) - 1)
    For i = 1 To .UsedRange.Rows.Count
        varArray(i - 1) = Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(.Range("a" & i & ":j" & i))), "|")
    Next i
End With

eind = Timer

testJoin2 = eind - begin

End Function

Sub test()

MsgBox testJoin & vbCrLf & testJoin2

End Sub

 

[snb]

sub snb()
 sn = Sheets(1).UsedRange
 
 for j=1 to ubound(sn)
   c01=c01 & vbcr & join(application.index(sn,j),"|")
 next
End Sub

Van deze tekstreeks kun je eenvoudig een array maken met sp=split(mid(c01,2),vbcr)

 

[Interface]

dank SNB!

Heb de functie getest en zie duidelijk wel een voordeel van de index aanspreken op deze manier. Het maakt het namelijk mogelijk om een regel direct uit een 2 dimensionale array te trekken en vervolgens te gebruiken als een één dimensionale array of door te geven aan (in jouw voorbeeld dan) een functie of procedure.

Probleem is dat als ik teveel data in mijn sheet heb staan (ong. 2000 a 3000 regels) dit duurt vervolgens 26 sec ong. om de regels direct in een één dimensionale array over te zetten.

Ik heb het nu zo:

varTime = Sheets(1).UsedRange

ReDim varTemp(UBound(varTime) - 1)

For i = 0 To UBound(varTime) - 1
    varTemp(i) = Join(Application.Index(varTime, i + 1), "|")
Next i

Dit moet toch sneller kunnen?! Het kan natuurlijk zijn dat ik het verkeerd aanpak

Er is een extra voorbeeld geüpload (voor diegene die mee willen denken). In dit voorbeeld is en procedure gebouwd die de data ophaalt van het werkblad "tbTime" en verwerkt tot een bepaalde dataset/format om vervolgens te plaatsen op het werkblad "resultaat".

En ik wil nu wel eens weten wat echt werkelijk de snelste manier is om gegevens te analyseren, en op basis van de analyse een query te maken en deze vervolgens te plaatsen op een ander tabblad!

De code in het werkboek bevat de volgende code:

'---------------------------------------------------------------------------------------
' Module    : modHelpMij
' Author    : Interface
' Date      : 10-1-2012
' Purpose   : ExampleMod
'---------------------------------------------------------------------------------------

Option Explicit

'---------------------------------------------------------------------------------------
' Procedure : getDataSet
' Author    : Interface
' Date      : 10-1-2012
' Purpose   : ExampleSub
'---------------------------------------------------------------------------------------
'
Function getDataSet()
 
Dim varTime             As Variant
Dim varEmployee         As Variant
Dim varPlanning         As Variant
Dim begin               As Double
Dim eind                As Double
Dim varTemp()           As String
Dim varRecord()         As String
Dim i                   As Integer
Dim j                   As Integer
Dim dblTimeTotal        As Double

begin = Timer

varEmployee = Sheets("tbEmployee").UsedRange
varTime = Sheets("tbTime").UsedRange

ReDim varTemp(UBound(varTime) - 1)

For i = 0 To UBound(varTime) - 1
    varTemp(i) = Join(Application.Index(varTime, i + 1), "|")
Next i

ReDim varPlanning(10, UBound(varEmployee) + 1)

For i = 2 To UBound(varEmployee)
    varPlanning(1, i - 1) = varEmployee(i, 1)
    varTime = Filter(varTemp, varEmployee(i, 1) & "|")
    For j = 0 To UBound(varTime)
        varRecord = Split(varTime(j), "|")
        varPlanning(2 + Weekday(DateSerial(varRecord(2), varRecord(1), varRecord(0)), vbMonday), i - 1) = varRecord(6) & "-" & varRecord(7)
    Next j
Next i
For i = 1 To UBound(varPlanning)
    For j = 1 To UBound(varPlanning, 2)
        Sheets(2).Cells(j + 4, i + 4) = varPlanning(i, j)
    Next j
Next i

eind = Timer

MsgBox eind - begin

End Function

 

[snb]

Waarom gebruik je hiervoor geen draaitabel ?

 

[Interface]

draairapport

Snb,

Gisterenavond na het lezen van je reactie, ben ik hard aan de slag gegaan met een draaitabel. Echter lukt het me simpelweg niet om de gegevens zo neer te planten dat ik ze ontvang in het format zoals ik het wil.

Daarbij denk ik dat het e.a.a. niet heel veel versneld, misschien pakt ik het verkeerd aan, en moet ik dan ook dit op een andere manier doen... Al had ik van de Snb goeroe wel een oplossing verwacht natuurlijk

 

[Interface]

Maar SNB ik snap je punt wel, waarom opnieuw het wiel uitvinden....

 

[Mark xl]

Als je tot één statisch rapport wilt komen is SQL altijd sneller, want daar is het voor gemaakt.

staat de informatie op een database?
zo ja, wat voor type database is het? ik kan je wel helpen met TSQL (sql server)

Ik stel voor dat je in een query de data zo klaarzet dat het gemakkelijk in een draaitabel past

bijvoorbeeld: (Microsoft) veldnamen komen overeen met de veldnamen in je tbTime tabblad
[SQL]
SELECT DATEPART(DW,
DATEADD(D,intDay,
DATEADD(M,intMonth-1,
CAST(intYear AS VARCHAR(10))+'-01-01'
))) as WeekDag
/* UREN APART */
,STUFF(RIGHT('0000' + CAST(bTime AS VARCHAR(4)),4),3,0,':') as tBegin
,STUFF(RIGHT('0000' + CAST(eTime AS VARCHAR(4)),4),3,0,':') as tEind
/* OF SAMEN */
,STUFF(RIGHT('0000' + CAST(bTime AS VARCHAR(4)),4),3,0,':') + '-' +
STUFF(RIGHT('0000' + CAST(eTime AS VARCHAR(4)),4),3,0,':') AS Tijd
,Worktime AS Totaal
,Name AS Naam
,Note AS Opmerking
FROM <TABEL?>
[/SQL]

en ook kun je in een keer een compleet rapport uit SQL toveren, alhoewel ik zelf voorstander ben van de draaitabel. maar dan moet wel je data zo geformatteerd zijn dat je het direct kan gebruiken.

Ik kan dit helaas niet spoiler tags geven
[SQL]
WITH ReportVars AS
(
SELECT DATEPART(DW,
DATEADD(D,intDay,
DATEADD(M,intMonth-1,
CAST(intYear AS VARCHAR(10))+'-01-01'
))) as WeekDag
/* UREN APART */
,STUFF(RIGHT('0000' + CAST(bTime AS VARCHAR(4)),4),3,0,':') as tBegin
,STUFF(RIGHT('0000' + CAST(eTime AS VARCHAR(4)),4),3,0,':') as tEind
/* OF SAMEN */
,STUFF(RIGHT('0000' + CAST(bTime AS VARCHAR(4)),4),3,0,':') + '-' +
STUFF(RIGHT('0000' + CAST(eTime AS VARCHAR(4)),4),3,0,':') AS Tijd
,Worktime AS Totaal
,Name AS Naam
,Note AS Opmerking
FROM <TABEL?>
)
SELECT Naam
/* kan maximaal één opmerking per week in deze layout : */
,MAX(Opmerking) as Opmerking
/* let op: achterijke amerikanen beschouwen zondag als dag 1 */
,MAX(CASE WHEN WeekDag = 2 THEN Tijd ELSE '' END) AS Maandag
,MAX(CASE WHEN WeekDag = 3 THEN Tijd ELSE '' END) AS Dinsdag
,MAX(CASE WHEN WeekDag = 4 THEN Tijd ELSE '' END) AS Woensdag
,MAX(CASE WHEN WeekDag = 5 THEN Tijd ELSE '' END) AS Donderdag
,MAX(CASE WHEN WeekDag = 6 THEN Tijd ELSE '' END) AS Vrijdag
,MAX(CASE WHEN WeekDag = 7 THEN Tijd ELSE '' END) AS Zaterdag
,MAX(CASE WHEN WeekDag = 1 THEN Tijd ELSE '' END) AS Zondag
,SUM(Totaal) AS Totaal
FROM ReportVars
GROUP BY Naam
[/SQL]

 

[Interface]

Dank Mark!

Mark XL,

Allereerst, wow ziet er altijd fancy uit zo'n mooie SQL! Daarbij kan ik zonder de SQL toe te passen al precies zien wat je doet, en hoe je de dataset opbouwt.

Daarbij wil ik zeggen dat ik gebruik maak van een Access database. Uit ervaring weet ik dat gezien de performance het niet een optie is om alles elke keer uit de database te trekken en weer terug te stoppen. Ik heb dit namelijk al een keer geprobeerd. In die case gebruikte ik Excel alleen als skin voor de database.

Hierna heb ik dezelfde db gebruikt en een skin in vb6 erover heen gegooid, dit was echter voor eindgebruikers nog vervelender omdat Excel data makkelijk te kopiëren en te bewerken is.

Uiteindelijk terug gegaan naar een bestand dat een volle week bevat, en deze is gebaseerd op een basisplanning die in de database staat. Vervolgens worden deze gegevens opgehaald en aangepast waar nodig en aan het einde van de week weer teruggeschreven naar een historische tabel in de db(dit voor statistiek gegevens).

Gedurende de week zal dus het Excel bestand gebruikt worden(met daarin het tbTime werkblad). Daaruit moet dus een query komen en met het juiste format worden weggeschreven naar een ander tabblad. Dit is de handeling die het meest gedaan wordt en het heeft dus de hoogste prioriteit voor mij dat dit super snel is.

Het probleem met SQL is dat ik niet de sqlfuncties kan gebruiken die bijvoorbeeld worden ondersteunt met een Oracle of mySQL. Het is dus niet mogelijk om een genestelde SQL statement te maken met het AS statement op het moment dat het werkboek geopend is. Ik kan wel SELECT .... AS gebruiken maar er vervolgens niet meer selecteren in dezelfde query(in deze case gebruik ik het werkblad tbTime uit hetzelfde werkboek, ik spreek dus geen database aan maar een werkblad uit het actieve werkboek).

Ik heb combinaties geprobeerd, maar het lijkt erop dat het initialiseren van SQL in combinatie met het aanspreken van de ADO class een niet te missen vertraging opleveren.

Daarom ben ik aan de slag gegaan met mijn eigen code van datasets opbouwen en vervolgens doorlopen, voorlopige uitkomst is dat behalve het genereren van de netto werktijden en nog wat kleine minor dingetjes sneller zijn dan formules.

Ik kan bijna niet geloven dat een streven naar het alleen gebruik van VBA processen kon vertragen...

Laat ik het zo zeggen, ik heb aparte classes gebouwd voor alles, en alles als objecten/private types benaderd, maar door het initialiseren van classes ben ik uiteindelijk nog steeds langzamer als formules in combinatie met VBA. Ik heb zelfs nog even geprobeerd om pointers op te vragen(varPtr, objPtr en strPtr i.c.m. Lib "kernel32" functie copymem) en deze door te geven aan me functies, classes en procedures om op deze manier data overdracht te versnellen, hier ben ik snel op teruggekomen en gaf zeker niet het gewenste resultaat.

Tevens ben ik zo iemand die altijd de tijd berekend die een macro/script/code nodig heeft, en heb nu in de afgelopen jaren gigantisch veel forums gelezen(net als de meeste hier waarschijnlijk), maar ben nu echt op zoek naar de meest efficiënte manier. Dit bovenstaande is een goed voorbeeld, maar ik merk ook vaak dat voor het berekenen van prognoses en overall resultaten er nog veel grotere datasets nodig zijn , en dus dit nog langzamer is...

Ondanks dat ik dus niet heel veel heb aan je statement, kan ik het waarderen dat je ook daadwerkelijk de query gemaakt heb:shocked:, dat je daar tijd voor vrijmaakt! Super bedankt!

 

[snb]

Probleem is dat als ik teveel data in mijn sheet heb staan (ong. 2000 a 3000 regels) dit duurt vervolgens 26 sec ong. om de regels direct in een één dimensionale array over te zetten.

Wat als

sub snb()
  c00=thisworkbook.fullname
  thisworkbook.saveas "E:\voorbeeld.txt"
  thisworkbook.saveas c00
 
  open "E:\voorbeeld.txt" for input as #1
   sn=split(input(LOF(1),1),vbcrlf)
  close
End sub

 

[Mark xl]

Beste Interface,

Ik denk dat je het iets anders moet zoeken

Over dat sql statement, Ik ben veel bezig met databases en vba, en zo een statement schud ik zo uit mn mouw.

Het belangrijkste deel van de door mij gesuggereerde query is niet de geneste statement, maar het transformeren van de velden zodat je er eventueel een draaitabel van kan maken. Want de fout in je aanpak zit het hem in het combineren van verschillende stappen in één. probeer je einddoel stapsgewijst te behalen.


optimaliseer je query in access zodat zoveel mogelijk data direct overeenkomt met de gewenste data in je rapport
Maak een werkmap aan met een query met sample data.

Mocht het niet lukken om de velden in de query te transformeren, gebruik dan een Excel formule.

bouw vervolgens een draaitabel met de velden die geoptimaliseerd zijn voor het uiteindelijke rapport (dus de van--tot samengevoegd), weekdag , etc.

Maak vervolgens een macro en deel deze op in 3 delen:
- een voor rapportage geoptimaliseerde query uit access naar excel.
- een toevoeging van berekende kolommen (een formule die de data transformeert zodat je deze direct in je rapport kunt gebruiken)
vergeet niet de formules in waarden te veranderen
- het opnieuw instellen van je draaitabel (opnieuw gegevens selecteren, vernieuwen)

Dat zou snel moeten gaan.

Wat is overigens de reden dat je van-tot tijden in de tabel wilt laten zien? wil je niet het aantal uren per dag zien?

 

[Interface]

Dan Mark en Snb voor jullie reactie!

Wat als

sub snb()
  c00=thisworkbook.fullname
  thisworkbook.saveas "E:\voorbeeld.txt"
  thisworkbook.saveas c00
 
  open "E:\voorbeeld.txt" for input as #1
   sn=split(input(LOF(1),1),vbcrlf)
  close
End sub

Hier had ik nog niet aan gedacht, slim een soort van "tussen database", zonder actie in het script( dus zonder join) duurt het initialiseren van het openen en weer sluiten net iets minder als 1,5 seconde, dat is toch echt iets te lang.

Echter heb ik wel degelijk hier iets aan, om de bestandsgrootte klein te houden kan ik wel op het moment van opstarten en afsluiten deze gegevens importeren/exporteren.

Ik ben nu bezig om de tbTime aan te passen zodat de weergave bijna 1 op 1 overgenomen kan worden door een script en er dus weinig berekeningen nodig zijn om het uiteindelijke format te realiseren.

Zodra ik mijn bevindingen hiermee heb getest zal ik posten wat het verschil is met de oude opzet.

Nogmaals beide bedankt!

 

[Interface]

voorlopig is dit het snelst

Ik heb de tabel aangepast en de tabel zo ingericht dat het met een simpeler loop snel wordt opgehaald(0,03 sec.)

Je ziet dat het dus meer dan belangrijk is om de data in de tabel zo effici�nt mogelijk in te richten.

Het script ziet er nu zo uit:

'---------------------------------------------------------------------------------------
' Author    : Interface
' Date      : 13-1-2012
' Purpose   : HelpMij
'---------------------------------------------------------------------------------------

Option Explicit

Sub GiveMeMyPlanning()

Const strCOLUMN_PL_COUNT         As Integer = 10

Dim strColumn(10)                       As String
Dim varTime                             As Variant
Dim varPlanning()                       As String
Dim i                                   As Integer
Dim j                                   As Integer

Dim begin                               As Double
Dim eind                                As Double

On Error GoTo Hell

begin = Timer

strColumn(0) = "Naam"
strColumn(1) = "Afdeling"
strColumn(2) = "Opmerking"
strColumn(3) = "Maandag"
strColumn(4) = "Dinsdag"
strColumn(5) = "Woensdag"
strColumn(6) = "Donderdag"
strColumn(7) = "Vrijdag"
strColumn(8) = "Zaterdag"
strColumn(9) = "Zondag"
strColumn(10) = "Totaal"

varTime = Sheets("blad1").UsedRange
ReDim varPlanning(UBound(varTime), 11)

For i = 1 To UBound(varTime)
    For j = 0 To strCOLUMN_PL_COUNT
        If i > 1 Then
                Select Case j
                    Case 3 To 9
                        If Len(varTime(i, ((j - 4) * 2) + 9)) > 0 Then
                            varPlanning(i, j + 1) = varTime(i, ((j - 4) * 2) + 8) & "-" & varTime(i, ((j - 4) * 2) + 9) 'Join the btime en etime
                        End If
                    Case 0 To 2
                        varPlanning(i, j + 1) = varTime(i, j + 3) 'Dep and Note
                    Case 10
                        varPlanning(i, j + 1) = varTime(i, 20) ' Worktime
                End Select
        Else
            varPlanning(1, j + 1) = CStr(strColumn(j)) 'add columnName from strColumnArray
        End If
    Next j
Next i

Sheets("planning").Range("c4:n" & UBound(varPlanning) + 4) = varPlanning

eind = Timer
MsgBox eind - begin

Exit Sub

Hell:
    MsgBox "Er is een fout opgetreden!", vbCritical
End Sub

 

[snb]

Ik zie geen verschil in opzet met je vorige bestand ....

 

[Interface]

er is wel degelijk verschil

Maar ik heb de nieuwe indeling van tbTime$ niet (mee)getoond in me laatste reactie. ik ben van 11 kolommen naar 20 gegaan en daardoor hoef ik minder data te controleren en minder logische tests te doen.