Impact of NodeService calls on MySQL database

[orichaud]

Hi,
I am currently having a look at the database usage as we are currently planning to develop with the Java Foundation API some new components and services. We have defined a new document type (subclassing cm:content) with many metadata and recursive associations connecting sub objects and we need to retrieve the full object graph. At worst, this can represent something like 700 properties scattered into tens of objects interconnected.

I traced the SQL statements using the ''NodeService.getChildAssocs'' which returns the children objects, but I did not see in my logs the corresponding SQL statement for 'NodeService.getProperties'. As my understanding of Alfresco is limited, I understand that the properties were fetched from the lucene indexes. Am I right?

My current environment is Windows + MySQL + Alfresco 3.4b.

More generally, I someone can give me some explanations, I would really appreciate.

Regards.

Olivier

[mrogers]

Although some properties are indexed in the lucene index for search purposes the properties live in the database.    There will be a sql query retrieving the node properties for each node that you read.

[orichaud]

Thanks for your reply.
I see only the statements for the associations. Is there any caching of properties?

And to sum up, for a given node to traverse, 2 select are necessary:
- one for the properties
- one for the associations

Regards.

Olivier

[mrogers]

Yes there's lots of caching.

Traversing may not need to read the properties, it depends upon the association type.

[orichaud]

I assume caching took place and this is why I did not notice any SQL statement in mysql logs (I use the general_log flag in my.ini file).

I assumed these calls because  we need to retrieve, starting from a node, the full object graph with for each object, all the properties. My association are child associations. In fact all those objects in this graph are necessary because we have many list of structured records. Thos objects can only live as long as the content object exists.

As an example, here is a short extract of our custom model. As you can see you have nested child associations and many properties in each type.

… SNIPET …
      <type name="acmc:RefPointType">
         <title>Reference Point</title>
         <parent>sys:base</parent>
         <archive>false</archive>
         <properties>
            <property name="acmc:refPointIndexPointCode">
               <type>d:int</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:refPointPrimaryIndicator">
               <type>d:boolean</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:refPointName">
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:refPointDirection">
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:refPointDistance">
               <type>d:float</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:refPointDistanceUnitName">
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
         </properties>
         <associations>
            <child-association name="acmc:transportations">
               <title>Transportations for this Reference Point</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:TransportationType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
         </associations>
         <mandatory-aspects>
            <aspect>acmc:MultilingDescriptionsAspect</aspect>
         </mandatory-aspects>
      </type>
… SNIPET …
      <aspect name="acmc:PropertyAspect">
         <title>Tag a ConfigurationType as a property</title>
         <properties>
            <property name="acmc:code">
               <title>Chain Code</title>
               <type>d:text</type>
               <multiple>false</multiple>
               <index enabled="true">
                  <atomic>true</atomic>
                  <stored>true</stored>
                  <tokenised>false</tokenised>
               </index>
            </property>
            <property name="acmc:lateCode">
               <title>Late Code</title>
               <type>d:int</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:contactPhone">
               <title>Contact Phone</title>
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:contactFax">
               <title>Contact Fax</title>
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:contactEmail">
               <title>Contact Email</title>
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:contactUrl">
               <title>Contact URL</title>
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:location">
               <title>location</title>
               <type>d:int</type>
               <multiple>false</multiple>
            </property>
            <property name="acmc:iataCode">
               <title>IATA Code</title>
               <type>d:text</type>
               <multiple>false</multiple>
            </property>
         </properties>
         <associations>
            <association name="acmc:linked-brand">
               <title>Referenced brand</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </source>
               <target>
                  <class>acmc:ConfigurationType</class>
                  <mandatory>true</mandatory>
                  <many>false</many>
               </target>
            </association>
            <child-association name="acmc:currencies">
               <title>Currencies</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:CurrencyType</class>
                  <mandatory>false</mandatory>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
            <child-association name="acmc:languages">
               <title>Languages</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:LanguageType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
            <child-association name="acmc:fops">
               <title>Forms Of Payment</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:FOPType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
            <child-association name="acmc:creditCards">
               <title>Credit Cards</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:CreditCardType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
            <child-association name="acmc:addresses">
               <title>Addresses</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:AddressType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
            <child-association name="acmc:refPoints">
               <title>Reference Points</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </source>
               <target>
                  <class>acmc:RefPointType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
            </child-association>
         </associations>
      </aspect>

… SNIPET …

      <aspect name="acmc:MultilingDescriptionsAspect">
         <associations>
            <child-association name="acmc:multilingDescriptions">
               <title>Descriptions</title>
               <source>
                  <mandatory>false</mandatory>
                  <many>false</many>
               </source>
               <target>
                  <class>acmc:MultilingDescriptionType</class>
                  <mandatory>false</mandatory>
                  <many>true</many>
               </target>
               <duplicate>false</duplicate>
               <propagateTimestamps>true</propagateTimestamps>
            </child-association>
         </associations>
      </aspect>
… SNIPET …
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Regards.

Olivier.