linq to sql 查询预编译

发布于 2024-11-27 15:15:28 字数 857 浏览 4 评论 0原文

如果 where 子句中有条件逻辑，则根据搜索参数的值，linq 可以构建不同的 sql 查询。

在这种情况下，如何利用查询预编译并将条件考虑在内？看起来必须生成多个预编译查询。然后需要根据输入参数触发正确的预编译查询。

大多数博客条目/文章都非常有限，并且仅在查询中没有条件逻辑时才处理 linq 查询预编译。然后他们只是在小字中提到查询预编译并不总是有益的，你最好不要使用它。

例如，如果 linq 查询中有一个条件参数，则可以构建 2 个不同的 sql 查询。那么为什么不预编译这两个 linq 查询并相应地重用呢？我确信，如果您多次重复使用不同的预编译查询，那么最终会节省一些处理能力。

我提出这个问题的原因是我目前正在开发一个具有相当复杂的搜索表单的网络应用程序。典型的搜索表单可以有大约 20 个字段（字符串、整数、下拉列表等）。 95% 的搜索使用默认参数。但其他搜索使用多个搜索参数。底层的 linq 查询将所有参数都考虑在内，语法非常好，特别是对于可空整数，例如： .Where(row=>!model.id.HasValue || row.id==model.id)。因此，根据是否 int? 可以产生两个不同的 sql 查询。 id是否有价值。我的 where 子句要复杂得多 - 它需要大约 20 个条件参数。

所以问题是：如果我根据条件参数预编译 linq 查询，是否会对性能有利？这将导致每个配置参数的组合产生一个预编译查询。因此，必须对参数进行分析，以确定预编译查询是否已经存在并且可以使用，或者是否需要创建新的预编译查询。

稍后可以进一步采取该解决方案。因此，当 Web 应用程序启动时，所有 linq 查询都将针对条件参数的所有可能组合进行主动预编译。或者，为了减少预编译查询的数量，我可以对一些最常用的参数组合使用预编译，并在更复杂的场景中强制使用非预编译查询。

原文

Depending on the value of search parameters linq can build different sql queries if there is conditional logic in the where clause.

How about taking advantage of query precompilation in this case and take the condition into the account? It looks like more then one precompiled query have to be produced. And then the correct precompiled query needs to be fired depending on the input parameters.

Most blog entries/articles are quite limited and are dealing with linq query precompilation only when there is no conditional logic in the query. And then they just mention in the small print that query precompilation is not always beneficial and you might be better off not using it.

For example if there is one conditional parameter in the linq query then 2 different sql queries can be built. So why not to precompile those two linq queries and reuse then accordingly? I'm sure that if you reuse different precompiled queries multiple times then it will save some processing power in the very end.

Reason why I'm raising this is that I'm currently working on a webapp with quite complex search forms. Typical search form can have about 20 fields ( strings, integers, dropdowns etc. ). 95% of searches use default parameters. But other searches are using multiple search parameters. The underlying linq query is taking all parameters into the account, syntax is quite nice, especially with nullable integers, for example : .Where(row=>!model.id.HasValue || row.id==model.id). So two different sql queries can be produced depending on whether int? id has value or not. And my where clause is much more complex - it takes about 20 conditional parameters.

So the question is : is it going to be beneficial performance wise if I precompile linq query depending on conditional parameters? This would result in a precompiled query per combination of confitional parameters. So parameters would have to be analyzed in order to work out whether precompiled query already exists and it can be used or if new precompiled query needs to be created.

This solution can be taken one step further later on. So when the web application starts all linq queries are going to be proactively precompiled for all possible combinations of conditional parameters. Or, in order to reduce the number of precompiled queries, I could use precompilation for few most commonly used confitional parameters combination and force using not precompiled queries for more complex scenarios.

分享到QQ

分享到微博