从lambda表达式生成sql server查询的替代方案
从lambda表达式生成sql server查询的替代方案
提问于 2011-11-29 11:26:06
我已经编写了一个lambda表达式，它可以产生预期的结果，但是它会生成一个绝对庞大的sql查询，而且它的性能很差。有关io/时间统计信息，请参阅最底部。
有没有其他方法来实现下面的查询？
select distinct(searchterms) as SearchTerms, max(totalresults) FROM cmsSearchLog
where totalresults != 0 and searchterms like 'de%' group by searchterms
order by max(totalresults) descc#代码片段：
// current lamda expression; has bad performance compared to above query
List<SearchTerm> existingSearchTerms1 = context.cmsSearchLogs.Where(oq =>
context.cmsSearchLogs.Where(q =>
q.SearchTerms.ToLower().Contains(terms.ToLower()) && q.TotalResults != 0)
.Select(s => s.SearchTerms)
.Distinct()
.Contains(oq.SearchTerms))
.Select(a => new { a.SearchTerms, a.TotalResults })
.GroupBy(gb => gb.SearchTerms)
.OrderByDescending(ob => ob.Max(m => m.TotalResults))
.Select(s => new SearchTerm()
{
SearchTerms = s.FirstOrDefault().SearchTerms,
TotalResults = s.FirstOrDefault().TotalResults
}
)
.ToList();
// get the suggestions back as a list of strings
List<string> suggestions = Enumerable.Range(0,
existingSearchTerms1.Count())
.Select(x => existingSearchTerms1.ElementAt(x).SearchTerms).ToList();这是保存查询结果的私有类
private class SearchTerm
{
public string SearchTerms { get; set; }
public int TotalResults { get; set; }
}lambda表达式生成的sql非常庞大：
SELECT
[Project13].[C2] AS [C1],
[Project13].[C3] AS [C2],
[Project13].[C4] AS [C3]
FROM ( SELECT
[Project12].[C1] AS [C1],
1 AS [C2],
[Project12].[C2] AS [C3],
[Project12].[C3] AS [C4]
FROM ( SELECT
[Project8].[C1] AS [C1],
[Project8].[C2] AS [C2],
(SELECT TOP (1)
[Extent5].[TotalResults] AS [TotalResults]
FROM [dbo].[cmsSearchLog] AS [Extent5]
WHERE ( EXISTS (SELECT 1 AS [C1]
FROM ( SELECT DISTINCT
[Extent6].[SearchTerms] AS [SearchTerms]
FROM [dbo].[cmsSearchLog] AS [Extent6]
WHERE (( CAST(CHARINDEX(LOWER('dew'),
LOWER([Extent6].[SearchTerms])) AS int)) > 0)
AND (0 <> [Extent6].[TotalResults])
) AS [Distinct3]
WHERE [Distinct3].[SearchTerms] = [Extent5].[SearchTerms]
)) AND ([Project8].[SearchTerms] = [Extent5].[SearchTerms]))
AS [C3]
FROM ( SELECT
[Project7].[C1] AS [C1],
[Project7].[SearchTerms] AS [SearchTerms],
[Project7].[C2] AS [C2]
FROM ( SELECT
[Project3].[C1] AS [C1],
[Project3].[SearchTerms] AS [SearchTerms],
(SELECT TOP (1)
[Extent3].[SearchTerms] AS [SearchTerms]
FROM [dbo].[cmsSearchLog] AS [Extent3]
WHERE ( EXISTS (SELECT 1 AS [C1] FROM ( SELECT DISTINCT
[Extent4].[SearchTerms] AS [SearchTerms]
FROM [dbo].[cmsSearchLog] AS [Extent4]
WHERE (( CAST(CHARINDEX(LOWER('dew'),
LOWER([Extent4].[SearchTerms])) AS int)) > 0)
AND (0 <> [Extent4].[TotalResults])) AS [Distinct2]
WHERE [Distinct2].[SearchTerms] = [Extent3].[SearchTerms]
)) AND ([Project3].[SearchTerms] = [Extent3].[SearchTerms])) AS [C2]
FROM ( SELECT
[GroupBy1].[A1] AS [C1],
[GroupBy1].[K1] AS [SearchTerms]
FROM ( SELECT
[Extent1].[SearchTerms] AS [K1],
MAX([Extent1].[TotalResults]) AS [A1]
FROM [dbo].[cmsSearchLog] AS [Extent1]
WHERE EXISTS (SELECT 1 AS [C1]
FROM ( SELECT DISTINCT [Extent2].[SearchTerms]
AS [SearchTerms] FROM [dbo].[cmsSearchLog] AS [Extent2]
WHERE (( CAST(CHARINDEX(LOWER('dew'),
LOWER([Extent2].[SearchTerms])) AS int)) > 0)
AND (0 <> [Extent2].[TotalResults])) AS [Distinct1]
WHERE [Distinct1].[SearchTerms] = [Extent1].[SearchTerms])
GROUP BY [Extent1].[SearchTerms]) AS [GroupBy1]
) AS [Project3]
) AS [Project7]
) AS [Project8]
) AS [Project12]
) AS [Project13]
ORDER BY [Project13].[C1] ASC我在打开io和时间统计的情况下执行了两个查询，结果如下所示。(注意: lambda生成的查询是第一个，我的手写查询第二个)所以这证实了我的怀疑，与我实际想要的查询相比，生成的查询执行得很糟糕。
(8 row(s) affected)
Table 'cmsSearchLog'. Scan count 6, logical reads 106, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
(7 row(s) affected)
Table 'cmsSearchLog'. Scan count 1, logical reads 5, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.回答 5
Stack Overflow用户
回答已采纳
发布于 2011-11-29 11:50:20
尝试此查询，而不是当前的LINQ查询：
var query = from x in context.cmsSearchLog
where totalresults != 0 &&
searchterms.BeginsWith("de")
group x by x.searchterms into terms
select new {
SearchTerms = terms.Key(),
TotalResults = terms.Max(t => t.totalresults)
};我还没有测试过它，但我假设它会生成一个非常有效的查询并返回所需的结果。
Stack Overflow用户
发布于 2011-11-29 11:32:51
LINQ转换(可以是LINQ到SQL、实体框架等)是关于高效开发的。它允许(在理论上)更具可读性和可维护性的代码，并降低了运行时数据库错误的可能性，等等。LINQ与性能无关。LINQ通常提供“足够好”的性能，但它永远不会超越手工编码的查询或存储过程等更接近金属的东西。
也就是说，您的查询返回不同的行数，因此其中一个(或两个)是错误的；第一个查询产生8行，而第二个查询产生7行。您不能很好地比较提供不同结果的查询！
Stack Overflow用户
发布于 2011-11-29 11:38:15
对于复杂或性能密集型查询，不要觉得不能创建视图或用户定义函数并映射到它们。在这种情况下，您甚至可以使用存储的proc并映射到它。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/8305461
复制相关文章
相似问题
腾讯云开发者
