linq to sql startwith性能索引列 [英] linq to sql startwith performance indexed columns
问题描述
我首先使用实体代码. 索引列:
I use entity code first. Indexed columns:
- SourceCatalogId
- 已禁用
- CategoryPath
表中的4万行，
我的问题是查询需要40秒！
My problem is the query takes 40s!!
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探查器进行的SQL:
SQL via sql profiler:
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子句中的最后一个中，如果我在以下位置删除"转义N''〜''":
In the last one before where clause, if I remove "escape N''~''" in:
WHERE ([Extent1].[SourceCatalogId] = @p__linq__0) AND (0 = [Extent1].[Disabled]) AND ([Extent1].[CategoryPath] LIKE @p__linq__1 ESCAPE N''~'')
查询需要4秒钟.
正常吗?索引用途?我如何用startWith解决呢?
Is it normal ? Index uses ? How i can solve it with startWith ?
编辑
categoryPath的索引属性:
Index attribut for categoryPath:
[Index("IX_SourceProduct_SourceCatalogId_Disabled_CategoryPath", 3), StringLength(400)]
public string CategoryPath { get; set; }
EDIT2
好的，我非常接近，我认为问题在于存储过程.
OK i thing that I'm pretty close, I think the probleme is stored procedure.
string search = "julien";
var list = db.Users.Where(x => x.Name.StartsWith(search));
string query = list.ToString();
=> 选择 [Extent1].[UserId] AS [UserId]， [范围1].[名称] AS [名称] 从[dbo].[用户] AS [范围1] 在[Extent1].[名称]喜欢@ p__linq__0 ESCAPE N'〜'
=> SELECT [Extent1].[UserId] AS [UserId], [Extent1].[Name] AS [Name] FROM [dbo].[Users] AS [Extent1] WHERE [Extent1].[Name] LIKE @p__linq__0 ESCAPE N'~'
var list2 = db.Users.Where(x => x.Name.StartsWith("julien"));
string query2 = list2.ToString();
=> 选择 [Extent1].[UserId] AS [UserId]， [范围1].[名称] AS [名称] 从[dbo].[用户] AS [范围1] 在[Extent1].[名称]喜欢N'julien％'
=> SELECT [Extent1].[UserId] AS [UserId], [Extent1].[Name] AS [Name] FROM [dbo].[Users] AS [Extent1] WHERE [Extent1].[Name] LIKE N'julien%'
因此，如果我在查询中使用变量来获取存储过程，则如果我使用const则会得到选择.
So if I use variable in the query in get a stored procedure, if I use const I get select.
在(由实体生成的)存储过程中，出现 @ p__linq__0 ，因此添加 ESCAPE N'〜' 避免在变量中使用wildCaractere.
In the stored procedure( generated by entity) makes appear @p__linq__0 so add ESCAPE N'~' to avoid wildCaractere in the variable.
因此，现在的问题更简单了.如何避免使用变量查询?这是可能的 ? 谢谢
So now the question is simplier. How avoid query with variable ? it's possible ? thanks
推荐答案
因此，这里需要做的就是获取变量的值，并将其用作生成的Expression中的常量.实际上这是完全有可能的.我们需要的是一个表达式，该表达式接受您想要的参数作为实选择器的参数，作为第二个参数，该参数是常量值的占位符，然后是您想要成为常量的值.然后，我们可以用常量的值替换参数的所有实例，只剩下一个将真实参数映射到结果的函数:
So what you need to do here is take the value of a variable and use it as a constant in an Expression that you are generating. This is actually quite possible. What we'll need is an expression that accepts the parameter you want as the parameter of your real selector, as second parameter that is a placeholder for the constant value, and then the value that you want to be a constant. We can then replace all instances of the parameter with the value of the constant, leaving just a function that maps the real parameter to the result:
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]);
}
这依靠以下方法将一个表达式的所有实例替换为另一个表达式:
This relies on the following methods for replacing all instances of one expression with another:
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();
这篇关于linq to sql startwith性能索引列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
