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

            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