Uploaded image for project: 'Service Packs and Hot Fixes'
  1. Service Packs and Hot Fixes
  2. MNT-15576

performance issue for "Select_NodeProperty_Results" sql with WHERE node_id IN vs. WHERE node.id IN

    Details

    • Type: Service Pack Request
    • Status: Closed (View Workflow)
    • Resolution: Fixed
    • Affects Version/s: 5.0.2.5, 4.1.9, 4.2.4
    • Fix Version/s: 5.0.4
    • Environment:
      Version: 4.1.9
      Database: PostgreSQL
      App Server: Jboss
      Mobile Version: Not Applicable
      OS: RHEL
      Workdesk Version: Not Applicable

      Description

      Customer's DBA noticed that query time where is: WHERE node_id IN is: 99462.404 ms
      But if they could change this to WHERE node.id IN the query time is: 30.047 ms

      Customer would like to know if it is possible to change this parameter from WHERE node_id to WHERE node.id ?

      alfresco=# explain SELECT node.id AS node_id,
      alfresco-# node.version AS node_version,
      alfresco-# prop.qname_id AS qname_id,
      alfresco-# prop.locale_id AS locale_id,
      alfresco-# prop.list_index AS list_index,
      alfresco-# prop.actual_type_n AS actual_type_n,
      alfresco-# prop.persisted_type_n AS persisted_type_n,
      alfresco-# prop.boolean_value AS boolean_value,
      alfresco-# prop.long_value AS long_value,
      alfresco-# prop.float_value AS float_value,
      alfresco-# prop.double_value AS double_value,
      alfresco-# prop.string_value AS string_value,
      alfresco-# prop.serializable_value AS serializable_value
      alfresco-# FROM alf_node node
      alfresco-# JOIN alf_node_properties prop ON (prop.node_id = node.id)
      alfresco-# WHERE node_id IN ('47774455', '47774488', '47774491', '47774492', '47774493', '47774498', '47774499', '47774518', '47774595', '47774614', '47774615', '47774637', '47774638', '47774661', '47774668', '47774671', '47774673', '47774696', '47774711', '47774723', '47774726', '47774730', '47774746', '47774753', '47774757', '47774759', '47774817', '47774861', '47774891', '47774907', '47774976', '47775740', '47775749', '47775750', '47775830', '47776195', '47776197', '47776687', '47776691', '47776823', '47776980', '47776981', '47777012', '47777058', '47777078');

      Support Investigation
      Discussed with Jennie on this, and Jennie adviced this issue is more to do with performance as prop.node_id would return 1-N rows whereas node.id would return 1 row for the where clause, therefore the timecost as indicated by customer would make sense. Request for Engineering review.

      Double checked in checked in alfresco-repository-5.0.2.5.jar > alfresco\ibatis\org.hibernate.dialect.Dialect\node-common-SqlMap.xml and confirm the sql where condition query is same as v4.1.9's node-common-SqlMap.xml.

          <sql id="select_NodeProperty_Results">
              select
                  node.id                 as node_id,
                  node.version            as node_version,
                  prop.qname_id           as qname_id,
                  prop.locale_id          as locale_id,
                  prop.list_index         as list_index,
                  prop.actual_type_n      as actual_type_n,
                  prop.persisted_type_n   as persisted_type_n,
                  prop.boolean_value      as boolean_value,
                  prop.long_value         as long_value,
                  prop.float_value        as float_value,
                  prop.double_value       as double_value,
                  prop.string_value       as string_value,
                  prop.serializable_value as serializable_value
          </sql>
          
      <select id="select_NodeProperties" parameterType="NodeProperty" resultMap="result_NodeProperty"> 
      <include refid="alfresco.node.select_NodeProperty_Results"/> 
      from 
      alf_node node 
      join alf_node_properties prop on (prop.node_id = node.id) 
      <where> 
      <if test="nodeId != null">prop.node_id = #{nodeId}</if> 
      <if test="nodeIds != null"> 
      and node_id in 
      <foreach item="item" index="index" collection="nodeIds" open="(" separator="," close=")"> 
      #{item} 
      </foreach> 
      </if> 
      <if test="key != null and key.qnameId != null">and qname_id = #{key.qnameId}</if> 
      <if test="qnameIds != null"> 
      and qname_id in 
      <foreach item="item" index="index" collection="qnameIds" open="(" separator="," close=")"> 
      #{item} 
      </foreach> 
      </if> 
      </where> 
      </select> 
      

        Attachments

          Issue Links

            Structure

              Activity

                People

                • Assignee:
                  closedbugs Closed Bugs
                  Reporter:
                  sliaw Seng Liaw
                • Votes:
                  1 Vote for this issue
                  Watchers:
                  8 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - 0 minutes
                    0m
                    Logged:
                    Time Spent - 1 hour, 5 minutes
                    1h 5m

                      Structure Helper Panel