Linq to SQL nvarchar 问题 [英] Linq to SQL nvarchar problem

问题描述

我在 Linq to SQL 中发现了一个巨大的性能问题.

I have discovered a huge performance problem in Linq to SQL.

使用字符串从表中选择时，传递给sql server的参数总是nvarchar，即使sql表是varchar.这会导致表扫描而不是查找，这是一个巨大的性能问题.

When selecting from a table using strings, the parameters passed to sql server are always nvarchar, even when the sql table is a varchar. This results in table scans instead of seeks, a massive performance issue.

var q = (
   from a in tbl
   where a.index == "TEST"
   select a)

var qa = q.ToArray();

参数作为 nvarchar 传递，导致整个索引在使用前从 varchar 转换为 nvarchar.

The parameter is passed through as a nvarchar, which results in the entire index being converted from varchar to nvarchar before being used.

如果参数是 varchar，则查找速度非常快.

If the parameter is a varchar it's a very fast seek.

有什么办法可以覆盖或改变它吗?

Is there any way to override or change this?

谢谢问候克雷格.

推荐答案

嗯.这是 LINQ-to-SQL 的 pre-RTM 版本的一个已知错误，但从我在网上阅读的内容来看，这是 RTM 中相等比较的一个固定问题(尽管对于 Contains() 比较仍然存在问题).

Hmmm. This was a known bug with pre-RTM builds of LINQ-to-SQL, but from what I read online this was a fixed problem for equality comparisons in RTM (although still broken for Contains() comparisons).

无论如何，这是 MSDN 论坛上的一个线程，其中详细介绍了一些解决方法:http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/4276ecd2-31ff-4cd0-82ea-7a22ce25308b

Regardless, here's a thread on MSDN forums with some workarounds detailed: http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/4276ecd2-31ff-4cd0-82ea-7a22ce25308b

我最喜欢的解决方法是这个:

The workaround I like most is this one:

//define a query
IQueryable<Employee> emps = from emp in dc2.Employees where emp.NationalIDNumber == "abc" select emp;

//get hold of the SQL command translation of the query...
System.Data.Common.DbCommand command = dc2.GetCommand(emps);

//change param type from "string" (nvarchar) to "ansistring" (varchar)
command.Parameters[0].DbType = DbType.AnsiString; 
command.Connection = dc2.Connection;

//run
IEnumerable<Employee> emps2 = dc2.Translate<Employee>(command.ExecuteReader());

顺便说一句，我看到的另一种情况是在一个值分布奇数的表中(例如，表的 50% 具有相同的值)，这意味着，鉴于 SQL Server 在计划编译时不知道该参数，表扫描是最好的计划.如果您的分布也异常，则上述解决方法将不起作用，因为扫描不会来自丢失的转换，而是来自参数化本身.在这种情况下，我知道的唯一解决方法是使用 OPTIMIZE FOR 提示并手动指定 SQL.

BTW, another case I saw this happening was in a table with odd distribution of values (e.g. 50% of table had the same value) meaning that, given the parameter is unknown to SQL Server at plan compilation time, a table scan was the best plan available. If your distribution is also unusual, then the workarounds above won't work, since the scan won't be coming from the missing conversion but rather from the parameterization itself. In that case, the only workaround I'd know would be to use an OPTIMIZE FOR hint and manually specify the SQL.

这篇关于Linq to SQL nvarchar 问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！