We are working on SQL Server 2008. Java web application is used as the front end.
Each query that gets fired from application is executed as a stored procedure as shown in query #1.
We observed while executing simple SELECT and UPDATE query from application execution plan is different.
Query #1 takes 3 secs for execution:
declare @p1 int
exec sp_prepexec @p1 output, N'@P4 nvarchar(4000)',
N' SELECT KEY FROM dbo.DETAIL
WHERE KEY = @P4',N'SIND-60068635-R-202'
select @p1
Query #2 takes less than 1 sec for execution:
SELECT KEY
FROM DETAIL
WHERE KEY = 'SIND-60068635-R-202'
We observed execution plan for both queries are different. For 2nd query an index created on KEY is getting applied and hence query response is good, but that same index is not getting used for query #1, and hence query response time is bad.
Any suggestion how to fix this issue are appreciated.
By changing nvarchar to varchar has helped for getting performance benefit (index scan to index seek). Also as JDBC is creating this issue while passing parameters as nvarchar instead of varchar . We referred following blog