c# - EF generated query performance tuning required. Or a database issue? -
i have search query generated ef 6 sometimes causing performance issue search criteria yields large results. query performance unpredictable, performs , not.the following query caught in sql profiler, queries view
exec sp_executesql n'select [extent1].[lastname] [lastname], [extent1].[firstname] [firstname], . . (select [personview].[lastname] [lastname], [personview].[firstname] [firstname], . . [staff].[personview] [personview]) [extent1] [extent1].[firstname] = @p__linq__0',n'@p__linq__0 varchar(8000)',@p__linq__0='smith'
following repository code
public ienumerable<personsearchresult> searchpersons(expression<func<personview, bool>> searchcriteria) { var query = _entities.personviews.asexpandable().where(searchcriteria); return query; }
i'm using predicate builder create dynamic search criteria.
my concern unpredictable nature of performance same search criteria.
following questions
i believe select x select x table query format causing issue. when executed inner part of select performing better when whole query struggling. need tuning? if yes, start with?
or issue database? because query performs sometimes?
- no.
- depending on actual set of conditions in
where
part, sql server might utilise indices, result in better performance (assuming there any). filtering non-indexed columns slow , resource consuming, worst of - unpredictably slow, i.e. tolerable today , stalling tomorrow.
usually, columns lengthy text such persons' names indexed, particular example destined perform poorly. unless, of course, search first / last names crucial part of system functionality , create indices columns might mentioned in search criteria, including large these ones.
i recommend searching sql scripts show "suggested indexes", should handy.
Comments
Post a Comment