Dear All,
I´m having a db with 417 rows and 19 columns, where 300 websocket inputs a sec (approx.) are being used to update the db + some processing is taking place, see code. I noticed that the size of java.exe is growing and growing, which I´m pretty sure is because of the SQLite db. The db is running in the memory (which is way more smooth), but when I tried using a file instead, it´s only taking up 50 kb of space, so it´s not the db table itself that causes this.
My idea is that so much is going on in SQLite that it cannot maintain the cache properly or something like that.
It sits for about ½ an hour at around 1 GB, then jumps to 2 GB and suddenly to 4 GB. I have seen it reaching 8 GB after 3 hours.
I could make a workaround, where the db gets cleared every hour or so, which would not be a big issue for the functionality, but I would rather that this memory problem gets fixed.
Any ideas?
The map that´s sent to insert_livedata contains parsed data from the websocket message.
I´m having a db with 417 rows and 19 columns, where 300 websocket inputs a sec (approx.) are being used to update the db + some processing is taking place, see code. I noticed that the size of java.exe is growing and growing, which I´m pretty sure is because of the SQLite db. The db is running in the memory (which is way more smooth), but when I tried using a file instead, it´s only taking up 50 kb of space, so it´s not the db table itself that causes this.
My idea is that so much is going on in SQLite that it cannot maintain the cache properly or something like that.
It sits for about ½ an hour at around 1 GB, then jumps to 2 GB and suddenly to 4 GB. I have seen it reaching 8 GB after 3 hours.
I could make a workaround, where the db gets cleared every hour or so, which would not be a big issue for the functionality, but I would rather that this memory problem gets fixed.
Any ideas?
The map that´s sent to insert_livedata contains parsed data from the websocket message.
B4X:
Sub Insert_livedata (m As Map)
Dim pair As String = m.Get("pair")
Dim rs As ResultSet
Dim diff As Double
Dim rowid As Int
sql3.ExecNonQuery2("UPDATE table2 Set p1a = ?, p1b = ?, p1av = ?, p1bv = ? WHERE p1 = ?", Array As Object(m.Get ("a"), m.Get("b"), m.Get ("av"),m.Get("bv"),p))
sql3.ExecNonQuery2("UPDATE table2 Set p2a = ?, p2b = ?, p2av = ?, p2bv = ? WHERE p2 = ?", Array As Object(m.Get ("a"), m.Get("b"), m.Get ("av"),m.Get("bv"),p))
sql3.ExecNonQuery2("UPDATE table2 Set p3a = ?, p3b = ?, p3av = ?, p3bv = ? WHERE p3 = ?", Array As Object(m.Get ("a"), m.Get("b"), m.Get ("av"),m.Get("bv"),p))
rs = sql3.ExecQuery2("SELECT rowid FROM table2 WHERE (p1 = ?) OR (p2 = ?) OR (p3 = ?)", Array As String(p, p, p))
Do While rs.NextRow
rowid = (rs.GetInt ("rowid"))
Dim diffmap As Map = Find_difference (rowid)
Update_diff (diffmap,rowid)
Loop
rs.Close
End Sub
Sub Update_diff (m As Map,rowid As Int)
Dim diff As Double = m.Get("diff")
Dim p1 As String = m.Get("p1")
Dim p2 As String = m.Get("p2")
Dim p3 As String = m.Get("p3")
Dim logic As String = sql3.ExecQuerySingleResult2 ("SELECT logic FROM table2 WHERE rowid = ?", Array As String (rowid))
Dim c1 As String = sql3.ExecQuerySingleResult2 ("SELECT p1 FROM table2 WHERE rowid = ?", Array As String (rowid))
Dim c2 As String = sql3.ExecQuerySingleResult2 ("SELECT p2 FROM table2 WHERE rowid = ?", Array As String (rowid))
Dim c3 As String = sql3.ExecQuerySingleResult2 ("SELECT p3 FROM table2 WHERE rowid = ?", Array As String (rowid))
diff = NumberFormat2(diff,1,8,8,False)
Dim p As String = rowid & ": " & c1 & "/" & c2 & "/" & c3
sql3.ExecNonQuery2 ("UPDATE table2 Set difference = ? WHERE rowid = ?", Array As Object (diff, rowid))
Dim writestring As String = (p & " - " & " p1a: " & p1 & " p2b: " & p2 & " p3a: " & p3 & " " & sql3.ExecQuerySingleResult ("SELECT difference FROM table2 WHERE rowid = " & (rowid)))
tx.WriteLine (DateTime.Time(DateTime.Now) & " " & writestring)
End Sub
Sub Find_difference (rowid As Int) As Map
Dim diff As Double
Dim p1 As Double
Dim p2 As Double
Dim p3 As Double
Dim logic As String
logic = sql3.ExecQuerySingleResult2 ("SELECT logic FROM table2 WHERE rowid = ?", Array As String (rowid))
If logic.StartsWith ("da") Then
p1 = sql3.ExecQuerySingleResult2 ("SELECT p1a FROM table2 WHERE rowid = ?", Array As String (rowid))
Else
p1 = sql3.ExecQuerySingleResult2 ("SELECT p1b FROM table2 WHERE rowid = ?", Array As String (rowid))
End If
If logic.SubString2(2,4) = "da" Then
p2 = sql3.ExecQuerySingleResult2 ("SELECT p2a FROM table2 WHERE rowid = ?", Array As String (rowid))
Else
p2 = sql3.ExecQuerySingleResult2 ("SELECT p2b FROM table2 WHERE rowid = ?", Array As String (rowid))
End If
If logic.EndsWith ("da") Then
p3 = sql3.ExecQuerySingleResult2 ("SELECT p3a FROM table2 WHERE rowid = ?", Array As String (rowid))
Else
p3 = sql3.ExecQuerySingleResult2 ("SELECT p3b FROM table2 WHERE rowid = ?", Array As String (rowid))
End If
diff = NumberFormat2(p1*p2*p3,1,8,8,False)
Dim m As Map
m.Initialize
m.Put ("diff",diff)
m.Put ("p1", p1)
m.Put ("p2", p2)
m.Put ("p3", p3)
Return m
End Sub
Last edited: