0

The following query (without details) ends up with timeout. All columns have nvarchar type. So it is not about this. Query works perfectly without sp_executesql.

exec sp_executesql N'declare @exp nvarchar(max) = ''%'' + @name + ''%''

select u.Id
from [local_db]..Users u
    join [linked_server].[remote_db].dbo.Players p on p.PlayerId = u.PlayerId
    join [linked_server].[remote_db].dbo.PlayerStats ps on ps.PlayerId = u.PlayerId
where @exp is null or u.Nickname like @exp or u.Name like @exp or p.Nickname like @exp
order by ps.Wins desc
offset @skip rows fetch next @take rows only',N'@skip int,@take int,@name nvarchar(8)',@skip=0,@take=24,@name=N'Foo'

I've found some interesting details. If I remove or p.Nickname like @exp, it works. The same happens, when I remove or u.Nickname like @exp or u.Name like @exp. But it's just a thought.

Update

Next query works fine!

exec sp_executesql N'
declare @skip int = 0, @take int = 24, @name nvarchar(8) = N''Foo''
declare @exp nvarchar(max) = ''%'' + @name + ''%''

select u.Id
from [local_db]..Users u
    join [linked_server].[remote_db].dbo.Players p on p.PlayerId = u.PlayerId
    join [linked_server].[remote_db].dbo.PlayerStats ps on ps.PlayerId = u.PlayerId
where @exp is null or u.Nickname like @exp or u.Name like @exp or p.Nickname like @exp
order by ps.Wins desc
offset @skip rows fetch next @take rows only'
Community
  • 1
  • 1
Dmitriy Dokshin
  • 710
  • 5
  • 25
  • The performance issue is because you have rendered your entire query nonSARGable by having the leading wildcard. The reason it "works" when you search fewer columns is because it is able to scan the other columns quick enough to avoid the timeout. – Sean Lange Jul 22 '15 at 14:34
  • But it also works perfectly without sp_executesql. – Dmitriy Dokshin Jul 22 '15 at 14:43
  • Not sure I would say perfectly as it is undoubtedly slow. From what you posted I don't see any reason to use dynamic sql anyway. Is there more to this query that requires you to use dynamic sql? – Sean Lange Jul 22 '15 at 14:48
  • This query is created by Entity Framework, when Database.SqlQuery method is called. – Dmitriy Dokshin Jul 22 '15 at 14:54
  • Ugh I have never used EF but from everything I see it creates some of the most awful sql. Is there a way to make it not use leading wildcards or is that a requirement for the search to work correctly? Sadly there is pretty much nothing you can do to speed that up because any indexes on those columns can't be used. – Sean Lange Jul 22 '15 at 14:57
  • Main query (without 'sp_executesql') is mine, but I call it through Database.SqlQuery. – Dmitriy Dokshin Jul 22 '15 at 15:04

2 Answers2

0

Queries with sp_executesql and without it have different execution plans (why???). In case of sp_executesql profiler suggested me to create index on Users(PlayerId) with included columns (Id, Name, Nickname). And it works like a charm! If you encounter this problem, try to profile your query.

Dmitriy Dokshin
  • 710
  • 5
  • 25
  • Glad that worked for you but you need to take the suggested indexes with a grain of salt. Sometimes you get lucky and they actually help. Many times they make the query slower. I wouldn't totally disregard the suggestions but they need to be analyzed and tested to see if they actually help. – Sean Lange Jul 22 '15 at 20:11
  • Of course. But after whole day of searching solution and understanding the problem it was a simple decision:) – Dmitriy Dokshin Jul 23 '15 at 05:21
0

I had the same problem. A query that worked slowly (about a minute) with sp_executesql and fine without it (instant result). The two execution plans were different. All I did was just recompute the statistics for my main table and it worked.

I don't have any clues as to why it became different. The statistics did not seem to be corrupted or unbalanced before I recomputed them.

Rob
  • 45,296
  • 24
  • 122
  • 150
Warny
  • 27
  • 3
  • @NilsGudat, I'd argue that "All I did was just recompute the statistics of my main table" is an answer. It's one I've used previously to resolve a query with slow performance! The wording was a bit ambiguous but I'm pretty certain it wasn't an attempt to ask another question =) – Rob Jan 04 '19 at 15:48
  • 1
    It is an answer. – Warny Jan 04 '19 at 15:53