Slow parameterized query speed

Slow parameterized query speed

This is the problem prototype:

 --SQL1 declare @OrderDetailID nvarchar(50) set @OrderDetailID='R1502003963580C001' select * from BillDetail where OrderDetailID=@OrderDetailID  --SQL2 select * from BillDetail where OrderDetailID='R1502003963580C001'  

PS: In addition, when SqlParameter is used each time, if it is a character type, add size as much as possible, involving the execution plan cache problem.

For example:new SqlParameter("@name", SqlDbType.Varchar, 40) { Value = name };

You still cannot get off work...

I checked again: it seems that Microsoft has encountered such a problem.

The solution is as follows: The only resolution to this problem is to use a stored querydef query instead of prepared queries.

It may be understandable (in the form of a stored procedure ):

Change the preceding parameterization to exec ('select * from BillDetail where OrderDetailID = ''' + @ OrderDetailID + '''')

This is the case for the moment! The main reason is that it cannot be reproduced and cannot be verified. If so, try it first.

The reason is that the defined string type does not match.

Declare @ OrderDetailID varchar (50) -- change from nvarchar to varchar

If you are anxious to find out the problem, just look at the following...

Summary:

The execution plan is as follows:

declare @OrderDetailID nvarchar(50)
set @OrderDetailID='R1502003963580C001'
select * from BillDetail where OrderDetailID=@OrderDetailID

There are two solutions:

1. Execution of Stored Procedures

declare @OrderDetailID1 nvarchar(20)
set @OrderDetailID1='R1502003963580C001'
exec('select * from BillDetail where OrderDetailID='''+@OrderDetailID1+'''')

2. Define the correct parameter type

declare @OrderDetailID2varchar(20)
set @OrderDetailID1='R1502003963580C001'
select * from BillDetail where OrderDetailID=@OrderDetailID2