Valentina SQLite Server bench tests

  1. 2 years ago

    Neil B

    23 Dec 2016 Pre-Release Testers

    @Ruslan Z

    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.

  2. Kem T

    23 Dec 2016 Pre-Release Testers, Xojo Pro, XDC Speakers New York

    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
  3. Kem T

    23 Dec 2016 Pre-Release Testers, Xojo Pro, XDC Speakers New York

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

  4. 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.

  5. Stephen, this was Xojo code.

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

  6. @Ruslan Z Stephen, this was Xojo code.

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

    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.

  7. James D

    24 Dec 2016 Pre-Release Testers, Xojo Pro Europe (Switzerland)

    @Neil B My only concern with Valentina SQLite Server is a memory leak that occurs and leads to freeze ups .

    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.

  8. René L

    24 Dec 2016 Pre-Release Testers Ratingen, Duesseldorf, Germany
    Edited 2 years ago

    Hello @James D,

    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.

  9. James D

    24 Dec 2016 Pre-Release Testers, Xojo Pro Europe (Switzerland)

    @RenéLandscheidt Hello @James D,

    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.

    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.

  10. Kem T

    24 Dec 2016 Pre-Release Testers, Xojo Pro, XDC Speakers New York

    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))"
  11. Neil B

    24 Dec 2016 Pre-Release Testers

    @Kem T 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:

    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)?

  12. Kem T

    24 Dec 2016 Pre-Release Testers, Xojo Pro, XDC Speakers New York

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

  13. Neil B

    24 Dec 2016 Pre-Release Testers

    @Kem T 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.

  14. Kem T

    24 Dec 2016 Pre-Release Testers, Xojo Pro, XDC Speakers New York

    Can you post your test code?

  15. Neil B

    24 Dec 2016 Pre-Release Testers

    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.

  16. Kem T

    24 Dec 2016 Pre-Release Testers, Xojo Pro, XDC Speakers New York

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

  17. Neil B

    24 Dec 2016 Pre-Release Testers

    @Kem T Can you post your test code?

    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:

    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

    Write to PostgreSQL:

    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
  18. Neil B

    24 Dec 2016 Pre-Release Testers

    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.

  19. Kem T

    24 Dec 2016 Pre-Release Testers, Xojo Pro, XDC Speakers New York

    Postgres is free. Like free, free.

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

  20. Neil B

    24 Dec 2016 Pre-Release Testers

    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.

       
      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
  21. Newer ›

or Sign Up to reply!