Valentina SQLite Server bench tests

  1. ‹ Older
  2. 3 years ago

    Kem T

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

    I've posted my project here:

    https://dl.dropboxusercontent.com/u/26920684/PSQL%20Test.xojo_binary_project

    You just have to fill in the appropriate credentials for your DB servers in the appropriate places.

  3. Would be interesting to see everyones results.

    I was able to test 2 of the options here ( I currently only have pgsql setup), so pgsql over the local network and flat file sqlite :

    pgsql result : 896068

    sqlite result : 24672

    Both were an average of 6 runs.

  4. Running the pgsql option on the db server itself instead of going over the network (Used 'localhost') it changed to an average of 301506.

  5. Hi Kem,

    Kem Tekinay: 100 ms for Valentina SQLite Server. Very impressive, but expected.

    So 100ms is better of postgre?

    I see that nobody yet try VDB.

    I see that so far only inserts are tested. Why?
    Inserts should be similar for most dbs. Because HDD plays the main role saving N Mb.

  6. Kem T

    25 Dec 2016 Pre-Release Testers, Xojo Pro, XDC Speakers Connecticut

    On my laptop postgres has shown the worst performance, but I also suspect there is something about my installation that's making it slower generally.

    I tried Valentina yesterday but couldn't get it going, then ran out of time.

  7. Neil B

    26 Dec 2016 Pre-Release Testers

    PostgreSQL doesn't use AUTOINCREMENT?

  8. Jens K

    26 Dec 2016 Pre-Release Testers, Xojo Pro Europe (Germany)
    Edited 3 years ago

    Use bigserial as primary key - then there is autoincrement.

  9. Neil B

    26 Dec 2016 Pre-Release Testers

    Here is a summary of the conversion steps required. Not simple for my size project. 45 tables. About 8 hours start to finish.

    1. Convert database - 3 hours to write conversion utility.
    2. Change all prepared statements to use $1,$2 instead of ?,?.
    3. changed CURRENT_TIMESTAMP to 'now()'
    4. changed some problems with data types ie. Round aggregate doesn't work with type real.
    5. multi-table queries need more of the fields added to the ORDER BY clause
  10. Jean-Yves P

    26 Dec 2016 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    be very careful with the data types. sqlite is very weak about it, and postgres is very strict.
    a query can run ( and give good results) in sqlite, and completely fail in postgres.
    also write method to convert one db to the other, dont do it by hand.
    you will likely do it more than one time ( and more than one database).

  11. Neil B

    26 Dec 2016 Pre-Release Testers

    I did create a conversion program first thing. As you said data types was the biggest hurdle. SQLite would even let you enter 1.5 into an integer field.

    The obstacle I'm hung up on at the moment is AUTOINCREMENT. I converted using Serial as a replacement. The problem is that if an ID is specified when inserting like "INSERT table(ID,Test) Values(Max(ID)+1, 'test')" the serial number isn't incremented. So in this case next time an insert is done the ID is less than the max ID. I tried using "ID INTEGER DEFAULT MAX(ID + 1)" but get an error saying an aggregate can't be used for a default value.

    I'm thought about adding a trigger to increase the serial next value whenever a record is inserted, but I'm not sure how.

  12. Jens K

    26 Dec 2016 Pre-Release Testers, Xojo Pro Europe (Germany)
    Edited 3 years ago

    Why do you want to write a value into 'ID' (probably the primary key). Don't write anything in it and everything will be ok. (If your datatype is bigserial - but serial should also be possible, but I never have used it for that purpose.)

    Or do I missunderstand the intention?

  13. Jean-Yves P

    26 Dec 2016 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    you must import your tables with the values of your ID
    then after the import process, set the sequences values to max(id) +1 and then the autoincrement process can start on its own

  14. Jean-Yves P

    26 Dec 2016 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    serial transforms to an int4 with an autoincrement sequence
    bigserial gives an int8 with autoincrement

  15. Jean-Yves P

    26 Dec 2016 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    the default value of the primarykey field must be : nextval('your_sequence_name'::regclass)

  16. Neil B

    26 Dec 2016 Pre-Release Testers

    OK. I'm converting an existing database. I must specify id to retain integrity. There are 'holes' in the sequence because of previously deleted records. What would be wrong with creating a function to check for max(id) and use the function as the default value? Would that slow down inserts due the query?

  17. Jens K

    26 Dec 2016 Pre-Release Testers, Xojo Pro Europe (Germany)

    For the conversion you don't want and don't need the autoincrement. Ok.

    But for "normal" use you could take the autoincrement of bigserials (automatically done without writing the id). Why do you want a function do do that?

  18. Neil B

    26 Dec 2016 Pre-Release Testers

    @Jean-YvesPochez you must import your tables with the values of your ID
    then after the import process, set the sequences values to max(id) +1 and then the autoincrement process can start on its own

    I've not been able the set the sequence start using a query, only an actual integer. I tried this:

    'this works
    ALTER SEQUENCE categories_id_seq START 10
    
    'but not this
    ALTER SEQUENCE categories_id_seq START SELECT MAX(ID)+1 FROM Categories
    
    'or this
    ALTER SEQUENCE categories_id_seq START (SELECT MAX(ID)+1 FROM Categories)
    
  19. Jean-Yves P

    26 Dec 2016 Pre-Release Testers, Xojo Pro Europe (France, Besançon)
    Edited 3 years ago

    I do it with xojo.
    read the max id value from the first database
    make a string with the desired value as a query to the second database

    rs1 = db1.sqlselect "select max(id)+1 from categories"
    mymaxid = rs1.idxfield(1).integervalue
    db2.sqlexecute "alter sequence categories_id_seq start "+str(mymaxid)
  20. Neil B

    26 Dec 2016 Pre-Release Testers

    That worked.

    I started a new topic as I've gotten this one pretty far off topic.

or Sign Up to reply!