Search code examples
subsonicparameterized

SubSonic - Generate SQL uses nvarchar instead of varchar which causes Index Scan instead of Seek

I am using the SubSonic SimpleRepository template for my application. I have created an ASP .NET WebForms project in VS2010 pointing to a SQL 2000 database.

I am having an issue where SubSonic is always using nvarchar in the parameterized queries instead of varchar. This causes SQL to do an Index Scan instead of an Index Seek. I have taken the SQL from the Profiler and altered it to make the parameters varchar like the table's fields and it executes very quickly (<1 second versus 8 seconds).

SubSonic Query from Profiler

exec sp_executesql N'SELECT [t0].[ADDRESS_L1], [t0].[ADDRESS_L2], [t0].[ADDRESS_L3], [t0].[CITY], [t0].[COUNTRY] FROM [aveadmin].[SAPADD] AS t0 WHERE (([t0].[SITE_ID] = @p0) AND ((([t0].[ADDRESS_TYPE] = @p1) AND 1 <> 0) OR (([t0].[ADDRESS_TYPE] = @p2) AND 0 <> 0)))', N'@p0 nvarchar(16),@p1 nvarchar(2),@p2 nvarchar(2)', @p0 = N'BCF8A0A27E543EE1', @p1 = N'00', @p2 = N'03'

Manually Modified Query

exec sp_executesql N'SELECT [t0].[ADDRESS_L1], [t0].[ADDRESS_L2], [t0].[ADDRESS_L3], [t0].[CITY], [t0].[COUNTRY] FROM [aveadmin].[SAPADD] AS t0 WHERE (([t0].[SITE_ID] = @p0) AND ((([t0].[ADDRESS_TYPE] = @p1) AND 1 <> 0) OR (([t0].[ADDRESS_TYPE] = @p2) AND 0 <> 0)))', N'@p0 varchar(16),@p1 varchar(2),@p2 varchar(2)', @p0 = N'BCF8A0A27E543EE1', @p1 = N'00', @p2 = N'03'

The SITE_ID and ADDRESS_TYPE are varchars. Is there a way to force the query to use varchar instead of nvarchar?

Solution

  • Is there a way to force the query to use varchar instead of nvarchar?

    You will have to modify the source code of SubSonic to change this behavior.