I have a query generated from a .net application and parameters are linq parameters. The procedure I followed to tune the query is as below.
- I run the application and capture the query using profiler.
The query is of the form
exec sp_executesql N'SELECT [Extent1].[Id] AS [Id], [Extent1].[GroupId] AS [GroupId], [Extent1].[Agency] AS [Agency], [Extent1].[Group] AS [Group], [Extent1].[Claim] AS [Claim], [Extent1].[StartDate] AS [StartDate], [Extent1].[ExpireDate] AS [ExpireDate], [Extent1].[IsActive] AS [IsActive] FROM [dbo].[Permissions] AS [Extent1] WHERE ([Extent1].[GroupId] = @p__linq__0) AND (([Extent1].[ExpireDate] IS NULL) OR ([Extent1].[ExpireDate] > @p__linq__1)) AND ([Extent1].[IsActive] <> 1)', N'@p__linq__0 int,@p__linq__1 datetime2(7)', @p__linq__0=15,@p__linq__1='2017-05-10 00:00:00'
This is just a sample query. The actual query is much bigger and includes lots of joins involving multiple views and table etc.
My question here is:
- When the run the query as it is, the execution times is around 15 sec
- When I take out the query alone from
sp_executesqland apply the filter values to the query, it runs in less than 1 sec.
I understand that since the values are hard-coded into the query it will be faster, but why is that much difference in execution time? Also the execution plans are different for both:
SQL Execution plan with sp_executesql
Execution Plan with hard-coded SQL
I am the only user working on this server and I execute same parameters in both scenarios. I have applied
OPTION(RECOMPILE) to the end of the
sp_executesql and it executes in 1 sec compared to 15 sec. But the execution plan is still not similar to the hard-coded plan.
OPTION(RECOMPILE) makes the query run fast in one second as it forces the query to compile and create new plan each time. But I am not supposed to hack the code and change the query. I will suggest
OPTION(RECOMPILE) as a last resort to my application team.