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
