I have an OrientDB database with around 300 million records of class 'A extends V'. Each record has a property 'x' (LONG) which I built an index for (NOTUNIQUE).
I'm now trying to access records where 'x > 0' from my C# code:
SELECT * FROM A WHERE x > 0
Because this was to much for the hard-coded socket timeout of the .NET-binary connector (30 secs), I tried to use pagination to limit the resultset as well as the query's time:
SELECT * FROM A WHERE @rid > <skip> AND x > 0 LIMIT 10000
I couldn't think of a proper way of receiving the for the next iteration (I would need the @rid of the last record searched, getting the @rids of the matches only) and ran into a timeout error once again (first query took around 4 secs, second took 5 secs, third took 6 secs, increasing quickly to over 30 secs, which lead to the timeout). This is probably, because my code was accidentally designed to scan for the first succession of non-hits and then fail to increase skip:
using (var db = new ODatabase("localhost", 2424, "testgraph", ODatabaseType.Graph, "root", "<some password>"))
{
var skip = new ORID();
var limit = 20000;
var records = db.Command($"SELECT * FROM A WHERE @rid > {skip} AND x > 0 LIMIT {limit}").ToList();
while (records.Count > 0)
{
// problem is here
skip = new ORID(records.Last().ORID);
foreach (var record in records)
{
// do awesome stuff
}
records = db.Command($"SELECT * FROM A WHERE @rid > {skip} AND x > 0 LIMIT {limit}").ToList();
}
}
Is there any way of retrieving records with x > 0, avoiding this timeout-issue other than thowing more CPU power and faster storage at it?
Thanks in advance!