Type choices in sp_executesql_sql_type cause poor query performance.

[Philgo68]

Using varchar(max) as a fall back parameter type causes SQL Server to make poor execution plan choices, dramatically slowing down queries.

If the type selection in sp_executesql_sql_type was better when the :sqlserver_type was missing, the system would perform MUCH better in these odd situations. Instead the quick logic falls to nvarchar(max) pretty easily:

def sp_executesql_sql_type(attr)
  return attr.type.sqlserver_type if attr.type.respond_to?(:sqlserver_type)
  case value = attr.value_for_database
  when Numeric
    value > 2_147_483_647 ? 'bigint'.freeze : 'int'.freeze
  else
    "nvarchar(max)".freeze
  end
end

We are querying against an ERP database we have little control over and have a "through:" relationship:

class WorkOrderAR
  has_many :operations,
    :primary_key => [:TYPE, :BASE_ID, :LOT_ID, :SPLIT_ID],
    :class_name => 'OperationAR',
    :foreign_key => [:WORKORDER_TYPE, :WORKORDER_BASE_ID, :WORKORDER_LOT_ID, :WORKORDER_SPLIT_ID],
    inverse_of: :work_order

  has_many :requirements, through: :operations, source: :direct_requirements
end

class OperationAR
  has_many :direct_requirements, -> {where(SUBORD_WO_SUB_ID: nil)},
    class_name: "RequirementAR",
    primary_key: [:WORKORDER_TYPE, :WORKORDER_BASE_ID, :WORKORDER_LOT_ID, :WORKORDER_SPLIT_ID, :WORKORDER_SUB_ID, :SEQUENCE_NO],
    foreign_key: [:WORKORDER_TYPE, :WORKORDER_BASE_ID, :WORKORDER_LOT_ID, :WORKORDER_SPLIT_ID, :WORKORDER_SUB_ID, :OPERATION_SEQ_NO],
    inverse_of: :operation
end

When loading Operations:

 workorder = WorkOrderAR.where({TYPE: 'W', BASE_ID: '2021023169', LOT_ID: '1', SPLIT_ID: '0', SUB_ID: '0'}).load.first
 workorder.operations.load

the sqlserver_type is in place and the query is fine:

EXEC sp_executesql N'SELECT [OPERATION].* FROM [OPERATION] WHERE [OPERATION].[WORKORDER_TYPE] = @0 AND [OPERATION].[WORKORDER_BASE_ID] = @1 AND [OPERATION].[WORKORDER_LOT_ID] = @2 AND [OPERATION].[WORKORDER_SPLIT_ID] = @3'
, N'@0 nchar(1), @1 nvarchar(30), @2 nvarchar(3), @3 nvarchar(3)', @0 = N'W', @1 = N'2021023169', @2 = N'1', @3 = N'0'

But when loading Requirements using the :through relationship

workorder.requirements.load

the sqlserver_type is missing and then query uses nvarchar(max):

EXEC sp_executesql N'SELECT [REQUIREMENT].* FROM [REQUIREMENT] INNER JOIN [OPERATION] ON [REQUIREMENT].[WORKORDER_TYPE] = [OPERATION].[WORKORDER_TYPE] AND [REQUIREMENT].[WORKORDER_BASE_ID] = [OPERATION].[WORKORDER_BASE_ID] AND [REQUIREMENT].[WORKORDER_LOT_ID] = [OPERATION].[WORKORDER_LOT_ID] AND [REQUIREMENT].[WORKORDER_SPLIT_ID] = [OPERATION].[WORKORDER_SPLIT_ID] AND [REQUIREMENT].[WORKORDER_SUB_ID] = [OPERATION].[WORKORDER_SUB_ID] AND [REQUIREMENT].[OPERATION_SEQ_NO] = [OPERATION].[SEQUENCE_NO] WHERE [OPERATION].[WORKORDER_TYPE] = @0 AND [OPERATION].[WORKORDER_BASE_ID] = @1 AND [OPERATION].[WORKORDER_LOT_ID] = @2 AND [OPERATION].[WORKORDER_SPLIT_ID] = @3 AND [REQUIREMENT].[SUBORD_WO_SUB_ID] IS NULL'
, N'@0 nvarchar(max), @1 nvarchar(max), @2 nvarchar(max), @3 nvarchar(max)', @0 = N'W', @1 = N'2021023169', @2 = N'1', @3 = N'0'

at least in our case, sql server then picks a terrible execution plan, ignoring the obvious indexes. Changing the parameters in the query from nvarchar(max) to nvarchar(40) takes the time down from 7+seconds to 50 ms.

We have patched in
"nvarchar(#{value.to_s.length + 1})".freeze
to replace the
"nvarchar(max)".freeze
in sp_executesql_sql_type and it has made active record / sql server usable for our application.

The only other types this procedure will return are int and bigint if the value is numeric. This also seems a bit naïve if a float or real got into this area of code, but does not come up in our app.