针对 SQL Server 的 LINQ 查询性能缓慢

Question

我想查找以搜索框中输入的字符开头的地理名称。一些地名在其他语言中还有替代名称。这些备用名称存储在单独的表中。

GN_Name 1 - 0:N GN_AlternateName

(PK)GN_Name.GeoNameId == (FK)GN_AlternateName.GeoNameId

我想首先搜索 GN_AlternateName.AlternateName 中的名称，如果不存在，则使用相应的 GN_Name.Name。

我编写了以下 LINQ 查询：

return (from name in db.GN_Name
        where name.CountryCode == "se"
        join alt in db.GN_AlternateName 
        on name.GeoNameId equals alt.GeoNameId into outer
        from alt in outer.DefaultIfEmpty()
        where ((alt.IsoLanguage == "sv" && 
                alt.AlternateName.StartsWith(query)) || 
                name.Name.StartsWith(query))
        select new GeoNameModel { 
            Language = alt.IsoLanguage, 
            Name = (alt == null ? name.Name : alt.AlternateName),
            FeatureClass = name.FeatureClass, 
            FeatureCode = name.FeatureCode, 
            GeoNameId = name.GeoNameId, 
            UniqueName = name.UniqueName,
            UniqueCount = name.UniqueCount}).Take(HB.AutoCompleteCount);

转换为以下 SQL：

exec sp_executesql N'SELECT 
[Limit1].[GeoNameId] AS [GeoNameId], 
[Limit1].[IsoLanguage] AS [IsoLanguage], 
[Limit1].[C1] AS [C1], 
[Limit1].[FeatureClass] AS [FeatureClass], 
[Limit1].[FeatureCode] AS [FeatureCode], 
[Limit1].[UniqueName] AS [UniqueName], 
[Limit1].[UniqueCount] AS [UniqueCount]
FROM ( SELECT TOP (5) 
    [Extent1].[GeoNameId] AS [GeoNameId], 
    [Extent1].[FeatureClass] AS [FeatureClass], 
    [Extent1].[FeatureCode] AS [FeatureCode], 
    [Extent1].[UniqueName] AS [UniqueName], 
    [Extent1].[UniqueCount] AS [UniqueCount], 
    CASE WHEN ([Extent2].[AlternateNameId] IS NULL) THEN [Extent1].[Name] ELSE [Extent2].[AlternateName] END AS [C1], 
    [Extent2].[IsoLanguage] AS [IsoLanguage]
    FROM  [dbo].[GN_Name] AS [Extent1]
    LEFT OUTER JOIN [dbo].[GN_AlternateName] AS [Extent2] ON [Extent1].[GeoNameId] = [Extent2].[GeoNameId]
    WHERE (''se'' = [Extent1].[CountryCode]) AND (((''sv'' = [Extent2].[IsoLanguage]) AND ([Extent2].[AlternateName] LIKE @p__linq__0 ESCAPE N''~'')) OR ([Extent1].[Name] LIKE @p__linq__1 ESCAPE N''~''))
)  AS [Limit1]',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'ja%',@p__linq__1=N'ja%'

我真的看不出它有什么问题，但大约需要 5 秒才能完成。

我应该添加一些索引吗？也许设置索引视图？ 我的 SQL 服务器知识有限，我很想回到一些真正的编码；）

热烈欢迎任何建议！

更新 我正在使用 SQL Server 2008。按照 taylonr 的说明，我得到了以下结果。

有 3 个“部分”，占总数的 100%。然而，我不知道如何使用这些统计数据。

更新2

SSMS执行计划推荐了以下索引：

CREATE NONCLUSTERED INDEX IX_GN_Name_CountryCode
ON [dbo].[GN_Name] ([CountryCode])
INCLUDE ([GeoNameId],[Name],[FeatureClass],[FeatureCode],[UniqueName],[UniqueCount])

我添加了它，查询现在运行得更好了！

更新3 taylonr 建议仅使用一个 LIKE 子句。我不知道如何实现这一点。有人准备迎接挑战吗？

Answer 1

首先，我会小心地称呼 SQL 而不是“真正的编码”，因为它看起来像是那里的改进可以帮助你；）（我是一个 C# 人员，而不是 SQL 专家，只是说......）

进入您的 SSMS，并获取生成的查询。

将其复制到新的查询窗口中。

现在在运行之前做两件事。
1. 进入查询菜单并单击“包括客户统计信息”
2. 进入“查询”菜单并单击“包括实际执行计划”

现在运行您的查询。

查询完成后，检查客户端统计信息中标有“服务器回复等待时间”的项目，这是服务器为此查询执行的时间量（以毫秒为单位）。

“总执行时间”是客户端和客户端所花费的时间。服务器来传送数据。

这将使您了解服务器上的时间。例如，如果该时间为 10 毫秒，而代码执行需要 5 秒，则 Sql 可能不是问题所在。

接下来，打开您的执行计划选项卡。这将向您展示 SQL 如何生成此数据。例如，如果它花费 100% 的时间进行表扫描（而不是索引扫描），那么您可能需要添加一些索引。

查看执行计划，看看哪个执行计划的百分比最高。这将使您了解可以在哪里优化查询。

我猜想，拥有两个单独的“喜欢”声明可能没有多大帮助。 Like 语句的性能不如等式，例如

WHERE name = 'taylonr'

比

WHERE name like 'taylo%'

Answer 2

您可以在数据库引擎优化顾问中运行查询。

它会在分析查询后提出索引建议。

Answer 3

您可以尝试将其分成 3 个左右的单独请求吗？然后看看其中有没有一个异常长。将所有工作放入一个 return() 中会使诊断变得非常困难。

Answer 4

我认为问题在于 EF 正在将参数 @p_linq_1 nvarchar(4000) 转换为 nvarchar，我的猜测是在数据库中它们存储为 varchar 强制 sql server 强制转换它们。

我遇到了同样的问题。尝试在查询分析器中运行sql并将参数类型更改为varchar，看看是否运行得更快。

Answer 5

可能有帮助的一件事是，如果翻译的 from 子句看起来像这样，

FROM [dbo].[GN_Name] AS [Extent1] 
LEFT OUTER JOIN [dbo].[GN_AlternateName] AS [Extent2] 
ON [Extent1].[GeoNameId] = [Extent2].[GeoNameId]) AS [Limit1] 
AND ( 'sv' = [Extent2].[IsoLanguage] ) 

我猜这意味着 linq 会在这里。

join alt in db.GN_AlternateName 
on name.GeoNameId equals alt.GeoNameId && alt.IsoLanguage == 'sv'

另外，我会考虑以下字段的索引。但 Tuning Advisor 确实应该以一种或另一种方式告诉您。

GN_Name.GeoNameId
GN_AlternateName.GeoNameId
GN_Name.CountryCode
GN_Name.Name
GN_AlternateName.AlternateName