Why is exec sp_executesql much slower than inline sql?

Question

I have test this query in management studio and this execute very fast(less than a second)

declare @p_Message_0 varchar(3) = 'whh'
declare @p_CreatedTime_0 datetime = '2015-06-01'

SELECT count(1) FROM (SELECT * FROM [Logs](nolock) WHERE CONTAINS([Message], @p_Message_0) AND [CreatedTime]<@p_CreatedTime_0) t
SELECT t2.* FROM (SELECT t.*,ROW_NUMBER() OVER (ORDER BY Id DESC) as rownum FROM (SELECT * FROM [Logs](nolock) t WHERE CONTAINS([Message], @p_Message_0) AND [CreatedTime]<@p_CreatedTime_0) t) t2 WHERE rownum>0 AND rownum<=20

execution plan like this:

then I move it into C# ado.net, it run as this

exec sp_executesql N'SELECT count(1) FROM (SELECT * FROM [Logs](nolock) WHERE CONTAINS([Message], @p_Message_0) AND [CreatedTime]<@p_CreatedTime_0) t
SELECT t2.* FROM (SELECT t.*,ROW_NUMBER() OVER (ORDER BY Id desc) as rownum FROM (SELECT * FROM [Logs](nolock) t WHERE CONTAINS([Message], @p_Message_0) AND [CreatedTime]<@p_CreatedTime_0) t) t2 WHERE rownum>0 AND rownum<=20',N'@p_Message_0 varchar(3),@p_CreatedTime_0 datetime',@p_Message_0='whh',@p_CreatedTime_0='2015-06-01'

this one run really slow(about 30s). execution plan like:

I don't know what make these two plan different. Sql server is 2008 R2 with SP2, and I have tried parameter hint and OPTION (RECOMPILE), both not work for me.

Answer 1

Try updating statistics. The first one uses a variable with today's date. Variables aren't sniffed so you will get a guessed distribution. The second one uses a parameter. This can be sniffed.

If the stats haven't been updated today SQL Server will think no rows exist for that date so will give a plan on that basis. Such as a nested loops plan that is estimated to execute the TVF once but actually execute it many times.

AKA the ascending date problem.