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

1. 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?

2. or issue database? because query performs sometimes?

1. no.
2. 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.