I posted some of this yesterday and was mostly focused on the memoryblock based class i was working on but I wanted to to see how it compared to using JSON both in terms of speed and size of the resulting string… I got thinking about this about a month back when Thom posted about his JSON testing…
I thought a MemoryBlock binary based solution in pure Xojo code might be able to be faster but to know if that was the case, I needed code to produce the JSON
Before yesterday I had never used JSON so I grabbed the RecordSet serialization method from Luna-Express to test against my code using both JSONItem Item and the JSONItem_MTC class, and then wrote a simple new framework method… And my memoryblock class compared reasonably to Xojo.Data.GenerateJSON and blew the others away in my metrics…
But the I realized that the structure of the JSON was naive and not comparable to what my class did… So I looked at the language reference and re-wrote them so they were more similar in content and structure with what my class did… It would require a little more coding on the client, but it should not be much more.
I will post the structured JSONItem And Xojo.Data.Generate code at the end… As I have not gotten to parsing yet (and my Memoryblock solution might be faster there) so I would appreciate it if those who are knowledgeable could take a look at the structured JSON code (new and old frame work) dnd let me know if they see any issues with it.
The test data was from an Sqlite Database view. The recordset had 37 fields - a mixture of strings and numbers with one field NULL in every record… It had 20,000 records to convert… (the DB is from an app I wrote at work to analyze data that helped me get a raise!) There were no binary fields but i want to support them as well… if there are enough of those I suspect the memoryblock class would produce the smallest strings.
So here are the results:
Method StringSize (MB) Time Sec GZipedSize (MB)
MemoryBlock 7.18 1.85 1.44
Naive JSON Code
JSONItem 14.95 124.67 1.55
JSONItem_MTC 14.95 12.42 1.55
Xojo.Data 14.95 1.67 1.58
Structured JSON Code
JSONItem 7.24 28.96 1.33
JSONItem_MTC 7.24 6.30 1.33
Xojo.Data 7.46 0.91 1.40
With the structured JSON, the size advantage of the memoryblock class almost completely disappeared . Speed wise it still beats the JSONtem and JSONitem_MTC classes by a large amount … But now the Xojo.Data.GenerateJSON is twice as fast as the memoryblock Class! I may keep both the memoryblock and a JSON method and let the client app request the format that works best for a given situation.
BTW having to deal with the xojo.framework reminded me why I disliked it so much!
Hopefully JSONItem’s speed will be improved so it matches that of Xojo.Data.GenerateJSON!
JSONItem and JSONItem_MTC code:
[code]Dim Startms as Double = Microseconds
'Create JSON for the different sections
Dim SetData As New JSONItem_MTC ’ Holds
Dim FieldNameArray As New JSONItem_MTC
Dim FieldTypeArray As New JSONItem_MTC
Dim RecordArray As New JSONItem_MTC
’ Convert field information to JSON and assign to SetData
Dim ubFields as Integer = RS.FieldCount -1, i as Integer
Dim TypeList(), theType as Integer
Redim TypeList(ubFields)
for i = 0 to ubFields
Dim FieldNameObj As New JSONItem_MTC
FieldNameObj.Value(RS.IdxField(i+1).Name) = i
FieldNameArray.Append FieldNameObj
thetype = RS.ColumnType(i)
TypeList(i) = theType
FieldTypeArray.Append thetype
Next
SetData.Value(“FieldNames”) = FieldNameArray
SetData.Value(“FieldTypes”) = FieldTypeArray
FieldNameArray = NIL
FieldtypeArray = NIL
’ Process the records
While Not RS.EOF
Dim theRecord As New JSONItem_MTC
For i = 0 To ubFields
Select Case TypeList(i)
Case 14,15,16 ’ Binary types
Dim theValue As Variant = RS.IdxField(i+1).Value
if theValue.IsNull Then
theRecord.Append theValue
Else
theRecord.Append Base64Encode(theValue)
End if
' may need special treatment for other data types
Else
theRecord.Append RS.IdxField(i+1).Value
End Select
Next
RecordArray.Append theRecord
RS.MoveNext
Wend
SetData.Value(“Records”) = RecordArray
'Convert to String
Dim StrVal As String = SetData.ToString
'Report time and size information
Dim Duration as Double = Microseconds - Startms
Dim Size As UInt32 =StrVal.LenB
Dim ZipedSz As UInt32 = GZip(StrVal).LenB
Dim ZDuration as Double = Microseconds - Startms
// Close the recordset.
If Close Then
RS.Close
End If
Dim C as New Clipboard
C.text = Str(Size) + Chr(9)+ Str(Duration) + Chr(9) + Str(ZipedSz)+ Chr(9) + Str(ZDuration)
C.Close[/code]
Here is the Xojo.Data.GenerateJSON code:
Dim Startms as Double = Microseconds
Dim SetData As New Xojo.Core.Dictionary
Dim FieldNameDic As New Xojo.Core.Dictionary
Dim TypeList() As Integer
Dim RecordArray() As Auto
' Convert field information to JSON and assign to SetData
Dim ubFields as Integer = RS.FieldCount -1, i as Integer
Redim TypeList(ubFields)
for i = 0 to ubFields
FieldNameDic.Value(RS.IdxField(i+1).Name) = i
TypeList(i) = RS.ColumnType(i)
Next
SetData.Value("FieldNames") = FieldNameDic
SetData.Value("FieldTypes") = TypeList
FieldNameDic = NIL
While Not RS.EOF
Dim theRecord() As Auto
For i = 0 To ubFields
Select Case TypeList(i)
Case 14,15,16 ' Binary types
Dim theValue As Variant = RS.IdxField(i+1).Value
if theValue.IsNull Then
theRecord.Append NIL
Else
theRecord.Append Base64Encode(theValue)
End if
' may need special treatment for other data types
Else
theRecord.Append RS.IdxField(i+1).Value
End Select
Next
RecordArray.Append theRecord
RS.MoveNext
Wend
SetData.Value("Records") = RecordArray
Dim StrVal As String = Xojo.Data.GenerateJSON(SetData)
Dim Duration as Double = Microseconds - Startms
Dim Size As UInt32 =StrVal.LenB
Dim ZipedSz As UInt32 = GZip(StrVal).LenB
// Close the recordset.
If Close Then
RS.Close
End If
Dim C as New Clipboard
C.text = Str(Size) + Chr(9)+ Str(Duration) + Chr(9) + Str(ZipedSz)
C.Close