logo标签列表

LINQ to SQL中的StartsWith性能如何？使用索引列改善性能。

c#sqllinqentity
7

我使用实体代码优先。

  • 源目录编号
  • 禁用
  • 类别路径

表中有 40,000 行数据，

我的问题是查询需要 40 秒！！

var result = DBContext.Set<SourceProduct>()
            .Include(x => x.SalesHistories, x => x.SourceCatalog)
            .Where(p => p.SourceCatalogId == 2)
            .where(p => p.Disabled == false)
            .where(x => x.CategoryPath.StartsWith("MyPath"))
            .orderby(x => x.ShortDesignation)
            .Skip(1)
            .Take(10)
            .toList();

通过SQL Profiler进行SQL查询：

exec sp_executesql N'SELECT TOP (10) 
[Project1].[SourceProductId] AS [SourceProductId], 
[Project1].[SourceSKU] AS [SourceSKU], 
[Project1].[SourceCatalogId] AS [SourceCatalogId], 
[Project1].[ManufacturerReference] AS [ManufacturerReference], 
[Project1].[Disabled] AS [Disabled], 
[Project1].[EAN] AS [EAN], 
[Project1].[ShortDesignation] AS [ShortDesignation], 
[Project1].[FullDesignation] AS [FullDesignation], 
[Project1].[Description] AS [Description], 
[Project1].[Url] AS [Url], 
[Project1].[CategoryPath] AS [CategoryPath], 
[Project1].[Condition] AS [Condition], 
[Project1].[BuyingPriceHT] AS [BuyingPriceHT], 
[Project1].[ShippingPriceHT] AS [ShippingPriceHT], 
[Project1].[PublicSellingPriceHT] AS [PublicSellingPriceHT], 
[Project1].[PictureUrl1] AS [PictureUrl1], 
[Project1].[PictureUrl2] AS [PictureUrl2], 
[Project1].[PictureUrl3] AS [PictureUrl3], 
[Project1].[PictureUrl4] AS [PictureUrl4], 
[Project1].[Quantity] AS [Quantity], 
[Project1].[AddDate] AS [AddDate], 
[Project1].[UpdateDate] AS [UpdateDate], 
[Project1].[Followers] AS [Followers]
FROM ( SELECT [Project1].[SourceProductId] AS [SourceProductId], [Project1].[SourceSKU] AS [SourceSKU], [Project1].[SourceCatalogId] AS [SourceCatalogId], [Project1].[ManufacturerReference] AS [ManufacturerReference], [Project1].[Disabled] AS [Disabled], [Project1].[EAN] AS [EAN], [Project1].[ShortDesignation] AS [ShortDesignation], [Project1].[FullDesignation] AS [FullDesignation], [Project1].[Description] AS [Description], [Project1].[Url] AS [Url], [Project1].[CategoryPath] AS [CategoryPath], [Project1].[Condition] AS [Condition], [Project1].[BuyingPriceHT] AS [BuyingPriceHT], [Project1].[ShippingPriceHT] AS [ShippingPriceHT], [Project1].[PublicSellingPriceHT] AS [PublicSellingPriceHT], [Project1].[PictureUrl1] AS [PictureUrl1], [Project1].[PictureUrl2] AS [PictureUrl2], [Project1].[PictureUrl3] AS [PictureUrl3], [Project1].[PictureUrl4] AS [PictureUrl4], [Project1].[Quantity] AS [Quantity], [Project1].[AddDate] AS [AddDate], [Project1].[UpdateDate] AS [UpdateDate], [Project1].[Followers] AS [Followers], row_number() OVER (ORDER BY [Project1].[ShortDesignation] ASC) AS [row_number]
    FROM ( SELECT 
        [Extent1].[SourceProductId] AS [SourceProductId], 
        [Extent1].[SourceSKU] AS [SourceSKU], 
        [Extent1].[SourceCatalogId] AS [SourceCatalogId], 
        [Extent1].[ManufacturerReference] AS [ManufacturerReference], 
        [Extent1].[Disabled] AS [Disabled], 
        [Extent1].[EAN] AS [EAN], 
        [Extent1].[ShortDesignation] AS [ShortDesignation], 
        [Extent1].[FullDesignation] AS [FullDesignation], 
        [Extent1].[Description] AS [Description], 
        [Extent1].[Url] AS [Url], 
        [Extent1].[CategoryPath] AS [CategoryPath], 
        [Extent1].[Condition] AS [Condition], 
        [Extent1].[BuyingPriceHT] AS [BuyingPriceHT], 
        [Extent1].[ShippingPriceHT] AS [ShippingPriceHT], 
        [Extent1].[PublicSellingPriceHT] AS [PublicSellingPriceHT], 
        [Extent1].[PictureUrl1] AS [PictureUrl1], 
        [Extent1].[PictureUrl2] AS [PictureUrl2], 
        [Extent1].[PictureUrl3] AS [PictureUrl3], 
        [Extent1].[PictureUrl4] AS [PictureUrl4], 
        [Extent1].[Quantity] AS [Quantity], 
        [Extent1].[AddDate] AS [AddDate], 
        [Extent1].[UpdateDate] AS [UpdateDate], 
        [Extent1].[Followers] AS [Followers]
        FROM [dbo].[SourceProducts] AS [Extent1]
        WHERE ([Extent1].[SourceCatalogId] = @p__linq__0) AND (0 = [Extent1].[Disabled]) AND ([Extent1].[CategoryPath] LIKE @p__linq__1 ESCAPE N''~'')
    )  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[ShortDesignation] ASC',N'@p__linq__0 bigint,@p__linq__1 nvarchar(4000)',@p__linq__0=2,@p__linq__1=N'MyPath%'

在最后一个where子句之前，如果我删除以下内容：“escape N''~''”，则会发生什么？
WHERE ([Extent1].[SourceCatalogId] = @p__linq__0) AND (0 = [Extent1].[Disabled]) AND ([Extent1].[CategoryPath] LIKE @p__linq__1 ESCAPE N''~'')

查询需要4秒钟。

这正常吗？使用索引？如何使用startWith解决它？

编辑

类别路径的索引属性：

[Index("IX_SourceProduct_SourceCatalogId_Disabled_CategoryPath", 3), StringLength(400)]
    public string CategoryPath { get; set; }

编辑2

好的，我认为我已经非常接近了，问题可能是存储过程。

string search = "julien";
            var list = db.Users.Where(x => x.Name.StartsWith(search));
            string query = list.ToString();

=> 从[dbo].[Users]表中选择[UserId]和[Name]，其中[Name]与@p__linq__0ESCAPE N'~'相似。

var list2 = db.Users.Where(x => x.Name.StartsWith("julien"));
            string query2 = list2.ToString();

=> 选择 [Extent1].[UserId] AS [UserId], [Extent1].[Name] AS [Name] FROM [dbo].[Users] AS [Extent1] WHERE [Extent1].[Name] LIKE N'julien%'

如果我在获取存储过程的查询中使用变量，则使用const会得到查询。

在存储过程中（由实体生成），出现@p__linq__0，因此添加ESCAPE N'~'以避免变量中的通配符。

现在问题更简单了。如何避免使用变量进行查询？这可能吗？ 谢谢

刚看到这篇文章，但不知道是否有解决方案？https://dev59.com/x3rZa4cB1Zd3GeqPyBWa - Julian50
1
你是在使用全文索引，还是普通索引（这种索引无法帮助你进行部分字符串匹配）？ - Mashton
@Mashton，我不理解你的问题，但这是我如何使用代码首次索引的方法，请查看第一个编辑。 - Julian50
全文索引用于加速字符串模式匹配，与列的常规索引不同。对列进行索引意味着它可以更快地找到精确的列匹配，但FTI允许您在这些字符串的位上进行匹配。您将无法使用linq.StartsWith，因为FTI要求您使用sql命令CONTAINS，这意味着您将不得不开始调用存储过程。但是速度的提高是值得的。我们从1m30s到<1s进行了查询。https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/ - Mashton
@Mashon，感谢您的回答。现在关于全文索引我更清楚了。但是我只需要startsWith功能，简单索引就足够了（对于contains功能，您是正确的，需要全文索引）。当我在SQL查询中删除“ESCAPE N''~''”时，我可以检查我所说的内容。查询非常快，我在SQL分析器中看到我使用了索引。我如何在实体中禁用转义功能？看起来与此类似：https://dev59.com/x3rZa4cB1Zd3GeqPyBWa - Julian50
1个回答
8
所需做的是将变量的值作为常量用于生成的Expression中。这是完全可能的。我们需要一个表达式，接受你想要的参数作为真正选择器的参数，第二个参数是占位符，用于常量值，然后是你想要成为常量的值。然后，我们可以用常量的值替换所有参数的实例，只留下将真正参数映射到结果的函数。
public static Expression<Func<TSource, TResult>> EmbedConstant
    <TSource, TResult, TConstant>(
    this Expression<Func<TSource, TConstant, TResult>> expression,
    TConstant constant)
{
    var body = expression.Body.Replace(
        expression.Parameters[1],
        Expression.Constant(constant));
    return Expression.Lambda<Func<TSource, TResult>>(
        body, expression.Parameters[0]);
}

这需要使用以下方法来替换所有实例的一个表达式为另一个表达式：
public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}
internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

这让您可以将这个进行映射：
string search = "julien";
var list = db.Users.Where(x => x.Name.StartsWith(search));
string query = list.ToString();

转换成这样：

string search = "julien";
Expression<Func<User, string, bool>> predicate = 
    (item, searchTerm) => item.Name.StartsWith(searchTerm);
var list = db.Users.Where(predicate.EmbedConstant(search));
string query = list.ToString();
太神奇了，它能工作！！！我花了一整天的时间……非常感谢你。我可以请你喝一杯啤酒吗？（就像我们在法国说谢谢一样） - Julian50
1
什么鬼？那么多代码只是为了在查询中包含Contains或StartWIth吗？？？如果没有这个，执行起来会非常慢。 - Arcadian
我该如何在LINQ to Object中使用它？它给我报错了。 - Arcadian
知道在哪里放置上述代码会很有帮助。 - yondaimehokage
刚刚尝试了EF 6.2.0，仍然像魔法一样正常工作！ - RoLYroLLs
显示剩余2条评论
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接