How to speed up "FIND REFERENCES #64:0" SQL

55 views
Skip to first unread message

Hung Tran

unread,
Jan 20, 2016, 6:07:52 AM1/20/16
to orient-...@googlegroups.com
Hi,

I am running OrientDB version 2.1.8, from Studio, it took long.


it took more than 2 seconds

Here is its explain


It takes the same amount of time on next attempts.

Any idea will be really appreciated!

My Best,
Hung Tran

alessand...@gmail.com

unread,
Jan 20, 2016, 9:00:31 AM1/20/16
to OrientDB
Hi,
how many record do you have in your database ?

Kind regards,
Alessandro

Hung Tran

unread,
Jan 20, 2016, 10:44:23 AM1/20/16
to orient-...@googlegroups.com
Hi Alessandro,

For the cluster 64, there is just a single record and no dependency.

For whole database, there are less than 110k records

Is there any clue?

My Best,
Hung Tran

alessand...@gmail.com

unread,
Jan 20, 2016, 11:02:17 AM1/20/16
to OrientDB
Hi ,
I think it depends by the number of records in your database.

Best regards,
Alessandro

Hung Tran

unread,
Jan 20, 2016, 11:25:18 AM1/20/16
to orient-...@googlegroups.com
Hi Alesandro,

so that is really a serious issue. A database with few hundred thousand of records are just a pretty small database in a business application.

Is there any workaround for this?

My Best,
Hung Tran 

scott molinari

unread,
Jan 20, 2016, 2:01:51 PM1/20/16
to OrientDB
What do you need that command for?

Scott

Hung Tran

unread,
Jan 20, 2016, 9:55:01 PM1/20/16
to orient-...@googlegroups.com
Hi Scott,

I have two use cases for this

1. Because OrientDB allows you delete any record even if they got referenced by some other records. I need to check its references to know when a record could be deleted, when it could be not and do a soft delete instead, and proceeds all related records according to ORM model. For example, with Sales model, when you delete an draft order, you will need also to delete its all order lines but not delete any Product referenced by them.

2. I have a special use case, when a record is updated, it will do an update only if that is not being referenced by others, otherwise it will do an insert instead.

Without that SQL, I will have to generate many SQL to compute its dependencies.

My Best,
Hung Tran

Luigi Dell'Aquila

unread,
Jan 21, 2016, 3:16:11 AM1/21/16
to orient-...@googlegroups.com
Hi guys,

FIND REFERENCES command is not intended to be used in typical production environments.
Links are unidirectional, so to find all the documents that refer to a single RID, OrientDB has to do a full scan of the database, this is operation will take a long time to execute.

We suggest to use FIND REFERENCES only to repair databases where you deliberately deleted records without updating their references.
For normal use cases we suggest to:
- use edges instead of links: edges are bi-directional (no need to execute FIND REFERENCES) and are automatically managed by OrientDB (they are automatically removed when you remove a connected vertex)
- use links only in schemaful, indexed properties, so that you always know where to look for in case you manually remove a linked document

There are no plans to make FIND REFERENCES faster, just because it would imply a huge performance impact on the whole architecture

Thanks

Luigi


2016-01-21 3:55 GMT+01:00 Hung Tran <tdhu...@gmail.com>:
Hi Scott,

I have two use cases for this

1. Because OrientDB allows you delete any record even if they got referenced by some other records. I need to check its references to know when a record could be deleted, when it could be not and do a soft delete instead, and all related records according to ORM model. For example, with Sales model, when you delete an draft order, you will need also to delete its all order lines but not delete any Product referenced by them.

2. I have a special use case, when a record is updated, it will do an update only if that is not being referenced by others, otherwise it will do an insert instead.

Without that SQL, I will have to generate many SQL to compute its dependencies.

My Best,
Hung Tran

On Thursday, January 21, 2016 at 2:01:51 AM UTC+7, scott molinari wrote:
What do you need that command for?

Scott

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply all
Reply to author
Forward
0 new messages