Have tested and indeed a lot faster now.
Again the same test, so 65000 records subtracting the same 2 fields:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 0.27 secs
RBS
On Sun, Oct 7, 2012 at 10:23 PM, Olaf Schmidt <s...@online.de> wrote:
> Am 06.10.2012 19:38, schrieb Bart Smissaert:
>
>> Times I get (65000 records, subtracting 2 fields defined as text in
>> the same table)
>> gives me following times:
>> method with julianday 0.4 secs
>> method with unixepoch 0.6 secs
>> using ctime etc. via VB wrapper 1.2 secs
>
>
> What? A VB-implemented User-Defined-Function slower than
> a built-in C-function? Now, there's a challenge... ;-)
>
> Since I'm in the last stages for a new wrapper-
> version (RichClient5) - I've tried to speed these
> two functions up already in the new Binary (FWIW).
>
> Wasn't all that difficult, because the former VB.Runtime-
> function (Format$,... yes, I was lazy), which internally
> was playing a major role in these conversions, is not
> the fastest and leaves enough room for improvement.
>
> So, after optimization the UDFs CTime/CDbl are now about
> twice as fast as the time/julianday-functions.
>
> If you want to try it out, here's a download of the
> current snapshot of the new wrapper:
> www.datenhaus.de/Downloads/vbRC5BaseDlls.zip
>
> After registering you should be able to run the following
> testcode (TestTable contains 86400 "increasing seconds").
>
> Tried to avoid the selection of too many records in the
> test-query (only a single one is returned), so that the
> test can run more or less completely inside SQLites VDBE.
>
> The printed results, after scanning over 86400 records are:
> Count: 1 Timing VB-UDF: 0,049s
> Count: 1 Timing SQLite: 0,090s
>
> ----------- used table-layout and VB-testcode ---------------
>
> '*Into a Form, then click the Form
> Option Explicit
>
> Private MemDB As New cMemDB, Rs As cRecordset, T!
>
> Private Sub Form_Load()
> With MemDB.NewFieldDefs
> .Add "ID Integer Primary Key"
> .Add "T1 Text"
> .Add "T2 Text"
> MemDB.CreateTable "T"
> End With
>
> With MemDB.CreateCommand("Insert Into T Values(@ID,@T1,@T2)")
> MemDB.BeginTrans
> Dim i As Long
> For i = 0 To 86400 - 1 '<-one day (increasing seconds in T2)
> .SetNull !ID
> .SetText !T1, "00:00:00"
> .SetText !T2, Format$(i / 86400, "hh:mm:ss")
> .Execute
> Next i
> MemDB.CommitTrans
> End With
>
> End Sub
>
> Private Sub Form_Click()
> Const Where1 = "CTime(CDbl(T2) - CDbl(T1)) >= '23:59:59'"
> Const Where2 = "time(julianday(T2) - julianday(T1) - .5) >= '23:59:59'"
>
> T = Timer
> Set Rs = MemDB.GetTable("T", Where1)
> T = Timer - T
> Print "Count:"; Rs.RecordCount, "Timing VB-UDF: "; Format(T, "0.000s")
>
> T = Timer
> Set Rs = MemDB.GetTable("T", Where2)
> T = Timer - T
> Print "Count:"; Rs.RecordCount, "Timing SQLite: "; Format(T, "0.000s")
>
> Print
> End Sub
>
> Olaf
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
