Slow parameterization query and slow parameterization

Slow parameterization query and slow parameterization

I am so tired that I have worked overtime till now, but you have done all the work you are in charge. But you still need to cooperate with others. You can only wait !!! It's already early in the morning...

Isn't the assignment Good? Individuals are not responsible? Poor time planning?

I don't know !!!

This is the prototype of the problem:

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

In fact, why didn't I find the materials I have searched for a long time? (It cannot be reproduced again. It is a problem that was previously noted down. It should be taken as a warning. Every time you discover a problem, you must write it down... If you are busy, you will not get any results at all)
I just want to say that I will use the second one later... Who can help me?

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.

I still cannot get off work... it's about 2 o'clock in the morning. I think I need to consider changing jobs.

I also found the next, refer to the blog http://www.cnblogs.com/fxwdl/archive/2013/01/09/1257409.html

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 = 'r150200100003580c001'
Select * from BillDetail where OrderDetailID = @ OrderDetailID

There are two solutions:

1. Execution of Stored Procedures

Declare @ OrderDetailID1 nvarchar (20)
Set @ OrderDetailID1 = 'r150200100003580c001'
Exec ('select * from BillDetail where OrderDetailID = ''' + @ OrderDetailID1 + '''')

2. Define the correct parameter type

Declare @ OrderDetailID2varchar (20)
Set @ OrderDetailID1 = 'r150200100003580c001'
Select * from BillDetail where OrderDetailID = @ OrderDetailID2

So, it should be solved !!! I am not busy, but I still haven't gotten off work. IT department in a hotel industry is really funny...