Today, I wrote a stored procedure. Because the execution time is relatively long (7 seconds), I plan to optimize it. The result is being optimized for testing. Code The next strange phenomenon is found.
Now there is a table mis_gl_balance, where the field acc_segment varchar (181) is null. Its content format is as follows:
3810.105301.5414070180.000000.00000000.0000.000000
In another table, finance_budget_account records the budget type of budget_type and mis_code MIS system code. The MIS code here is the third section of the acc_segment table.
To query all acc_segment of a certain budget type, write the following code: Select Acc_segment
From DBO. mis_gl_balance
Where Left (Acc_segment, 22 ) In ( Select ' 3810.105301. ' + Mis_code
From Finance_budget_account
Where Budget_type = ' Circuit rental fee ' )
The preceding SQL statement extracts all the acc_segment of a certain circuit rental fee. The execution efficiency can also be received, and the process is completed in one second.
However, if the code is changed to the following: Declare @ Mis_company_code Char ( 4 )
Set @ Mis_company_code = ' 3810 '
Select Acc_segment
From DBO. mis_gl_balance
Where Left (Acc_segment, 22 ) In ( Select @ Mis_company_code + ' . 105301. ' + Mis_code
From Finance_budget_account
Where (Budget_type = ' Circuit rental fee ' ))
To compare the two SQL statements, we only write one of the strings '123' as a parameter, but the execution efficiency is totally different.
The second SQL statement takes 7 seconds.
Why is the execution efficiency significantly different for the same SQL statement?