SQL query slow when executed with LINQ (using full text search) -


i have query that's generated linq , generates time out error. when run generated query in sql server management studio executes in less 1 second.

here's query:

exec sp_executesql n'select count(*) [value] [dbo].[document] [t0] inner join [dbo].[document_search_order_nummer](@p0) [t1] on [t0].[documentid] = [t1].[key] left outer join [dbo].[doctype] [t2] on [t2].[id] = [t0].[documenttypeidout] left outer join [dbo].[doctypeformat] [t3] on [t3].[id] = [t2].[formatid] left outer join [dbo].[doctypetype] [t4] on [t4].[id] = [t2].[typeid] inner join [dbo].[originaldocument] [t5] on [t5].[originaldocid] = [t0].[originaldocid] inner join [dbo].[user] [t6] on [t6].[user_id] = [t0].[documentfrom] inner join [dbo].[user] [t7] on [t7].[user_id] = [t0].[documentto] ([t0].[documentid] <> @p1)',n'@p0 nvarchar(4000),@p1 int',@p0=n'"*+11110001+*" ',@p1=270675 

below log sql server profiler:

using linq:

eventclass= rpc:completed
applicationname= .net sqlclient data provider
cpu= 12625
reads= 1137844
writes = 0
duration = 29989

using sql server management studio:

eventclass= sql:batchcompleted
applicationname= microsoft sql server management studio - query
cpu= 78
reads= 31645
writes = 0
duration = 99

what's cause of big performance difference equal query , how can solve issue?

we had same problem once.

one possible cause parameters type mismatching causes this.

but, wasn't issue in our case. turned out in cases query takes longer time execute. isolated query , ran trough ado.net sqlcommand class. believe or not when added white spaces between sql commands caused query execute faster.

so took query , created view it. solved our problem.


Comments