Improve performance of slow queries

[georgiana-b]

We have a few requests that take way more than they should:

• get list of countries (/tenders/countries)
• get list of cpvs (/tenders/cpvs)
• get node details (/networks/{networkID}/nodes/{nodeID}

Investigate why they take so long. Obv some of the assumptions on which I based these queries are wrong.

[georgiana-b]

Node details speed improvements:

• total request time for bidder with 8 bids: 25 sec 🙀
• getting the node details from db: 1.2 sec

  SELECT set(@rid).size() as numberOfWinningBids,
    sum(price.netAmountEur) as amountOfMoneyExchanged,
    list(price.netAmountEur).size() as numberOfAvailablePrices,
    set(@rid) as bidRIDs
      FROM (
        SELECT *
        FROM Bid
        WHERE xCountry in ['CZ'] AND xYear in [2015] AND out('AppliedTo').in('Comprises').out('HasCPV').code in ['16700000']
        AND in('Participates').id in ['bb25de5c-e4b4-408a-a7d7-382fa0ee2e2f']
        AND isWinning=true
      );

• getting the bidders of the bid: 4.337 sec

  SELECT expand(in('Participates')) FROM Bid where @rid='#91:48440';

  • getting things by @rid seems super slow: 4.3 sec

    SELECT FROM Bid where @rid='#91:48440';

Possible solutions:

• Unique persistent IDs for lots and bids #67
• use an internal id for bid and lot just to have an index to use instead of rid

[georgiana-b]

Get countries speed improv:

Even though we have an index on xCountry, ODB doesn't use that to compute distinct(xCountry). It reads all the bids so it ends up being very slow: 5.9 sec.
If I add a filter by year to use the xYear index, the speed increases dramatically:

Same issue with with getting a list of years on /tenders/years

Possible solutions:

• open an issue to ODB GH about this since it's a pretty big problem
• in the meantime try to use the Bid.xCountry index directly from the code to compute the distinct

[georgiana-b]

The changes in #84 have brought significant improvements to some queries.
For example getting cpvs by country https://dev.api.tenders.exposed/tenders/cpvs?countries=RO was brought down from 12 sec to 5.3 sec. Of course, 5.3 sec is still a lot but I haven't been able to identify why the cpvs query still takes so long.

Run in OrientDB 3.0 the same cpvs query is tremendously faster, topping at 0.070 sec but unfortunately we can't switch to that version yet because of orientechnologies/orientjs#304

[georgiana-b]

For /tenders/countries and /tenders/years the indexes on xCountry and xYear are not used to calculate distinct(). I tried to access the values directly from the index as described here but nothing is returned.

I opened orientechnologies/orientdb#8169 for this since it would be a great improvement but I don't have my hopes too high since ODB 2 is no longer their main focus.

In ODB 3 the same query we use now runs a lot faster: 0.435 sec as opposed to 5.9 sec in ODB 2.

[georgiana-b]

I will move this to backlog and come back to it later when there are updates on ODB 3.