SQL batch performance tuning suggestions?

Hi experts,

We had some performance issue when running a very large SQL batch via REST API. In our case, the graph consists of 12655 Vertexes and 12654 Edges. I have assembled the REST request as follows, where there are (12655+12654) = 25309 SQLs in the batch that I’ve omitted the rest of them below.

{ “transaction” : true,
“operations” : [
{
“type” : “script”,
“language” : “sql”,
“script” : [ “LET account = UPDATE Account MERGE {…} UPSERT RETURN AFTER WHERE someId = '” + var.id + “’”,
“LET city = UPDATE CYvt_Person MERGE {…} UPSERT RETURN AFTER WHERE @rid = '” + var.anotherId +"’",
“CREATE EDGE Lives UPSERT FROM $account TO $city”,
… ]
}
]
}

The execution is very slow and takes about 15 minutes. Is this performance expected? If not, how could I tune the performance?

Any suggestions are much appreciated.

Hi @vivacewwxu

the total execution time is 15minutes?

Do you have indexes on Account.someId?

Thanks

Yes and I have indexes on those ids otherwise the UPSERT command will fail.

Hi @vivacewwxu

how much does it take the single request?

Thanks

I had another test with 1/4 of the data set, i.e. 3505 Vertexes and 3504 Edges, and it took 62 seconds. But it is still slow, or is it expected performance?

Does the transaction flag slow down the performance a lot? It seems that even if I set it to false, I still get similar performance. What if I configure the server according to guide below? (I don’t have server admin access so haven’t tried it yet…)

http://orientdb.com/docs/3.0.x/tuning/Performance-Tuning.html#disable-journal
http://orientdb.com/docs/3.0.x/tuning/Performance-Tuning.html#disable-transaction-log

Another question, what if I switch to Java API and set the Massive Insert Intent? Is there much difference from SQL batch via REST API?

Thanks.

And I have some new testing results as follows. The performance gets worse as I insert more data into the OrientDB. Specifically, every time I insert a new data set (3505 Vertexes and 3504 Edges) into the database, the execution time increases 40-70 seconds (see logs below). I have also disabled the journal and transaction log according to performance tuning links above, but no luck on performance improvement.

Do you have any comments or suggestions on how to improve the performance? Thanks.

[3/31/20 10:42:55:410 UTC] 00154e98 LoggerScripta I Finish to push: 129000 ms.

[3/31/20 10:45:45:574 UTC] 00154e98 LoggerScripta I Finish to push: 168000 ms. (+39000ms)

[3/31/20 10:49:17:784 UTC] 00154e98 LoggerScripta I Finish to push : 210000 ms. (+42000ms)

[3/31/20 10:53:32:622 UTC] 00154e98 LoggerScripta I Finish to push : 253000 ms. (+43000ms)

[3/31/20 10:58:28:731 UTC] 00154e98 LoggerScripta I Finish to push : 293000 ms. (+40000ms)

[3/31/20 11:04:11:117 UTC] 00154e98 LoggerScripta I Finish to push : 341000 ms. (+48000ms)

[3/31/20 11:10:34:307 UTC] 00154e98 LoggerScripta I Finish to push : 381000 ms. (+40000ms)

[3/31/20 11:17:51:753 UTC] 00154e98 LoggerScripta I Finish to push : 435000 ms. (+54000ms)

[3/31/20 11:26:04:827 UTC] 00154e98 LoggerScripta I Finish to push : 491000 ms. (+56000ms)

[3/31/20 11:35:00:106 UTC] 00154e98 LoggerScripta I Finish to push : 533000 ms. (+42000ms)

[3/31/20 12:30:10:389 UTC] 001595d7 LoggerScripta I Finish to push : 604000 ms. (+71000ms)

[3/31/20 12:41:23:115 UTC] 001595d7 LoggerScripta I Finish to push : 671000 ms. (+67000ms)