Query with a combination of LET and Subselect is very slow

46 views
Skip to first unread message

Hung Tran

unread,
Jan 21, 2016, 10:31:18 PM1/21/16
to orient-...@googlegroups.com
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

unread,
Jan 22, 2016, 12:47:46 AM1/22/16
to OrientDB
Hi Hung Tran,

could you post the entire schema to try the query ?

Thanks

Luca

Hung Tran

unread,
Jan 22, 2016, 3:50:10 AM1/22/16
to orient-...@googlegroups.com
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

unread,
Jan 22, 2016, 4:21:07 AM1/22/16
to OrientDB
Hi Hung Tran,

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

Thanks

Luca

Hung Tran

unread,
Jan 22, 2016, 4:27:01 AM1/22/16
to OrientDB
Hi Luca,

Please find it in attachment.

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

My Best,
Hung Tran
db-temp-50k.gz

Luca Son

unread,
Jan 22, 2016, 5:22:13 AM1/22/16
to OrientDB
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

unread,
Jan 22, 2016, 10:39:23 AM1/22/16
to OrientDB
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

unread,
Jan 22, 2016, 12:56:18 PM1/22/16
to OrientDB
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

unread,
Jan 22, 2016, 1:04:15 PM1/22/16
to orient-...@googlegroups.com
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
Reply all
Reply to author
Forward
0 new messages