Query with a combination of LET and Subselect is very slow

[Hung Tran]

Hi,

I am running OrientDB version 2.1.8, I running into the performance issue with this SQL

SELECT Statistics, $activeContacts[0].count FROM ContactList

   LET $activeContacts = (SELECT count(*) FROM Contact WHERE OwnedBy = $parent.$current AND Status = 0)

 WHERE Account=#20:0 AND EntityInfo.State=0

I have tried to break it down, each part individual SQL is fast, but a combination is pretty slow, I read its explain, but not sure what causes this.

The ContactList table has only 3 records, and Contact table has about 50k records.

Any suggestion will be very appreciated!

My Best,

Hung Tran

[Luca Son]

Hi Hung Tran,

could you post the entire schema to try the query ?

Thanks

Luca

[Hung Tran]

Hi Luca,

Here you are

CREATE CLASS Account

  CREATE PROPERTY Account.Status INTEGER

  ALTER PROPERTY Account.Status MANDATORY true

  ALTER PROPERTY Account.Status NOTNULL true

  CREATE INDEX Account.Status ON Account (Status) NOTUNIQUE_HASH_INDEX

CREATE CLASS ListStatistics
  CREATE PROPERTY ListStatistics.NumActiveContacts INTEGER
  ALTER PROPERTY ListStatistics.NumActiveContacts MANDATORY true
  ALTER PROPERTY ListStatistics.NumActiveContacts NOTNULL true
  CREATE PROPERTY ListStatistics.NumContacts INTEGER
  ALTER PROPERTY ListStatistics.NumContacts MANDATORY true
  ALTER PROPERTY ListStatistics.NumContacts NOTNULL true

CREATE CLASS EntityInfo
  CREATE PROPERTY EntityInfo.State INTEGER
  ALTER PROPERTY EntityInfo.State MANDATORY true
  ALTER PROPERTY EntityInfo.State NOTNULL true
  CREATE INDEX EntityInfo.State ON EntityInfo (State) NOTUNIQUE_HASH_INDEX

CREATE CLASS ContactList
  CREATE PROPERTY ContactList.Name STRING
  ALTER PROPERTY ContactList.Name MANDATORY true
  ALTER PROPERTY ContactList.Name COLLATE ci
  CREATE INDEX ContactList.Name ON ContactList (Name) UNIQUE_HASH_INDEX

  CREATE PROPERTY ContactList.Account LINK Account
  CREATE PROPERTY ContactList.Statistics LINK ListStatistics
  CREATE PROPERTY ContactList.EntityInfo EMBEDDED EntityInfo

CREATE CLASS Contact
  CREATE PROPERTY Contact.Status INTEGER
  ALTER PROPERTY Contact.Status MANDATORY true
  ALTER PROPERTY Contact.Status NOTNULL true
  CREATE INDEX Contact.Status ON Contact (Status) NOTUNIQUE_HASH_INDEX
  CREATE PROPERTY Contact.EmailAddress STRING
  ALTER PROPERTY Contact.EmailAddress MANDATORY true

  CREATE PROPERTY Contact.OwnedBy LINK ContactList
  CREATE PROPERTY Contact.EntityInfo EMBEDDED EntityInfo

Remarks, I did simplify the model, just keep related fields with the query. If you want a full schema and data, I will make a file attachment (it is about 2.4 MB)

My Best,

Hung Tran

[Luca Son]

Hi Hung Tran,

yes, if it were possible it would help a lot.

Thanks

Luca

[Hung Tran]

Hi Luca,

Please find it in attachment.

I look forward to hearing from you. Thank you for your concern.

My Best,

Hung Tran

[Luca Son]

Hi,

thanks for the DB.

To compare the results, could you tell me what's your best execution time about the above query ?

Thanks

Luca

[alessand...@gmail.com]

Hi,

can you try with this query ?

SELECT OwnedBy[EntityInfo.State=0][Account=#20:0].Statistics, count(*) FROM Contact where Status = 0 group by OwnedBy

Best regards,

Alessandro

[Hung Tran]

Hi Luca,

3s is the best result on my VM with that query (I don't know why JVM process is staying at 27% until now, that may impact on the result)

My Best,

Hung Tran

[Hung Tran]

Hi Alessandro,

OMG, it gives a better result. 2-2.5 sec instead.

Look at its explain, I don't know why it is faster.

With that query style, how could I write a similar query like

SELECT Statistics, $active[0].count, $total[0].count FROM ContactList
   LET $active = (SELECT count(*) FROM Contact WHERE OwnedBy = $parent.$current AND Status = 0),
       $total = (SELECT count(*) FROM Contact WHERE OwnedBy = $parent.$current)

 WHERE Account=#20:0 AND EntityInfo.State=0

I did

SELECT OwnedBy[EntityInfo.State=0][Account=#20:0].Statistic, count(@this[Status=0]), count(*)
  FROM Contact
 GROUP BY OwnedBy

Hopefully, that's an official way, it's obviously faster the old style.

I look forward to hearing from you. Thank you for your concern.

My Best,

Hung Tran