Help me speed this up.

MerkurAlex · December 3, 2009

Okay so I wrote some code and it seems like it could go a lot faster but I am having trouble thinking about how to make it faster.

it uses the "test.csv" file I provided below just make "test.csv" in the same folder with the code and it should run(SLOWLY!)

The code:

#include-once
#include <array.au3>
#Include <Date.au3>

;$StartDate = '2009-09-01 00:00:00'
;$EndDate = '2009-09-02 00:00:00'
dim $dates[1][2]=[[0]]
$file=FileOpen(@ScriptDir&"\test.csv", 0)
$csv=StringRegExp(FileRead($file),"(.*?)"&@CRLF,3)
FileClose($file)
$exitloop=0

for $i=0 to ubound($csv)-1
    $csvSplit=StringSplit($csv[$i],",")
    $tarr=StringRegExp($csvSplit[1],"(.*?)\.",1)
    $csvSplit[1]=$tarr[0]
    $tarr=StringRegExp($csvSplit[2],"(.*?)\.",1)
    $csvSplit[2]=$tarr[0]
    
    $diff=_DateDiff('s',$csvSplit[1],$csvSplit[2])
    for $d=1 to $diff
        $curDate=_DateAdd('s',$d,$csvSplit[1])
        $found=0
        for $e=0 to UBound($dates)-1
            if string($dates[$e][0])=$curDate Then
                $found=1
                $dates[$e][1]+=1
                ExitLoop
            EndIf
        Next
        if $found=0 Then
            reDim $dates[ubound($dates)+1][2]
            $dates[ubound($dates)-1][0]=$curDate
            $dates[ubound($dates)-1][1]=1
        EndIf
    Next
Next
_ArrayDisplay($dates)

The "test.csv" file I am working with:

2009-09-01 07:59:08.257,2009-09-01 08:00:46.080
2009-09-01 08:02:11.247,2009-09-01 08:03:01.807
2009-09-01 08:05:32.823,2009-09-01 08:06:50.387
2009-09-01 08:08:40.807,2009-09-01 08:10:18.650
2009-09-01 08:09:10.340,2009-09-01 08:10:48.153
2009-09-01 08:09:12.887,2009-09-01 08:10:31.433
2009-09-01 08:11:57.387,2009-09-01 08:12:44.590
2009-09-01 08:12:00.450,2009-09-01 08:13:31.247
2009-09-01 08:29:55.717,2009-09-01 08:30:43.093
2009-09-01 08:30:05.657,2009-09-01 08:30:18.000
2009-09-01 08:31:09.453,2009-09-01 08:31:58.610
2009-09-01 08:31:21.703,2009-09-01 08:32:11.127
2009-09-01 08:32:55.033,2009-09-01 08:33:42.270
2009-09-01 08:33:06.940,2009-09-01 08:34:42.567
2009-09-01 08:33:19.283,2009-09-01 08:34:44.693
2009-09-01 08:33:37.690,2009-09-01 08:34:22.927
2009-09-01 08:33:37.910,2009-09-01 08:34:28.880
2009-09-01 08:33:42.253,2009-09-01 08:33:56.753
2009-09-01 08:34:22.723,2009-09-01 08:35:08.490
2009-09-01 08:34:50.833,2009-09-01 08:35:50.960
2009-09-01 08:35:03.537,2009-09-01 08:35:50.193
2009-09-01 08:35:16.553,2009-09-01 08:36:03.397
2009-09-01 08:35:35.147,2009-09-01 08:36:22.380
2009-09-01 08:44:33.970,2009-09-01 08:45:20.453
2009-09-01 08:46:38.003,2009-09-01 08:47:24.127
2009-09-01 08:57:41.187,2009-09-01 08:57:48.517
2009-09-01 08:57:48.267,2009-09-01 08:58:22.077
2009-09-01 09:44:56.587,2009-09-01 09:45:11.803
2009-09-01 10:25:07.787,2009-09-01 10:26:25.727
2009-09-01 10:32:51.000,2009-09-01 10:34:03.423
2009-09-01 10:34:55.673,2009-09-01 10:35:03.003
2009-09-01 10:35:19.300,2009-09-01 10:36:04.817
2009-09-01 10:39:58.273,2009-09-01 10:41:05.383
2009-09-01 10:44:07.263,2009-09-01 10:44:37.153
2009-09-01 10:44:55.437,2009-09-01 10:46:06.123
2009-09-01 10:47:36.377,2009-09-01 10:48:52.457
2009-09-01 10:48:29.127,2009-09-01 10:49:49.753
2009-09-01 10:49:33.520,2009-09-01 10:50:20.363
2009-09-01 10:50:01.160,2009-09-01 10:51:05.973
2009-09-01 10:51:03.363,2009-09-01 10:52:21.663
2009-09-01 10:51:38.693,2009-09-01 10:52:25.570
2009-09-01 11:00:09.923,2009-09-01 11:00:56.267
2009-09-01 11:00:35.080,2009-09-01 11:01:49.533
2009-09-01 11:00:54.313,2009-09-01 11:01:40.597
2009-09-01 11:01:37.970,2009-09-01 11:02:25.143
2009-09-01 11:01:56.550,2009-09-01 11:02:51.377
2009-09-01 11:03:03.487,2009-09-01 11:03:48.347
2009-09-01 11:03:05.127,2009-09-01 11:03:50.787
2009-09-01 11:07:00.633,2009-09-01 11:08:36.120
2009-09-01 11:39:25.483,2009-09-01 11:40:11.420
2009-09-01 11:39:49.937,2009-09-01 11:40:57.110
2009-09-01 12:20:59.057,2009-09-01 12:21:02.603
2009-09-01 16:35:24.257,2009-09-01 16:35:52.633
2009-09-01 16:35:41.680,2009-09-01 16:36:54.213
2009-09-01 16:36:40.510,2009-09-01 16:37:52.353
2009-09-01 16:39:39.373,2009-09-01 16:40:25.577
2009-09-01 16:42:23.627,2009-09-01 16:43:40.750
2009-09-01 16:42:39.720,2009-09-01 16:43:55.690
2009-09-01 16:42:53.937,2009-09-01 16:44:15.737
2009-09-01 16:43:17.673,2009-09-01 16:44:08.533
2009-09-01 16:43:31.237,2009-09-01 16:44:20.097
2009-09-01 16:44:13.347,2009-09-01 16:44:59.877
2009-09-01 16:44:21.237,2009-09-01 16:45:06.817
2009-09-01 16:44:40.800,2009-09-01 16:45:26.427
2009-09-01 16:44:42.113,2009-09-01 16:45:54.647
2009-09-01 16:44:45.283,2009-09-01 16:46:00.457
2009-09-01 16:44:54.033,2009-09-01 16:46:12.583
2009-09-01 16:45:06.080,2009-09-01 16:46:25.473
2009-09-01 16:45:26.537,2009-09-01 16:46:35.410
2009-09-01 16:45:31.440,2009-09-01 16:46:24.020
2009-09-01 16:46:20.113,2009-09-01 16:47:58.880
2009-09-01 16:46:52.833,2009-09-01 16:48:30.740
2009-09-01 16:47:33.507,2009-09-01 16:49:11.413
2009-09-01 16:47:35.927,2009-09-01 16:48:30.100
2009-09-01 16:47:52.693,2009-09-01 16:48:39.273
2009-09-01 16:48:16.710,2009-09-01 16:49:03.460
2009-09-01 16:48:39.197,2009-09-01 16:51:06.790
2009-09-01 16:48:39.210,2009-09-01 16:49:25.947
2009-09-01 16:48:56.507,2009-09-01 16:49:44.087
2009-09-01 16:49:01.430,2009-09-01 16:49:54.087
2009-09-01 16:49:13.617,2009-09-01 16:50:32.727
2009-09-01 16:49:30.540,2009-09-01 16:50:22.527
2009-09-01 16:49:32.743,2009-09-01 16:50:51.400
2009-09-01 16:53:05.480,2009-09-01 16:53:53.597
2009-09-01 16:53:08.543,2009-09-01 16:53:55.923
2009-09-01 16:53:15.523,2009-09-01 16:54:57.543
2009-09-01 16:53:46.117,2009-09-01 16:55:06.100
2009-09-01 16:54:00.233,2009-09-01 16:55:18.110
2009-09-01 17:15:09.670,2009-09-01 17:16:22.483
2009-09-01 17:17:58.827,2009-09-01 17:19:10.593
2009-09-01 17:18:59.140,2009-09-01 17:19:45.937
2009-09-01 19:07:54.140,2009-09-01 19:09:11.127
2009-09-01 19:47:31.670,2009-09-01 19:48:45.657
2009-09-01 20:05:05.753,2009-09-01 20:06:18.567

Anyone feel like helping?

if it helps we could just make it a competition to see who can make it go the fastest

MerkurAlex · December 3, 2009

Okay... sorry if this is considered a bump.

I found a way to make it much faster the array was getting too large and the check I had to add 1 to the second dimension in the array instead of adding a whole new row was bogging it down so I handle 10 lines from the file at a time with this code:

#include-once
#include <array.au3>
#include <Date.au3>

;$StartDate = '2009-09-01 00:00:00'
;$EndDate = '2009-09-02 00:00:00'

$file = FileOpen(@ScriptDir & "\test.csv", 0)
$csv = StringRegExp(FileRead($file), "(.*?)" & @CRLF, 3)
FileClose($file)
$exitloop = 0

$timer = TimerInit()
$lastTime = 0

$output = ""

$perEach = 10
For $amt = 0 To UBound($csv) - 1 Step $perEach
    Dim $dates[1][2] = [[0]]
    If $amt + $perEach <= UBound($csv) - 1 Then
        $final = $amt + $perEach
    Else
        $final = UBound($csv) - 1
    EndIf
    $lastTime = TimerDiff($timer)
    For $i = $amt To $final
        $csvSplit = StringSplit($csv[$i], ",")
        $tarr = StringRegExp($csvSplit[1], "(.*?)\.", 1)
        $csvSplit[1] = $tarr[0]
        $tarr = StringRegExp($csvSplit[2], "(.*?)\.", 1)
        $csvSplit[2] = $tarr[0]

        $diff = _DateDiff('s', $csvSplit[1], $csvSplit[2])
        For $d = 1 To $diff
            $curDate = _DateAdd('s', $d, $csvSplit[1])
            $found = 0

            For $e = 0 To UBound($dates) - 1
                If String($dates[$e][0]) = $curDate Then
                    $found = 1
                    $dates[$e][1] += 1
                    ExitLoop
                EndIf
            Next

            If $found = 0 Then
                ReDim $dates[UBound($dates) + 1][2]
                $dates[UBound($dates) - 1][0] = $curDate
                $dates[UBound($dates) - 1][1] = 1
            EndIf
        Next
    Next
    ConsoleWrite(Round(($i / (UBound($csv) - 1)) * 100, 0) & "%" & "  " & $amt & " to " & $final & " took: " & (TimerDiff($timer) - $lastTime) & @CRLF)
    For $a = 0 To UBound($dates) - 1
        $output &= $dates[$a][0] & "," & $dates[$a][1] & @CRLF
    Next
Next

ClipPut($output)
ConsoleWrite($output)
HotKeySet("{enter}", "exitificate")
ConsoleWrite(@CRLF & "Press enter to continue...")
While 1
    Sleep(1)
WEnd

Func exitificate()
    Exit
EndFunc   ;==>exitificate

I still feel it could be faster if you have any suggestions they are welcome...

Zedna · December 4, 2009

Add time testing to see what is slower:

$start = TimerInit()
...
ConsoleWrite(TimerDiff($start) & @CRLF)

Tips:

- use stringmid to parse strings when you have known positions, it's quicker than regexp

- make optimized local copy of standard date/time UDFs -> remove all general unneccessary code from it (tests of correct inputs etc.)

- probably there could be done some optimizations replacing Redim of array in inner loop

- add consolewrites to see which parts of code cost much time

- precalculate constants: 86400 instead of 24 * 60 * 60

- don't calculate the same function more than once:

reDim $dates[ubound($dates)+1][2]
    $dates[ubound($dates)-1][0]=$curDate
    $dates[ubound($dates)-1][1]=1

$count = ubound($dates)+1
    reDim $dates[$count][2]
    $dates[$count-1][0]=$curDate
    $dates[$count-1][1]=1

Here is some concept (unfinished) and beware:

DateTimeSplit() is only concept how to do optimizations - this one doesn't return correct results

#include-once
#include <array.au3>
#Include <Date.au3>

$start = TimerInit()

;$StartDate = '2009-09-01 00:00:00'
;$EndDate = '2009-09-02 00:00:00'
dim $dates[1][2]=[[0]]
$csv=StringSplit(FileRead(@ScriptDir&"\test.csv"),@CRLF,1)
$exitloop=0

for $i=1 to $csv[0]
    $date1=StringLeft($csv[$i],23)
    $date2=StringMid($csv[$i],25,23)

;~  $csvSplit=StringSplit($csv[$i],",")
;~  $tarr=StringRegExp($csvSplit[1],"(.*?)\.",1)
;~  $tarr=StringSplit($csvSplit[1],"(.*?)\.",1)
;~  $csvSplit[1]=$tarr[0]
;~  $tarr=StringRegExp($csvSplit[2],"(.*?)\.",1)
;~  $csvSplit[2]=$tarr[0]
    
    $diff=DateDiff('s',$date1,$date2)
    for $d=1 to $diff
    $curDate=DateAdd('s',$d,$date1)
    $found=0
    for $e=0 to UBound($dates)-1
    if string($dates[$e][0])=$curDate Then
    $found=1
    $dates[$e][1]+=1
    ExitLoop
    EndIf
    Next
    if $found=0 Then
            $count = ubound($dates)+1
    reDim $dates[$count][2]
    $dates[$count-1][0]=$curDate
    $dates[$count-1][1]=1
    EndIf
    Next
Next

ConsoleWrite(TimerDiff($start) & @CRLF)
_ArrayDisplay($dates)
 
 
Func DateDiff($sType, $sStartDate, $sEndDate)
    Local $asStartDatePart[4]
    Local $asStartTimePart[4]
    Local $asEndDatePart[4]
    Local $asEndTimePart[4]
    Local $iTimeDiff
;~  Local $iYearDiff
;~  Local $iMonthDiff
    Local $iStartTimeInSecs
    Local $iEndTimeInSecs
    Local $aDaysDiff
    ;
    ; Verify that $sType is Valid
;~  $sType = StringLeft($sType, 1)
;~  If StringInStr("d,m,y,w,h,n,s", $sType) = 0 Or $sType = "" Then
;~      SetError(1)
;~      Return (0)
;~  EndIf
    ; Verify If StartDate is valid
;~  If Not _DateIsValid($sStartDate) Then
;~      SetError(2)
;~      Return (0)
;~  EndIf
    ; Verify If EndDate is valid
;~  If Not _DateIsValid($sEndDate) Then
;~      SetError(3)
;~      Return (0)
;~  EndIf
    ; split the StartDate and Time into arrays
    DateTimeSplit($sStartDate, $asStartDatePart, $asStartTimePart)
    ; split the End Date and time into arrays
    DateTimeSplit($sEndDate, $asEndDatePart, $asEndTimePart)
    ; ====================================================
    ; Get the differens in days between the 2 dates
    $aDaysDiff = _DateToDayValue($asEndDatePart[1], $asEndDatePart[2], $asEndDatePart[3]) - _DateToDayValue($asStartDatePart[1], $asStartDatePart[2], $asStartDatePart[3])
    ; ====================================================
    ; Get the differens in Seconds between the 2 times when specified
    If $asStartTimePart[0] > 1 And $asEndTimePart[0] > 1 Then
        $iStartTimeInSecs = $asStartTimePart[1] * 3600 + $asStartTimePart[2] * 60 + $asStartTimePart[3]
        $iEndTimeInSecs = $asEndTimePart[1] * 3600 + $asEndTimePart[2] * 60 + $asEndTimePart[3]
        $iTimeDiff = $iEndTimeInSecs - $iStartTimeInSecs
        If $iTimeDiff < 0 Then
            $aDaysDiff = $aDaysDiff - 1
            $iTimeDiff = $iTimeDiff + 86400 ; 24 * 60 * 60
        EndIf
    Else
        $iTimeDiff = 0
    EndIf
;~  Select
;~      Case $sType = "d"
;~          Return ($aDaysDiff)
;~      Case $sType = "m"
;~          $iYearDiff = $asEndDatePart[1] - $asStartDatePart[1]
;~          $iMonthDiff = $asEndDatePart[2] - $asStartDatePart[2] + $iYearDiff * 12
;~          If $asEndDatePart[3] < $asStartDatePart[3] Then $iMonthDiff = $iMonthDiff - 1
;~          $iStartTimeInSecs = $asStartTimePart[1] * 3600 + $asStartTimePart[2] * 60 + $asStartTimePart[3]
;~          $iEndTimeInSecs = $asEndTimePart[1] * 3600 + $asEndTimePart[2] * 60 + $asEndTimePart[3]
;~          $iTimeDiff = $iEndTimeInSecs - $iStartTimeInSecs
;~          If $asEndDatePart[3] = $asStartDatePart[3] And $iTimeDiff < 0 Then $iMonthDiff = $iMonthDiff - 1
;~          Return ($iMonthDiff)
;~      Case $sType = "y"
;~          $iYearDiff = $asEndDatePart[1] - $asStartDatePart[1]
;~          If $asEndDatePart[2] < $asStartDatePart[2] Then $iYearDiff = $iYearDiff - 1
;~          If $asEndDatePart[2] = $asStartDatePart[2] And $asEndDatePart[3] < $asStartDatePart[3] Then $iYearDiff = $iYearDiff - 1
;~          $iStartTimeInSecs = $asStartTimePart[1] * 3600 + $asStartTimePart[2] * 60 + $asStartTimePart[3]
;~          $iEndTimeInSecs = $asEndTimePart[1] * 3600 + $asEndTimePart[2] * 60 + $asEndTimePart[3]
;~          $iTimeDiff = $iEndTimeInSecs - $iStartTimeInSecs
;~          If $asEndDatePart[2] = $asStartDatePart[2] And $asEndDatePart[3] = $asStartDatePart[3] And $iTimeDiff < 0 Then $iYearDiff = $iYearDiff - 1
;~          Return ($iYearDiff)
;~      Case $sType = "w"
;~          Return (Int($aDaysDiff / 7))
;~      Case $sType = "h"
;~          Return ($aDaysDiff * 24 + Int($iTimeDiff / 3600))
;~      Case $sType = "n"
;~          Return ($aDaysDiff * 24 * 60 + Int($iTimeDiff / 60))
;~      Case $sType = "s"
            Return ($aDaysDiff * 86400 + $iTimeDiff) ; 24 * 60 * 60
;~  EndSelect
EndFunc ;==>_DateDiff
 
Func DateAdd($sType, $iValToAdd, $sDate)
    Local $asTimePart[4]
    Local $asDatePart[4]
    Local $iJulianDate
    Local $iTimeVal
    Local $iNumDays
    Local $Day2Add
    ; Verify that $sType is Valid
;~  $sType = StringLeft($sType, 1)
;~  If StringInStr("D,M,Y,w,h,n,s", $sType) = 0 Or $sType = "" Then
;~      SetError(1)
;~      Return (0)
;~  EndIf
    ; Verify that Value to Add is Valid
;~  If Not StringIsInt($iValToAdd) Then
;~      SetError(2)
;~      Return (0)
;~  EndIf
    ; Verify If InputDate is valid
;~  If Not _DateIsValid($sDate) Then
;~      SetError(3)
;~      Return (0)
;~  EndIf
    ; split the date and time into arrays
    DateTimeSplit($sDate, $asDatePart, $asTimePart)
    
    ; ====================================================
    ; adding days then get the julian date
    ; add the number of day
    ; and convert back to Gregorian
;~  If $sType = "d" Or $sType = "w" Then
;~      If $sType = "w" Then $iValToAdd = $iValToAdd * 7
;~      $iJulianDate = _DateToDayValue($asDatePart[1], $asDatePart[2], $asDatePart[3]) + $iValToAdd
;~      _DayValueToDate($iJulianDate, $asDatePart[1], $asDatePart[2], $asDatePart[3])
;~  EndIf
    ; ====================================================
    ; adding Months
;~  If $sType = "m" Then
;~      $asDatePart[2] = $asDatePart[2] + $iValToAdd
;~      ; pos number of months
;~      While $asDatePart[2] > 12
;~          $asDatePart[2] = $asDatePart[2] - 12
;~          $asDatePart[1] = $asDatePart[1] + 1
;~      WEnd
;~      ; Neg number of months
;~      While $asDatePart[2] < 1
;~          $asDatePart[2] = $asDatePart[2] + 12
;~          $asDatePart[1] = $asDatePart[1] - 1
;~      WEnd
;~  EndIf
    ; ====================================================
    ; adding Years
;~  If $sType = "y" Then
;~      $asDatePart[1] = $asDatePart[1] + $iValToAdd
;~  EndIf
    ; ====================================================
    ; adding Time value
;~  If $sType = "h" Or $sType = "n" Or $sType = "s" Then
        $iTimeVal = _TimeToTicks($asTimePart[1], $asTimePart[2], $asTimePart[3]) / 1000
;~      If $sType = "h" Then $iTimeVal = $iTimeVal + $iValToAdd * 3600
;~      If $sType = "n" Then $iTimeVal = $iTimeVal + $iValToAdd * 60
        If $sType = "s" Then $iTimeVal = $iTimeVal + $iValToAdd
        ; calculated days to add
        $Day2Add = Int($iTimeVal / 86400) ; (24 * 60 * 60)
        $iTimeVal = $iTimeVal - $Day2Add * 86400 ; 24 * 60 * 60
        If $iTimeVal < 0 Then
            $Day2Add = $Day2Add - 1
            $iTimeVal = $iTimeVal + 86400 ; 24 * 60 * 60
        EndIf
        $iJulianDate = _DateToDayValue($asDatePart[1], $asDatePart[2], $asDatePart[3]) + $Day2Add
        ; calculate the julian back to date
        _DayValueToDate($iJulianDate, $asDatePart[1], $asDatePart[2], $asDatePart[3])
        ; caluculate the new time
        _TicksToTime($iTimeVal * 1000, $asTimePart[1], $asTimePart[2], $asTimePart[3])
;~  EndIf
    ; ====================================================
    ; check if the Input day is Greater then the new month last day.
    ; if so then change it to the last possible day in the month
    $iNumDays = StringSplit('31,28,31,30,31,30,31,31,30,31,30,31', ',')
    If _DateIsLeapYear($asDatePart[1]) Then $iNumDays[2] = 29
    ;
    If $iNumDays[$asDatePart[2]] < $asDatePart[3] Then $asDatePart[3] = $iNumDays[$asDatePart[2]]
    ; ========================
    ; Format the return date
    ; ========================
    ; Format the return date
    $sDate = $asDatePart[1] & '/' & StringRight("0" & $asDatePart[2], 2) & '/' & StringRight("0" & $asDatePart[3], 2)
    ; add the time when specified in the input
    If $asTimePart[0] > 0 Then
        If $asTimePart[0] > 2 Then
            $sDate = $sDate & " " & StringRight("0" & $asTimePart[1], 2) & ':' & StringRight("0" & $asTimePart[2], 2) & ':' & StringRight("0" & $asTimePart[3], 2)
        Else
            $sDate = $sDate & " " & StringRight("0" & $asTimePart[1], 2) & ':' & StringRight("0" & $asTimePart[2], 2)
        EndIf
    EndIf
    ;
    Return ($sDate)
EndFunc ;==>_DateAdd
 
Func DateTimeSplit($sDate, ByRef $asDatePart, ByRef $iTimePart)
    $asDatePart = StringSplit(StringLeft($sDate,10), "-")
    $iTimePart = StringSplit(StringMid($sDate,12,12), ":")

;~  Local $sDateTime
;~  Local $x
;~  ; split the Date and Time portion
;~  $sDateTime = StringSplit($sDate, " T")
;~  ; split the date portion
;~  If $sDateTime[0] > 0 Then $asDatePart = StringSplit($sDateTime[1], "/-.")
;~  ; split the Time portion
;~  If $sDateTime[0] > 1 Then
;~      $iTimePart = StringSplit($sDateTime[2], ":")
;~      If UBound($iTimePart) < 4 Then ReDim $iTimePart[4]
;~  Else
;~      Dim $iTimePart[4]
;~  EndIf
;~  ; Ensure the arrays contain 4 values
;~  If UBound($asDatePart) < 4 Then ReDim $asDatePart[4]
;~  ; update the array to contain numbers not strings
;~  For $x = 1 To 3
;~      If StringIsInt($asDatePart[$x]) Then
;~          $asDatePart[$x] = Number($asDatePart[$x])
;~      Else
;~          $asDatePart[$x] = -1
;~      EndIf
;~      If StringIsInt($iTimePart[$x]) Then
;~          $iTimePart[$x] = Number($iTimePart[$x])
;~      Else
;~          $iTimePart[$x] = -1
;~      EndIf
;~  Next
;~  Return (1)
EndFunc ;==>_DateTimeSplit

MerkurAlex · December 4, 2009

@Zedna

Yes I used timers to find out what I posted just before what you posted I certainly sped it up quite a bit.

Thanks for all the tips though I will look your post over

Zedna · December 4, 2009

If you work with large arrays (add/redim/search) you can can use Dictionary COM object or memory SQLite database instead which spead it up VERY MUCH!

Zedna · December 4, 2009

Here is SQLite version which is about 20 times quicker than original one based on array redim

#include-once
#include <array.au3>
#Include <Date.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

Global $iRows, $iColumns;, $iRval

$start = TimerInit()

_SQLite_Startup ()
_SQLite_Open () ; Open a :memory: database
_SQLite_Exec (-1, "BEGIN;")
_SQLite_Exec (-1, "CREATE TABLE dates1 (date1);")
_SQLite_Exec (-1, "CREATE TABLE dates2 (date2, count2);")
_SQLite_Exec (-1, "CREATE INDEX ix1 on dates1 (date1);")
_SQLite_Exec (-1, "CREATE INDEX ix2 on dates2 (date2);")

;$StartDate = '2009-09-01 00:00:00'
;$EndDate = '2009-09-02 00:00:00'
dim $dates
$csv=StringSplit(FileRead(@ScriptDir&"\test.csv"),@CRLF,1)
$exitloop=0

for $i=1 to $csv[0]
    $date1=StringLeft($csv[$i],23)
    $date2=StringMid($csv[$i],25,23)

    $sql_cmd = ''
    
    $diff=DateDiff('s',$date1,$date2)
    for $d=1 to $diff
    $curDate=DateAdd('s',$d,$date1)
        $sql_cmd &= "INSERT INTO dates1 VALUES ('" & $curDate & "');"
    Next
    
    If $sql_cmd <> '' Then _SQLite_Exec (-1, $sql_cmd)
Next

_SQLite_Exec (-1, "INSERT INTO dates2 SELECT date1, count(*) FROM dates1 GROUP BY date1;")
_SQLite_Exec (-1, "COMMIT;")

_SQLite_GetTable2d (-1, "SELECT date2,count2 FROM dates2 ORDER BY date2;", $dates, $iRows, $iColumns)
_SQLite_Close()
_SQLite_Shutdown()

ConsoleWrite(TimerDiff($start) & @CRLF)
_ArrayDisplay($dates)
 
Func DateDiff($sType, $sStartDate, $sEndDate)
    Local $asStartDatePart[4]
    Local $asStartTimePart[4]
    Local $asEndDatePart[4]
    Local $asEndTimePart[4]
    Local $iTimeDiff
;~  Local $iYearDiff
;~  Local $iMonthDiff
    Local $iStartTimeInSecs
    Local $iEndTimeInSecs
    Local $aDaysDiff
    ;
    ; Verify that $sType is Valid
;~  $sType = StringLeft($sType, 1)
;~  If StringInStr("d,m,y,w,h,n,s", $sType) = 0 Or $sType = "" Then
;~      SetError(1)
;~      Return (0)
;~  EndIf
    ; Verify If StartDate is valid
;~  If Not _DateIsValid($sStartDate) Then
;~      SetError(2)
;~      Return (0)
;~  EndIf
    ; Verify If EndDate is valid
;~  If Not _DateIsValid($sEndDate) Then
;~      SetError(3)
;~      Return (0)
;~  EndIf
    ; split the StartDate and Time into arrays
    _DateTimeSplit($sStartDate, $asStartDatePart, $asStartTimePart)
    ; split the End Date and time into arrays
    _DateTimeSplit($sEndDate, $asEndDatePart, $asEndTimePart)
    ; ====================================================
    ; Get the differens in days between the 2 dates
    $aDaysDiff = _DateToDayValue($asEndDatePart[1], $asEndDatePart[2], $asEndDatePart[3]) - _DateToDayValue($asStartDatePart[1], $asStartDatePart[2], $asStartDatePart[3])
    ; ====================================================
    ; Get the differens in Seconds between the 2 times when specified
    If $asStartTimePart[0] > 1 And $asEndTimePart[0] > 1 Then
        $iStartTimeInSecs = $asStartTimePart[1] * 3600 + $asStartTimePart[2] * 60 + $asStartTimePart[3]
        $iEndTimeInSecs = $asEndTimePart[1] * 3600 + $asEndTimePart[2] * 60 + $asEndTimePart[3]
        $iTimeDiff = $iEndTimeInSecs - $iStartTimeInSecs
        If $iTimeDiff < 0 Then
            $aDaysDiff = $aDaysDiff - 1
            $iTimeDiff = $iTimeDiff + 86400 ; 24 * 60 * 60
        EndIf
    Else
        $iTimeDiff = 0
    EndIf
;~  Select
;~      Case $sType = "d"
;~          Return ($aDaysDiff)
;~      Case $sType = "m"
;~          $iYearDiff = $asEndDatePart[1] - $asStartDatePart[1]
;~          $iMonthDiff = $asEndDatePart[2] - $asStartDatePart[2] + $iYearDiff * 12
;~          If $asEndDatePart[3] < $asStartDatePart[3] Then $iMonthDiff = $iMonthDiff - 1
;~          $iStartTimeInSecs = $asStartTimePart[1] * 3600 + $asStartTimePart[2] * 60 + $asStartTimePart[3]
;~          $iEndTimeInSecs = $asEndTimePart[1] * 3600 + $asEndTimePart[2] * 60 + $asEndTimePart[3]
;~          $iTimeDiff = $iEndTimeInSecs - $iStartTimeInSecs
;~          If $asEndDatePart[3] = $asStartDatePart[3] And $iTimeDiff < 0 Then $iMonthDiff = $iMonthDiff - 1
;~          Return ($iMonthDiff)
;~      Case $sType = "y"
;~          $iYearDiff = $asEndDatePart[1] - $asStartDatePart[1]
;~          If $asEndDatePart[2] < $asStartDatePart[2] Then $iYearDiff = $iYearDiff - 1
;~          If $asEndDatePart[2] = $asStartDatePart[2] And $asEndDatePart[3] < $asStartDatePart[3] Then $iYearDiff = $iYearDiff - 1
;~          $iStartTimeInSecs = $asStartTimePart[1] * 3600 + $asStartTimePart[2] * 60 + $asStartTimePart[3]
;~          $iEndTimeInSecs = $asEndTimePart[1] * 3600 + $asEndTimePart[2] * 60 + $asEndTimePart[3]
;~          $iTimeDiff = $iEndTimeInSecs - $iStartTimeInSecs
;~          If $asEndDatePart[2] = $asStartDatePart[2] And $asEndDatePart[3] = $asStartDatePart[3] And $iTimeDiff < 0 Then $iYearDiff = $iYearDiff - 1
;~          Return ($iYearDiff)
;~      Case $sType = "w"
;~          Return (Int($aDaysDiff / 7))
;~      Case $sType = "h"
;~          Return ($aDaysDiff * 24 + Int($iTimeDiff / 3600))
;~      Case $sType = "n"
;~          Return ($aDaysDiff * 24 * 60 + Int($iTimeDiff / 60))
;~      Case $sType = "s"
            Return ($aDaysDiff * 86400 + $iTimeDiff) ; 24 * 60 * 60
;~  EndSelect
EndFunc ;==>_DateDiff
 
Func DateAdd($sType, $iValToAdd, $sDate)
    Local $asTimePart[4]
    Local $asDatePart[4]
    Local $iJulianDate
    Local $iTimeVal
    Local $iNumDays
    Local $Day2Add
    ; Verify that $sType is Valid
;~  $sType = StringLeft($sType, 1)
;~  If StringInStr("D,M,Y,w,h,n,s", $sType) = 0 Or $sType = "" Then
;~      SetError(1)
;~      Return (0)
;~  EndIf
    ; Verify that Value to Add is Valid
;~  If Not StringIsInt($iValToAdd) Then
;~      SetError(2)
;~      Return (0)
;~  EndIf
    ; Verify If InputDate is valid
;~  If Not _DateIsValid($sDate) Then
;~      SetError(3)
;~      Return (0)
;~  EndIf
    ; split the date and time into arrays
    _DateTimeSplit($sDate, $asDatePart, $asTimePart)
    
    ; ====================================================
    ; adding days then get the julian date
    ; add the number of day
    ; and convert back to Gregorian
;~  If $sType = "d" Or $sType = "w" Then
;~      If $sType = "w" Then $iValToAdd = $iValToAdd * 7
;~      $iJulianDate = _DateToDayValue($asDatePart[1], $asDatePart[2], $asDatePart[3]) + $iValToAdd
;~      _DayValueToDate($iJulianDate, $asDatePart[1], $asDatePart[2], $asDatePart[3])
;~  EndIf
    ; ====================================================
    ; adding Months
;~  If $sType = "m" Then
;~      $asDatePart[2] = $asDatePart[2] + $iValToAdd
;~      ; pos number of months
;~      While $asDatePart[2] > 12
;~          $asDatePart[2] = $asDatePart[2] - 12
;~          $asDatePart[1] = $asDatePart[1] + 1
;~      WEnd
;~      ; Neg number of months
;~      While $asDatePart[2] < 1
;~          $asDatePart[2] = $asDatePart[2] + 12
;~          $asDatePart[1] = $asDatePart[1] - 1
;~      WEnd
;~  EndIf
    ; ====================================================
    ; adding Years
;~  If $sType = "y" Then
;~      $asDatePart[1] = $asDatePart[1] + $iValToAdd
;~  EndIf
    ; ====================================================
    ; adding Time value
;~  If $sType = "h" Or $sType = "n" Or $sType = "s" Then
        $iTimeVal = _TimeToTicks($asTimePart[1], $asTimePart[2], $asTimePart[3]) / 1000
;~      If $sType = "h" Then $iTimeVal = $iTimeVal + $iValToAdd * 3600
;~      If $sType = "n" Then $iTimeVal = $iTimeVal + $iValToAdd * 60
        If $sType = "s" Then $iTimeVal = $iTimeVal + $iValToAdd
        ; calculated days to add
        $Day2Add = Int($iTimeVal / 86400) ; (24 * 60 * 60)
        $iTimeVal = $iTimeVal - $Day2Add * 86400 ; 24 * 60 * 60
        If $iTimeVal < 0 Then
            $Day2Add = $Day2Add - 1
            $iTimeVal = $iTimeVal + 86400 ; 24 * 60 * 60
        EndIf
        $iJulianDate = _DateToDayValue($asDatePart[1], $asDatePart[2], $asDatePart[3]) + $Day2Add
        ; calculate the julian back to date
        _DayValueToDate($iJulianDate, $asDatePart[1], $asDatePart[2], $asDatePart[3])
        ; caluculate the new time
        _TicksToTime($iTimeVal * 1000, $asTimePart[1], $asTimePart[2], $asTimePart[3])
;~  EndIf
    ; ====================================================
    ; check if the Input day is Greater then the new month last day.
    ; if so then change it to the last possible day in the month
    $iNumDays = StringSplit('31,28,31,30,31,30,31,31,30,31,30,31', ',')
    If _DateIsLeapYear($asDatePart[1]) Then $iNumDays[2] = 29
    ;
    If $iNumDays[$asDatePart[2]] < $asDatePart[3] Then $asDatePart[3] = $iNumDays[$asDatePart[2]]
    ; ========================
    ; Format the return date
    ; ========================
    ; Format the return date
    $sDate = $asDatePart[1] & '/' & StringRight("0" & $asDatePart[2], 2) & '/' & StringRight("0" & $asDatePart[3], 2)
    ; add the time when specified in the input
    If $asTimePart[0] > 0 Then
        If $asTimePart[0] > 2 Then
            $sDate = $sDate & " " & StringRight("0" & $asTimePart[1], 2) & ':' & StringRight("0" & $asTimePart[2], 2) & ':' & StringRight("0" & $asTimePart[3], 2)
        Else
            $sDate = $sDate & " " & StringRight("0" & $asTimePart[1], 2) & ':' & StringRight("0" & $asTimePart[2], 2)
        EndIf
    EndIf
    ;
    Return ($sDate)
EndFunc ;==>_DateAdd

Edited December 4, 2009 by Zedna

Zedna · December 4, 2009

Here is simplified SQLite version using only one table instead of two ones.

two optimizied date UDFs DateDiff,DateAdd not listed here - see post above ....

#include-once
#include <array.au3>
#Include <Date.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

Global $iRows, $iColumns;, $iRval

$start = TimerInit()

_SQLite_Startup ()
_SQLite_Open () ; Open a :memory: database
_SQLite_Exec (-1, "BEGIN;")
_SQLite_Exec (-1, "CREATE TABLE dates1 (date1);")
_SQLite_Exec (-1, "CREATE INDEX ix1 on dates1 (date1);")

;$StartDate = '2009-09-01 00:00:00'
;$EndDate = '2009-09-02 00:00:00'
dim $dates
$csv=StringSplit(FileRead(@ScriptDir&"\test.csv"),@CRLF,1)
$exitloop=0

for $i=1 to $csv[0]
    $date1=StringLeft($csv[$i],23)
    $date2=StringMid($csv[$i],25,23)

    $sql_cmd = ''
    
    $diff=DateDiff('s',$date1,$date2)
    for $d=1 to $diff
    $curDate=DateAdd('s',$d,$date1)
        $sql_cmd &= "INSERT INTO dates1 VALUES ('" & $curDate & "');"
    Next
    
    If $sql_cmd <> '' Then _SQLite_Exec (-1, $sql_cmd)
Next

_SQLite_Exec (-1, "COMMIT;")
_SQLite_GetTable2d (-1, "SELECT date1, count(*) FROM dates1 GROUP BY date1 ORDER BY date1;", $dates, $iRows, $iColumns)
_SQLite_Close()
_SQLite_Shutdown()

ConsoleWrite(TimerDiff($start) & @CRLF)
_ArrayDisplay($dates)
 
...

Edited December 4, 2009 by Zedna

Sign In

Help me speed this up.

Recommended Posts

MerkurAlex

MerkurAlex

Zedna

MerkurAlex

Zedna

Zedna

Zedna

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta