Sign in to follow this  
Followers 0
MerkurAlex

Help me speed this up.

7 posts in this topic

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 :)


[quote name='PsaltyDS' post='635433' date='Jan 27 2009, 07:04 AM']Larry is a mass murderer?! It's always the quiet, clean cut, bald guys... [/quote]

Share this post


Link to post
Share on other sites



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...


[quote name='PsaltyDS' post='635433' date='Jan 27 2009, 07:04 AM']Larry is a mass murderer?! It's always the quiet, clean cut, bald guys... [/quote]

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

@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 :)


[quote name='PsaltyDS' post='635433' date='Jan 27 2009, 07:04 AM']Larry is a mass murderer?! It's always the quiet, clean cut, bald guys... [/quote]

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by Zedna

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by Zedna

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0