Linq to SQL nvarchar 问题

发布于 2024-08-11 06:14:16 字数 390 浏览 4 评论 0原文

我发现 Linq to SQL 存在巨大的性能问题。

使用字符串从表中进行选择时，传递到 sql server 的参数始终为 nvarchar，即使 sql 表是 varchar 也是如此。这会导致表扫描而不是查找，这是一个巨大的性能问题。

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

var qa = q.ToArray();

该参数作为 nvarchar 传递，这会导致整个索引在使用之前从 varchar 转换为 nvarchar。

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

有什么办法可以覆盖或改变这个吗？

谢谢问候克雷格.

原文

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

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();

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

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

Is there any way to override or change this?

Thanks
Regards
Craig.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

A君 2024-08-18 06:14:16

嗯。这是 LINQ-to-SQL 的 RTM 之前版本的一个已知错误，但从我在线阅读的内容来看，这是 RTM 中相等比较的一个固定问题（尽管对于 Contains() 比较仍然有问题）。

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

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

//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。

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).

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, 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.

回复收藏 0 原文

~没有更多了~