Linq to SQL Performance using contains

I'm overloading a vb.net search procedure which queries a SQL database. One of the older methods i'm using as a comparison uses a Stored Procedure to perform the search and return the query. My new method uses linq.

我正在重载查询SQL数据库的vb.net搜索过程。我用作比较的旧方法之一使用存储过程来执行搜索并返回查询。我的新方法使用linq。

I'm slightly concerned about the performance when using contains queries with linq. I'm looking at equally comparable queries using both methods.
Basically having 1 where clause to Here are some profiler results;

当使用包含linq的查询时,我有点担心性能。我正在使用这两种方法查看同样可比较的查询。基本上有1个where子句,这里有一些分析器结果;

Where name = "ber10rrt1"

Linq query : 24reads

Linq查询:24reads

Stored query : 111reads

存储查询:111reads

Where name = "%ber10%"

名称=“%ber10%”
Linq query : 53174reads

Linq查询:53174reads
Stored proc query : 23386reads

存储过程查询:23386reads

Forgetting for a moment, the indexes (not my database)... The fact of the matter is that both methods are fundamentally performing the same query (albeit the stored procedure does reference a view for [some] of the tables).

遗忘了一下,索引(不是我的数据库)......事实上,两个方法都是从根本上执行相同的查询(尽管存储过程确实引用了[某些]表的视图)。

Is this consitent with other peoples experiance of linq to sql?

这是否与其他人的linq to sql的实验相结合?

Also, interestingly enough;

还有,有趣的是;

Using like "BER10%"

使用像“BER10%”
resultset.Where(Function(c) c.ci.Name.StartsWith(name))

Results in the storedproc using 13125reads and linq using 8172reads

使用13125reads和linq使用8172reads在storedproc中获得结果

1 个解决方案

#1

I'm not sure there is enough there for a complete analysis... I'm assuming we are talking about string.Contains/string.StartsWith here (not List<T>.Contains).

我不确定是否有足够的完整分析...我假设我们正在讨论string.Contains / string.StartsWith here(而不是List .Contains)。

If the generated TSQL is similar, then the results should be comparable. There are a few caveats to this - for example, is the query column a calculated+persisted value? If so, the SET options must be exact matches for it to be usable "as is" (otherwise it has to re-calculate per row).

如果生成的TSQL类似,那么结果应该是可比较的。对此有一些警告 - 例如,查询列是计算的+持久值吗?如果是这样,SET选项必须完全匹配才能“按原样”使用(否则它必须每行重新计算)。

So: what is the TSQL from the SP and LINQ? Are they directly comparable? You mention a VIEW - I'm guessing this could make a big difference if (for example) it filters out data (either via a WHERE or an INNER JOIN).

那么:SP和LINQ的TSQL是什么?它们是直接可比的吗?你提到了一个视图 - 我猜这可能会产生很大的不同,如果(例如)它过滤掉数据(通过WHERE或INNER JOIN)。

Also - LIKE clauses starting % are rarely a good idea - not least, it can't make effective use of any index. You might have better performance using "full text search"; but this isn't directly available via LINQ, so you'll have to wrap it in an SP and expose the SP via the LINQ data-context (just drag the SP into the designer).

另外 - 启动%的LIKE子句很少是一个好主意 - 尤其是,它无法有效地使用任何索引。使用“全文搜索”可能会有更好的表现;但这不能通过LINQ直接获得,因此您必须将其包装在SP中并通过LINQ数据上下文公开SP(只需将SP拖到设计器中)即可。

My money is on the VIEW (and the other code in the SP) being the main difference here.

我的钱在VIEW上(以及SP中的其他代码)是这里的主要区别。

#1