High performance drop when using ArcadeGraph with RemoteDatabase

[dijef]

ArcadeDB Version:

ArcadeDB Server v23.11.1

OS and JDK Version:

Windows 10 10.0 - OpenJDK 64-Bit Server VM 17.0.9 (Temurin-17.0.9+9)

Expected behavior

The performance difference between RemoteDatabase and Gremlin Traversal API and embedded mode is reduced.

Actual behavior

Following some performance testing I did in #1299 I ran tests for ArcadeGraph instance using object and traversal API, both well underperforming when inserting data. RemoteDatabase SQL statements are also not that great. Managing transactions manually helps a little. I understand from #1390 and #1373 that there is combination of factors like HTTP used instead of binary as well as extra calls made. I do believe however the difference is too big to ignore, as in OrientDB remote OrientGraph was very fast. Our tests using real data also show that ArcadeDB is performing worse than other providers executing exactly same code (the gap is much bigger than in code I provided below.

This leads to massive performance difference between embedded and remote (on same machine) modes, about 8 times slower. Re-writing all logic to Traversal only is undesired effort and embedded mode performance will suffer as result. Are there any plans to either improve this or provide binary protocol that would improve I/O? Our application is performing lots of small inserts and edits with user actions.

Steps to reproduce

I extracted and placed testing code in this location: https://github.com/dijef/arcade-db-performance-tests See ReadMe for instructions or how this can be used

[lvca]

Thanks @dijef for the test case. The big difference with OrientDB is not in using the binary protocol, but the fact that when I designed the remote API for OrientDB (a long time ago), I was using a caching mechanism on the client that keeps in the client's RAM the records until the transaction is committed. That's why with OrientDB we always suggest using explicit transactions with remote because of this side effect of "batching".

I don't see why we can't implement the same mechanism with ArcadeDB Remote API. One of the issues could be the transaction isolation level. Right now a transaction is opened on the server and all the reads are using server-side transactions, 1-to-1. With this caching technique, we'll have the same issue with OrientDB: the lack of isolation from remote, especially REPEATABLE_READ.

By a quick check in the code, the main points to change are:

• RemoteDatabase.begin() based on the tx isolation level should (1) maintain the current behavior if REPEATABLE_READ is used, otherwise just set a status in the object that the TX is started and will be cached
• RemoteDatabase.saveRecord() should store in a local cache (like a Map<RID,Document>) all the saved records to be retrieved by further reads in the same transaction
• RemoteDatabase.lookupByRID() should first check in the local cache, then ask the server
• RemoteDatabase.commit() in case the tx is batched, it should send a SQL SCRIPT with all the changes together with a COMMIT command at the end

One open question is about RIDs. Now they are assigned at creation time, while with OrientDB, they were temporary (negative record positions) until commit time. I guess we need something like that, otherwise creating edges between 2 vertices won't work.