Valentina SQLite Server bench tests

@Ruslan Zasukhin

I recently switch to Valentina SQLite Server.
I did some speed test on several different databases and was impressed with the speed of Valentina.
I’m posting my results here for anyone that may be interested.

Here is a link to the Valentina forum showing the details of my testing.

And here is a summary of my test results, inserting 2095 records with 7 columns.
*1 SQLite flat file on local network 0.28 seconds
*2 Valentina SQLite Server 0.55 seconds
*3 CubeSQL about 2-3 seconds
*4 PostgreSQL 6 seconds
*5 MySQL 46 seconds

Quite a wide variation in speed using the same computers and tables.

My only concern with Valentina SQLite Server is a memory leak that occurs and leads to freeze ups. I have taken care of the problem by disconnecting and reconnecting. This seems to free up the memory.

Even though I haven’t tried their SQLIte server, I know first hand how fast Valentina is so I’m not surprised their SQLite server is quick.

I thought the timing for PostgreSQL was a bit odd so I tested it myself. To insert 2095 records takes a little under a second, and that’s testing on PSQL 9.5.x on my laptop running on battery and in the IDE. I wonder what the difference is and whether it points to some problem with your test suite as a whole?

The code I used:

dim sql as string

sql = "DROP TABLE qbitems"
db.SQLExecute sql

sql = "CREATE TABLE QBItems (Item TEXT PRIMARY KEY UNIQUE, " + _
	"Type TEXT, Description TEXT, " + _
	"Price NUMERIC(12,2), TaxCode TEXT, Category Text, " + _
	"CatID Integer, SubItem TEXT)"
db.SQLExecute sql
AddToResult db.ErrorMessage

//
// Create data
//
dim recs() as DatabaseRecord

for i as integer = 1 to 2095
	dim data as new DatabaseRecord
	data.Column( "item" ) = chr( i + 64 )
	data.Column( "type" ) = "some type"
	data.Column( "description" ) = "some description " + str( i )
	data.DoubleColumn( "price" ) = i / 7.0
	data.Column( "category" ) = "category " + str( i )
	data.Column( "taxcode" ) = "tax code"
	data.IntegerColumn( "catid" ) = i
	data.Column( "subitem" ) = "sub item"

	recs.Append data
next

dim msg as string
dim sw as new Stopwatch_MTC
sw.Start

for each rec as DatabaseRecord in recs
	db.InsertRecord "qbitems", rec
next

sw.Stop
msg = format( sw.ElapsedMicroseconds, "#," ) + " microsecs"
AddToResult msg

db.Close

BTW, inside a transaction, it takes less than half a second.

A little something to add for PostgreSQL.

You dont mention if you are using your own program code with PostgreSQL or using the pgAdmin application for testing purposes.

pgAdminIII and the most recent pgAdminIV seem to run horrendously slowly; I’ve noticed this across OSX, Windows and Linux.

Retrieving all lines of an existing table for example, takes around 7 - 10 seconds depending which OS. Said table has a little over 5000 rows. The same query in Xojo code takes nowhere near that amount of time.

Stephen, this was Xojo code.

instead of pgAdmin you can try our Valentina Studio with postgreSQL.

[quote=305389:@Ruslan Zasukhin]Stephen, this was Xojo code.

instead of pgAdmin you can try our Valentina Studio with postgreSQL.[/quote]

Yep, I have valentina studio and do use it. pgAdmin is something I use for quick and dirty checking/testing/setup of the pgsql system itself. I just noticed huge performance hits when using pgAdmin thats all.

[quote=305367:@Neil Burkholder]
My only concern with Valentina SQLite Server is a memory leak that occurs and leads to freeze ups.[/quote]

It does not matter how fast it is, if it is unstable it is about as useful as a chocolate teapot. In 2014 and 2015, I tested and tossed it for that reason. I’ve no interest in a database server that cannot stay up and remain functional 7x24.

Hello @James Dooley,

did you try the newer Versions? (V6.x).

I have one Running on my Server (and 1 on my PasPi (BananaPI Pro)) and it is running stable 24x7.

Ok. i must say it is only my one of my own Faktura Software (still in Development) and another Test Software for a Customer running on these Server’s at the Moment with medium to small load. And all is running Stable.

PS: I use the Valentina DB not it’s SQLite Functionality.

[quote=305396:@RenLandscheidt]Hello @JamesDooley,

did you try the newer Versions? (V6.x).

I have one Running on my Server (and 1 on my PasPi (BananaPI Pro)) and it is running stable 24x7.

Ok. i must say it is only my one of my own Faktura Software (still in Development) and another Test Software for a Customer running on these Server’s at the Moment with medium to small load. And all is running Stable.

PS: I use the Valentina DB not it’s SQLite Functionality.[/quote]

Not extensively no. It was in the Omegabundle bundle so I put it running writing 15k records from 3 virtual workstations and it did not stay up. I ignored it, I did not investigate it.

This morning I tested MySQL. I had to change the table definition a bit (see below), but those inserts inside a transaction took about 250 ms under the same conditions. (Well, now powered, not on battery, but the PSQL times were about the same as last night.)

sql = "CREATE TABLE QBItems (Item VARCHAR(50) PRIMARY KEY UNIQUE, " + _
	"Type VARCHAR(50), Description VARCHAR(255), " + _
	"Price NUMERIC(12,2), TaxCode VARCHAR(50), Category VARCHAR(50), " + _
	"CatID Integer, SubItem VARCHAR(50))"

Thanks
I was using a prepared statement inside a transaction. Using your method the result (PostgreSQL) was 0.33 seconds.
Sorry I can’t edit my original post anymore.

Is InsertRecord the same as a prepared statement in that you can add @#$\’" (junk)?

Yes, it’s just another way to do it.

A much faster way in this case.

Anybody have some input about MySQL? I tried Kem’s method with almost the same results 46 seconds. Why so slow? These tests were all using Xojo plugins.

Can you post your test code?

I never use the InsertRecord way because with SQLite it is many, many times slower.
Insert takes 133 seconds using this method with Valentina SQLite Server.
Different strokes for different databases I guess.

Something odd is going on. I just tested my code against Valentina SQLite Server and came up with 100 ms.

Here is my test which really is redundant because I never really delete all order lines in my program. I just used this because is was a table with more columns.

My test project is kind of cluttered. I tried a lot of things and didn’t bother to clean up.
test.db
whole gory project

Get data into memory:

[code]Sub Action()
db = new SQLiteDatabase
if not DB.Connect then msgbox “Error opening QB.DB”
db.SQLExecute(“CREATE TABLE OrderLines (ID INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,OrderID INTEGER,Idx INTEGER,Item TEXT,Quantity REAL,” _

  • "Description TEXT,Price REAL,Tax TEXT,Category TEXT,LF TEXT, Status INTEGER,ProductionID INTEGER,QBID TEXT);")

db1 = new SQLiteDatabase
Dim dbFile As New FolderItem(“templocation\test.db”)
db1.DatabaseFile = dbFile
if db1.Connect then
end if

if db1 <> nil Then
dim t as Integer = Ticks
dim rs as RecordSet = db1.SQLSelect(“SELECT * FROM OrderLinesTest”)
t = Ticks - t
dim cnt as String = rs.RecordCount.ToText
if rs <> Nil and rs.RecordCount>0 Then
do
Dim row As New DatabaseRecord
for i as Integer = 1 to rs.FieldCount
row.Column(rs.IdxField(i).Name) = rs.IdxField(i).StringValue
next
db.InsertRecord(“OrderLines”, row)
rs.MoveNext
loop until rs.EOF
rs = db.SQLSelect(“SELECT * FROM OrderLines”)
if rs <> nil Then MsgBox rs.RecordCount.ToText + “, " + Format(t/60,”#0.00") + ", " + cnt
end if
end if
End Sub
[/code]

Write to PostgreSQL:

[code]Sub Action()
dim pDb as PostgreSQLDatabase
pDb = New PostgreSQLDatabase

pDb.Host = “localhost”
pDb.UserName = “postgres”
pDb.Password = “QBMS123”
pDb.DatabaseName = “Test”

If pDb.Connect Then
Else
Return
End If
pDb.SQLExecute(“BEGIN TRANSACTION”)
pdb.SQLExecute(“DROP TABLE OrderLinesTest”)
pdb.SQLExecute(“CREATE TABLE OrderLinesTest (ID Integer DEFAULT nextval(’”“table1_ID_seq”"’::regclass) NOT NULL,OrderID INTEGER,Idx INTEGER,Item TEXT,Quantity REAL," _

  • “Description TEXT,Price REAL,Tax TEXT,Category TEXT,LF TEXT, Status INTEGER,ProductionID INTEGER,QBID TEXT);”)

if pDb <> nil Then
dim t as Integer = Ticks
dim rs as RecordSet = db.SQLSelect(“SELECT * FROM OrderLines”)
't = Ticks - t
dim cnt as String = rs.RecordCount.ToText
dim c1 as Integer
if rs <> Nil and rs.RecordCount>0 Then
dim recs() as DatabaseRecord
do
dim data as new DatabaseRecord
data.IntegerColumn( “ID” ) = rs.Field(“ID”).IntegerValue
data.IntegerColumn( “OrderID” ) = rs.Field(“OrderID”).IntegerValue
data.IntegerColumn( “Idx” ) = rs.Field(“Idx”).IntegerValue
data.Column(“Item”) = rs.Field(“Item”).StringValue
data.DoubleColumn( “Quantity” ) = rs.Field(“Quantity”).DoubleValue
data.Column(“Description”) = rs.Field(“Description”).StringValue
data.DoubleColumn(“Price”) = rs.Field(“Price”).DoubleValue
data.Column(“Tax”) = rs.Field(“Tax”).StringValue
data.Column(“Category”) = rs.Field(“Category”).StringValue
data.Column(“LF”) = rs.Field(“LF”).StringValue
data.IntegerColumn(“Status”) = rs.Field(“Status”).IntegerValue
data.IntegerColumn(“Status”) = rs.Field(“Status”).IntegerValue
data.Column(“QBID”) = rs.Field(“QBID”).StringValue
recs.Append data
rs.MoveNext
loop until rs.EOF

  for each rec as DatabaseRecord in recs
    pdb.InsertRecord "OrderLinesTest", rec
  next
  rs = pDb.SQLSelect("SELECT * FROM OrderLinesTest")
  if rs <> nil Then MsgBox rs.RecordCount.ToText + ", " + Format((ticks-t)/60,"#0.00") + ", " + cnt
  
end if

end if
pDb.Commit

End Sub
[/code]

Is PostgreSQL free for enterprise distribution with app? Say an install for a client with 25 intranet users. I couldn’t find any pricing or purchase options on their website.

Postgres is free. Like free, free.

Your Postgres test looks fine. How is it different from the SQLite Server test?

I thought it was the same. I added the progress bar code so I could tell if it was trying. This adds less than a second.

[code]
mdb.SQLExecute(“BEGIN TRANSACTION”)
mdb.SQLExecute(“DROP TABLE OrderLinesTest”)
mdb.SQLExecute(“CREATE TABLE OrderLinesTest (ID INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,OrderID INTEGER,Idx INTEGER,Item TEXT,Quantity double,” _

  • "Description TEXT,Price double,Tax TEXT,Category TEXT,LF TEXT, Status INTEGER,ProductionID INTEGER,QBID TEXT);")

if mdb <> nil Then
dim t as Integer = Ticks
dim rs as RecordSet = db.SQLSelect(“SELECT * FROM OrderLines”)
't = Ticks - t
dim cnt as String = rs.RecordCount.ToText
dim c1 as Integer
if rs <> Nil and rs.RecordCount>0 Then
dim recs() as DatabaseRecord
do
dim data as new DatabaseRecord
data.IntegerColumn( “ID” ) = rs.Field(“ID”).IntegerValue
data.IntegerColumn( “OrderID” ) = rs.Field(“OrderID”).IntegerValue
data.IntegerColumn( “Idx” ) = rs.Field(“Idx”).IntegerValue
data.Column(“Item”) = rs.Field(“Item”).StringValue
data.DoubleColumn( “Quantity” ) = rs.Field(“Quantity”).DoubleValue
data.Column(“Description”) = rs.Field(“Description”).StringValue
data.DoubleColumn(“Price”) = rs.Field(“Price”).DoubleValue
data.Column(“Tax”) = rs.Field(“Tax”).StringValue
data.Column(“Category”) = rs.Field(“Category”).StringValue
data.Column(“LF”) = rs.Field(“LF”).StringValue
data.IntegerColumn(“Status”) = rs.Field(“Status”).IntegerValue
data.IntegerColumn(“Status”) = rs.Field(“Status”).IntegerValue
data.Column(“QBID”) = rs.Field(“QBID”).StringValue
recs.Append data
rs.MoveNext
loop until rs.EOF

  c1 = 0
  ProgressBar1.Maximum = 2095
  for each rec as DatabaseRecord in recs
    c1 = c1 + 1
    ProgressBar1.Value = c1
    mdb.InsertRecord "OrderLinesTest", rec
  next
  
  rs = mdb.SQLSelect("SELECT * FROM OrderLinesTest")
  mdb.Commit
  if rs <> nil Then MsgBox rs.RecordCount.ToText + ", " + Format((ticks-t)/60,"#0.00") + ", " + cnt
  
end if

end if
mdb.Commit[/code]