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?

谢谢
问候
克雷格。

Thanks Regards Craig.

推荐答案

嗯。这是LINQ-to-SQL的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());

BTW，我看到这种情况发生的另一个案例是在一个奇怪的价值分布表中（例如50％） table具有相同的值）意味着，如果在计划编译时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屋！