Crowd queries varchar columns using nvarchar type parameters for Microsoft SQL databases

XMLWordPrintable

    • Type: Bug
    • Resolution: Not a bug
    • Priority: Low
    • None
    • Affects Version/s: 3.0.0, 3.0.1
    • Component/s: Database
    • None
    • 3
    • Severity 3 - Minor

      Summary

      Crowd creates tables with column varchar in SQL Server which can cause performance issues. It makes database server scanning an index instead of seeking in it, which has performance impact growing linearly with the table size. 

      Steps to Reproduce

      1. Install Crowd 3.0.1 on SQL Server

      Expected Results

      Query does not run slowly

      Actual Results

      Crowd can have performance issues

      Notes

      For example, the cwd_membership table is created with varchar column type.

      TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SS_DATA_TYPE
      crowd301 dbo cwd_membership id 2 numeric 19 21 0 10 0 NULL NULL 2 NULL NULL 1 NO 63
      crowd301 dbo cwd_membership parent_id 2 numeric 19 21 0 10 1 NULL NULL 2 NULL NULL 2 YES 108
      crowd301 dbo cwd_membership child_id 2 numeric 19 21 0 10 1 NULL NULL 2 NULL NULL 3 YES 108
      crowd301 dbo cwd_membership membership_type 12 varchar 32 32 NULL NULL 1 NULL NULL 12 NULL 32 4 YES 39
      crowd301 dbo cwd_membership group_type 12 varchar 32 32 NULL NULL 0 NULL NULL 12 NULL 32 5 NO 39
      crowd301 dbo cwd_membership parent_name 12 varchar 255 255 NULL NULL 0 NULL NULL 12 NULL 255 6 NO 39
      crowd301 dbo cwd_membership lower_parent_name 12 varchar 255 255 NULL NULL 0 NULL NULL 12 NULL 255 7 NO 39
      crowd301 dbo cwd_membership child_name 12 varchar 255 255 NULL NULL 0 NULL NULL 12 NULL 255 8 NO 39
      crowd301 dbo cwd_membership lower_child_name 12 varchar 255 255 NULL NULL 0 NULL NULL 12 NULL 255 9 NO 39
      crowd301 dbo cwd_membership created_date 11 datetime 23 16 3 NULL 1 NULL NULL 9 3 NULL 10 YES 111
      crowd301 dbo cwd_membership directory_id 2 numeric 19 21 0 10 0 NULL NULL 2 NULL NULL 11 NO 63

      Crowd then makes SQL calls such as

      (@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 bigint,@P3 nvarchar(4000)) SELECT internalme0_.child_name as col_0_0_, internalme0_.lower_child_name as col_1_0_ FROM cwd_membership internalme0_ WHERE internalme0_.lower_parent_name= @P0 AND internalme0_.membership_type= @P1 AND internalme0_.directory_id= @P2 AND internalme0_.group_type= @P3 ORDER BY internalme0_.lower_child_name

      The query is using Unicode data types (nvarchar), but the table is ansi (varchar), causing a plan warning and might be causing slow performance.

      The table is created with

      com/atlassian/crowd/model/membership/InternalMembership.hbm.xml
      <property name="lowerParentName" column="lower_parent_name" type="string" length="255" not-null="true" index="idx_mem_dir_parent,idx_mem_dir_parent_child"/>
<property name="membershipType" column="membership_type" type="MembershipType" length="32" unique-key="uk_mem_parent_child_type" index="idx_mem_dir_parent_child,idx_mem_dir_parent,idx_mem_dir_child"/>
<property name="groupType" column="group_type" type="GroupType" length="32" not-null="true"/>

      Workaround

      None

            Assignee:
            Unassigned
            Reporter:
            Mareusz (Inactive)
            Votes:
            3 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: