针对SQL Server的LINQ查询性能下降

I want to find geographic names that starts with characters entered in a search box. Some geographic names have alternative names in other languages. These alternative names are stored in a separate table.

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

GN_Name 1 - 0:N GN_AlternateName

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

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

I want to search the name in GN_AlternateName.AlternateName first and if that doesn't exist, use the corresponding GN_Name.Name.

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

I wrote following LINQ query:

我写了以下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);

That translates into the following SQL:

这转换为以下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%'

I can't really see whats wrong with it, but it takes around 5 seconds to complete.

我真的不知道它有什么问题,但需要大约5秒才能完成。

Should i add some index? Maybe set up an indexed view? My SQL server knowledge is limited and i would love to get back to some real coding ;)

我应该添加一些索引吗?也许设置一个索引视图?我的SQL服务器知识有限,我很想回到一些真正的编码;)

Any suggestions warmly appreciated!

任何建议热烈赞赏!

UPDATE I'm using SQL server 2008. Following the instructions of taylonr i got the following results. 针对SQL Server的LINQ查询性能下降

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

There are 3 "parts" that make up 100% of the total. I, however, don't have a clue on how to use these statistics.

有3个“部分”占总数的100%。但是,我没有关于如何使用这些统计数据的线索。

UPDATE 2

SSMS Execution Plan recommended the following index:

SSMS执行计划建议使用以下索引:

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

I added it and the query now runs much better!

我添加了它,查询现在运行得更好!

UPDATE 3 taylonr suggests using only one LIKE clause. I'm not sure how to accomplish this. Anyone up for the challenge?

更新3 taylonr建议只使用一个LIKE子句。我不知道如何做到这一点。有谁接受挑战?

5 个解决方案

#1

First, I'd be careful about calling SQL not "real coding" since it looks like improvement there could help you out ;) (I'm a C# guy, and not a SQL expert, just sayin...)

首先,我要小心调用SQL而不是“真正的编码”,因为看起来好像可以帮助你改进;)(我是一个C#人,而不是SQL专家,只是说...)

Go in to your SSMS, and take the query that is generated.

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

Copy that into a new Query Window.

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

Now do 2 things before your run it. 1. Go into the Query menu and click "Include Client Statistics" 2. Go into the Query menu and click "Include Actual Execution plan"

现在做两件事就跑了。 1.进入“查询”菜单,然后单击“包含客户端统计信息”.2。进入“查询”菜单,然后单击“包括实际执行计划”

Now run your query.

现在运行您的查询。

When the query is done, check the Client statistics for the item labeled "Wait time on server replies" this is the amount of time (in ms) that the server is executing for this query.

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

The "Total Execution time" is the amount of time it took the client & server to communicate the data.

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

That will give you an idea of what the time is like on the server. For example, if that's 10ms and it takes 5s to execute from your code, Sql might not be the problem.

这将让您了解服务器上的时间。例如,如果那是10ms并且从代码执行需要5s,那么Sql可能不是问题。

Next, open your execution plan tab. This will show you how SQL generated this data. For example, if it spent 100% of the time doing a table scan (as opposed to an index scan) then you might want to add some indexes.

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

Take a look at the execution plan and see what has the highest percentage. This will give you an idea of where you might be able to optimize your query.

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

I would guess that having the two separate 'like' statements probably isn't helping much. Like statements aren't as performant as an equality, e.g.

我猜想有两个单独的“喜欢”的陈述可能没什么帮助。类似的陈述不像平等那样有效,例如:

WHERE name = 'taylonr'

is quicker than

比...更快

WHERE name like 'taylo%'

#2

You could run the query in Database Engine Tuning Advisor.

您可以在Database Engine Tuning Advisor中运行查询。

It will make index suggestions after analyzing the query.

它将在分析查询后生成索引建议。

#3

Can you try breaking this into 3 or so separate requests? Then see if any one of them is abnormally long. Putting all your work into one single return() makes it really hard to diagnose things.

你能尝试将其分成3个左右的单独请求吗?然后看看它们中的任何一个是否异常长。将所有工作放在一个单一的return()中会使得诊断事项变得非常困难。

#4

I think the problem is that EF is turning your parameters @p_linq_1 nvarchar(4000) into nvarchar and my guess is that in the database they are stored as varchar forcing sql server to cast them.

我认为问题是EF正在将你的参数@ p_linq_1 nvarchar(4000)转换为nvarchar,我的猜测是在数据库中它们被存储为varchar,迫使sql server抛出它们。

I ran into the same problem. Try running the sql in query analyzer and change the types of the parameters to varchar and see if it runs faster.

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

#5

One thing that could help is if the translated from clause looked like this

有一点可能有帮助,如果翻译的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] )

I'm guess that would mean the linq would be here.

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

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

Also I would consider indexes on the following fields. But the Tuning Advisor should really tell you one way or another.

我还会考虑以下字段的索引。但是Tuning Advisor应该以某种方式告诉你。

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

#1