Hi, Lvc:
Here is the result:
orientdb {compounds}> select count(*) from Compound where '000075-72-9' in
cas
----+-----+-----
# |@RID |count
----+-----+-----
0 |#-2:0|1
----+-----+-----
1 item(s) found. Query executed in 222.285 sec(s).
On Sunday, April 20, 2014 4:38:59 PM UTC+8, Lvc@ wrote:
>
> Hi Jack,
>
> if you execute this:
>
> select count(*) from Compound where '000075-72-9' in cas
>
> How many records are retrieved? How much time does it take?
>
> Lvc@
>
>
>
> On 20 April 2014 07:07, Wise Jack <[email protected] <javascript:>>wrote:
>
>> Has anyone got a suggestion?
>>
>>
>> On Tuesday, April 15, 2014 10:00:19 AM UTC+8, Wise Jack wrote:
>>>
>>> Hi, Andrey:
>>>
>>> The result is the same, unacceptable slow, here is the explain:
>>>
>>> orientdb {compounds}> explain select * from Compound where '000075-72-9'
>>> in cas
>>>
>>> Profiled command '{involvedIndexes:[1],current:#11:960477,
>>> fetchingFromTargetElapsed:327390,documentReads:959211,
>>> documentAnalyzedCompatibleClass:959211,recordReads:959211,
>>> elapsed:327501.62,resultType:collection,resultSize:1}' in 327.528992
>>> sec(s):
>>> {"@type":"d","@version":0,"involvedIndexes":["Compound.
>>> cas"],"current":"#11:960477","fetchingFromTargetElapsed":
>>> 327390,"documentReads":959211,"documentAnalyzedCompatibleClas
>>> s":959211,"recordReads":959211,"elapsed":327501.62,"
>>> resultType":"collection","resultSize":1,"@fieldTypes":"
>>> involvedIndexes=e,fetchingFromTargetElapsed=l,documentReads=l,
>>> documentAnalyzedCompatibleClass=l,recordReads=l,elapsed=f"}
>>>
>>> On Friday, April 11, 2014 6:15:39 PM UTC+8, Andrey Lomakin wrote:
>>>>
>>>> HI,
>>>> Could you try now ?
>>>>
>>>>
>>>> On Tue, Apr 8, 2014 at 5:13 PM, Wise Jack <[email protected]> wrote:
>>>>
>>>>> Hi, Andrey:
>>>>>
>>>>> Sure. I'll send you a sample document of the database, I can't send
>>>>> the whole database to you since it's too large:
>>>>>
>>>>> This is a sample record of the database, I'm immigrating a chemical
>>>>> compounds database from MySQL to OrientDB.
>>>>> --------------------------------------------------
>>>>> ODocument - Class: Compound id: #11:5111 v.1
>>>>> --------------------------------------------------
>>>>> iupac_cas_name : chloro(trifluoro)methane
>>>>> create_date : Sat Jan 17 00:00:00 CST 1970
>>>>> iupac_traditional_name : chloro(trifluoro)methane
>>>>> cactvs_hbond_acceptor : 3
>>>>> component_count : 1
>>>>> cactvs_tauto_count : 1
>>>>> nonstandardbond : null
>>>>> molecular_weight : 104.45891
>>>>> coordinate_type : 1
>>>>> 5
>>>>> 255
>>>>> monoisotopic_weight : 103.964066
>>>>> iupac_inchikey : AFYPFACVUDMOHA-UHFFFAOYSA-N
>>>>> exact_mass : 103.964066
>>>>> xlogp3 : 2.0
>>>>> iupac_name : chloro(trifluoro)methane
>>>>> openeye_iso_smiles : C(F)(F)(F)Cl
>>>>> compound_canonicalized : 1
>>>>> isotopic_atom_count : 0
>>>>> cactvs_subskeys : AAADcQAAAYAEAAAAAAAAAAAAAAAAAA
>>>>> AAAAAAAAAAAAAAAAAAAAAAAQIAAAAAAAAAABAAAAAAAAAAAAAAAAAAAAAAAA
>>>>> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==
>>>>> atom_udef_stereo_count : 0
>>>>> cactvs_complexity : 28
>>>>> iupac_systematic_name : chloranyl-tris(fluoranyl)methane
>>>>> bond_udef_stereo_count : 0
>>>>> bond_def_stereo_count : 0
>>>>> cactvs_hbond_donor : 0
>>>>> bondannotations : undefined
>>>>> cactvs_tpsa : 0
>>>>> cas : [75-72-9, 185009-43-2
>>>>> 75-72-9, 50815-73-1, 000075-72-9, 185009-43-2, 4-01-00-00034
>>>>> (Beilstein Handbook Reference)]
>>>>> openeye_can_smiles : C(F)(F)(F)Cl
>>>>> heavy_atom_count : 5
>>>>> iupac_openeye_name : chloro(trifluoro)methane
>>>>> iupac_inchi : InChI=1S/CClF3/c2-1(3,4)5
>>>>> modify_date : Sat Jan 17 00:00:00 CST 1970
>>>>> molecular_formula : CClF3
>>>>> total_charge : 0
>>>>> compound_cid : 6392
>>>>> atom_def_stereo_count : 0
>>>>> cactvs_rotatable_bond : 0
>>>>>
>>>>> The embedded list field is the CAS field.
>>>>>
>>>>> The schema of Class Compound is as the attachment.
>>>>>
>>>>> On Tuesday, April 8, 2014 9:09:30 PM UTC+8, Andrey Lomakin wrote:
>>>>>
>>>>>> Could you provide database sample ?
>>>>>>
>>>>>>
>>>>>> On Tue, Apr 8, 2014 at 8:51 AM, Wise Jack <[email protected]> wrote:
>>>>>>
>>>>>>> Hi, Andrey.
>>>>>>>
>>>>>>> Thanks for your reply. The memory information is as below:
>>>>>>>
>>>>>>> [root@root ~]# cat /proc/meminfo
>>>>>>> MemTotal: 8063160 kB
>>>>>>> MemFree: 228968 kB
>>>>>>>
>>>>>>> As you can see
>>>>>>>
>>>>>>> "involvedIndexes":["ClassA.fieldA"],
>>>>>>> "current":"#11:960477",
>>>>>>> "fetchingFromTargetElapsed":160596,
>>>>>>> "documentReads":959211,
>>>>>>>
>>>>>>> Even the database can see the index, but it still iterate all the
>>>>>>> documents in the database, I think that's the reason for the slow.
>>>>>>>
>>>>>>> The same data in mysql(that using fieldA's index), can return data
>>>>>>> in 0.015second, so I think this is not the fault of the data, maybe
>>>>>>> there
>>>>>>> is a better way for creating index or querying using index for embedded
>>>>>>> list of OrientDB.
>>>>>>>
>>>>>>> On Monday, April 7, 2014 5:25:27 PM UTC+8, Andrey Lomakin wrote:
>>>>>>>
>>>>>>>> Yes too slow.
>>>>>>>> What amount of RAM do you have ?-
>>>>>>>>
>>>>>>>>
>>>>>>>> On Mon, Apr 7, 2014 at 5:33 AM, Wise Jack <[email protected]>wrote:
>>>>>>>>
>>>>>>>>> I'm testing orientdb for a storage database of a knowledge base.
>>>>>>>>>
>>>>>>>>> The database can be something like this:
>>>>>>>>>
>>>>>>>>> [
>>>>>>>>> {
>>>>>>>>> fieldA: ['a','b','c']
>>>>>>>>> },
>>>>>>>>> {
>>>>>>>>> fieldA: ['c','d','e']
>>>>>>>>> },
>>>>>>>>> ]
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> and the query is something like this:
>>>>>>>>>
>>>>>>>>> select from ClassA where 'c' in fieldA
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> The query is very very slow, the explain of the query is as below
>>>>>>>>>
>>>>>>>>> {
>>>>>>>>> "@type":"d","@version":0,
>>>>>>>>> "involvedIndexes":["ClassA.fieldA"],
>>>>>>>>> "current":"#11:960477",
>>>>>>>>> "fetchingFromTargetElapsed":160596,
>>>>>>>>> "documentReads":959211,
>>>>>>>>> "documentAnalyzedCompatibleClass":959211,
>>>>>>>>> "recordReads":959211,
>>>>>>>>> "elapsed":160596.25,
>>>>>>>>> "resultType":"collection",
>>>>>>>>> "resultSize":1,
>>>>>>>>>
>>>>>>>>> "@fieldTypes":"involvedIndexes=e,fetchingFromTargetElapsed=l,documentReads=l,documentAnalyzedCompatibleClass=l,recordReads=l,elapsed=f"
>>>>>>>>> }
>>>>>>>>>
>>>>>>>>> As you can see, even OrientDB used the fieldA index, it still
>>>>>>>>> costs 16 seconds to query a million records, it is unacceptable.
>>>>>>>>>
>>>>>>>>> Is there any good way to make this query faster?
>>>>>>>>>
>>>>>>>>> https://stackoverflow.com/questions/22896528/embedded-list-q
>>>>>>>>> uery-performance-in-orientdb
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>>
>>>>>>>>> ---
>>>>>>>>> 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 [email protected].
>>>>>>>>>
>>>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Best regards,
>>>>>>>> Andrey Lomakin.
>>>>>>>>
>>>>>>>> Orient Technologies
>>>>>>>> the Company behind OrientDB
>>>>>>>>
>>>>>>>> --
>>>>>>>
>>>>>>> ---
>>>>>>> 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 [email protected].
>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Best regards,
>>>>>> Andrey Lomakin.
>>>>>>
>>>>>> Orient Technologies
>>>>>> the Company behind OrientDB
>>>>>>
>>>>>> --
>>>>>
>>>>> ---
>>>>> 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 [email protected].
>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Best regards,
>>>> Andrey Lomakin.
>>>>
>>>> Orient Technologies
>>>> the Company behind OrientDB
>>>>
>>>> --
>>
>> ---
>> 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 [email protected] <javascript:>.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
---
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
