Bench Valentina Sqlite Server vs cubeSQL. x3 times faster.

Hi Xojo Devs,

We have spend some time making simple Xojo project which do N inserts into:
a) Valentina SQLite LOCAL db using our copy in V4RB;
b) Valentina Sqlite SERVER;
c) cubeSQL Sqlite SERVER;

It has simplest Table T1 with 2 integer fields f1 and f2. No indexes during inserts.

Loop is wrapped by transaction, providing the best speed of inserts for SQLite. Also PrepareStatement was used.

Idea of this bench is:

  1. to see overhead of Server solutions over the LOCAL sqlite engine.
  2. compare Valentina Sqlite Server vs CubeSQL server. Compare first of all client-server-protocol overhead. For this we use small SQL commands, which should eat near to zero time from engine itself, so we can see overhead of server.

Results for 100K records INSERTS, for LOCALHOST servers (Macbook, i7, 8GB):

  1. LOCAL SQLite – about 0.5 - 1 second on different runs.
  2. Valentina Sqlite Server – about 20 seconds.
  3. cubeSQL Server – about 65-70 seconds.

Results for 100K SELECTs, each SELECT finds 1 record.

  1. LOCAL SQLite – about 3-4 seconds
  2. Valentina Sqlite Server – about 23 seconds.
  3. cubeSQL Server – about 60 seconds.

P.S. Valentina Server and cubeSQL both use WAL mode of SQLite engine, and shared cache mode.
P.S.2 Also I can add that both servers are installed with their default settings. There is no any special tuning.
P.S.3 This project will be in the V4RB/Examples/Advanced folder, staring from the next our beta. So everybody can check this easy if install both servers.

TODO: to run this against servers on local network, using Wifi (the slower the better).
TODO: may be we will add mySQL and postgreSQL using Xojo classes.

Under OSX, with Xcode installed, with “Hardware IO Tools for Xcode” package installed, you could use the “Network Link Conditioner” to simulate specific traffic conditions.

same work load with 1, 2, n clients (its a server so one user is kind of low load)
complex loads (many inserters many readers etc)

benchmarking one aspect like this doesn’t say much for a server
simultaneous workloads are much more interesting

Hi Rick,

thank you for point. Good tool.

The reason why we still use CubeSQL is because it is easy in any aspect.

Easy to find and to download.
Easy to get a free 3 user license
Easy to install
Easy to administer
Easy to upload a single user SQLite database to it (and vice versa)
Easy to use with Xojo

Easy.

[quote=201421:@Norman Palardy]same work load with 1, 2, n clients (its a server so one user is kind of low load)
complex loads (many inserters many readers etc)

benchmarking one aspect like this doesn’t say much for a server
simultaneous workloads are much more interesting[/quote]

Hi Norman,

yes of course in ideal, we need yet add many other benches. For example, SELECT which finds 50-100K records and send them by protocol. Although in such bench a lots of time will use SQLite engine itself, reading them from disk. But still can be interesting and important to check this of course.

  • This was the first attempt to bench and I think anyway this is the simplest scenario.

  • My interest was to bench exactly: client-server-protocol on small commands, because this expose overhead of protocol.

Few years ago, Xojo developer, which have port his own ORM from mySQL server to Valentina Server, have surprise us, that on such small “SELECT f1,f2 FROM T WHERE PK = x” queries VServer was few times faster of mySQL. ORM do thousands of such queries, especially on App start. BTW, we did not check that ourself.

  • Valentina SQLite Server uses our standard protocol, multi-threading, multi-user techniques, etc established by years.

My personal opinion is, that posts like this should be blocked or deleted. The OP of a third party product producer is bad-mouthing a another third party product.

May be “Easy” is subjective point of view :slight_smile:

I have spend few hours fighting with it, for me was surprise that it starts transaction,
and my code for LOCAL Xojo SQLite engine

db.SqlExecute( “begin Transaction”)

was not working, because you cannot start transaction inside of other…

Or that PrepareStatement very differs from Xojo API, e.g. count from 1-based, and it is not reusable. So again EXISTED code for LOCAL sqlite engine was broken. Easy?

It’s not bad-mouthing if it’s true. Or would you say Bob, Kem, Christian, etc are bad-mouthing someone when they publish speed comparisons?

However he should have it on his blog and maybe have a link to it posted here as it is advertising your own product, so could be considered spam.

It’s a post out of the blue. Nobody asked for a benchmark and Ruslan replied then. This is just pure aggressive advertising.

I couldn’t have said it better.

In addition it should have been posted under “AddOns”, not under “Databases”.

Have you done comparisons with strings and indices? This might be interesting. Inserting strings with an index get so much slower with large databases.

@Eli: even if it is advertising it is interesting for me. And no it’s not bad mouthing.

No, I agree with Ruslan: he made an observation on products relevant to this forum, and he published his findings. Perfectly legal, imho.

It should have been on their website.

Of course they will…

Their support for an SQLite based server is quite new. They are looking for customers. That is the reason hat they did not test against MySQL and Postgres. Folks, this is pure marketing – don’t be naiv.

Thank you Markus,

yes of course we have post this on our forum also.

I wonder, when Marco few years ago have post here his benches of cubeSQL this was not advertising?

Now you can compare cubeSQL with other product.
Once again, bench project will be open source available to everybody.
Everybody can check that self, and add other benches. The more the better.

Why you guys okay to watch sport with two sprinters or two boxers, and why this “spam/advertising” in the software world? :slight_smile:

Than more that Valentina SQLite Server provides 10 free connections. Everybody can take and use.

Eli, please note

  1. Valentina SQLite Server provides
    10 free connections to SQLite DB
    5 connections to Valentina Reports

  2. Exists dozens Xojo Developers - which already have buy OmegaBundle, and they ALREADY have license for
    Valentina Studio Pro and Valentina Server - including that SQLite Server

So they are already our customers, and this info is important for them

Xojo, Inc.: can someone please block this thread.

Eli, its you starting to move question into “pure marketing”.

We try to discuss here only TECHNICAL questions and comparison: speed, API, wish-list for new product.

Once again, did you asked to block thread about cubeSQL benches?

good work Ruslan

i am waiting to release sqlite server

[quote=201445:@Alexis Colon Lugo]We try to discuss here only TECHNICAL questions and comparison: speed, API, wish-list for new product.
[/quote]

Thank you Alexis,

  1. Actually for us is more interesting in the above bench why OUR Server is 20-30 times slower of LOCAL sqlite … As for me yet too big overhead.

I remember benches of Valentina Server vs Valentina local engine was about x2 times overhead for server working on localhost. We will check this again later.

  1. We will think more :slight_smile:

Your comment is aggressive and has no interest.

There are so few companies who work for Xojo environment. There is need of emulation also.

Great job, Ruslan, keep it up!