HoneyHoney

ALTER PROCEDURE Get_OrderID_OrderQty
 @ProductID INT,
 @OrderId INT = NULL
AS 

SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID 
AND (SalesOrderID = @OrderId OR @OrderId IS NULL)

GO

SET STATISTICS IO  ON

EXEC Get_OrderID_OrderQty @ProductID=870

EXEC Get_OrderID_OrderQty @ProductID=870, @OrderId= 51108

SET STATISTICS IO OFF

改成Dynamic query的方式，就好了。

ALTER PROCEDURE Get_OrderID_OrderQty
 @ProductID INT,
 @OrderId INT = NULL
AS 

DECLARE @sql NVARCHAR(MAX) = N'';

SET @sql = 'SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID '

IF @OrderId IS NOT NULL
    SET @sql = @sql + 'AND SalesOrderID = @OrderId'

EXEC sys.sp_executesql @sql
    , N'@ProductID INT, @OrderId INT'
    , @ProductID, @OrderId

GO

SET STATISTICS IO  ON

EXEC Get_OrderID_OrderQty @ProductID=897

EXEC Get_OrderID_OrderQty @ProductID=897, @OrderId= 51108

SET STATISTICS IO OFF

这样就好了？那我们就too young too simple了。有一个明显的问题，就是tipping point。如果这个时候执行下面的query，你就会得到的一个糟糕的结果。实际上，IO由1246变成了14379，这是非常非常的糟糕的。最好参数里面能够有high selective的，否则还是得老老实实加上OPTION(RECOMPILE)。

SET STATISTICS IO  ON

EXEC Get_OrderID_OrderQty @ProductID=870

SELECT SalesOrderDetailID, OrderQty, SalesOrderID 
FROM Sales.SalesOrderDetail
WHERE ProductID = 870
SET STATISTICS IO OFF

1. SP_RECOMPILE 在不知道怎么改的时候，一般的临时方案就是sp_recompile. 

   EXEC sys.sp_recompile 'Get_OrderID_OrderQty' 
   GO

2. WITH RECOMPILE 能够工作，但是这样做会没有cached query plan，也没有DMV data。

   ALTER PROCEDURE Get_OrderID_OrderQty
    @ProductID INT
    WITH RECOMPILE
   AS 
   
   SELECT SalesOrderDetailID, OrderQty
   FROM Sales.SalesOrderDetail
   WHERE ProductID = @ProductID;

3. OPTION RECOMPILE 比上面稍微好点，能够cached 最后一次执行的query plan，DMV里面也能够看到数据了。

   ALTER PROCEDURE Get_OrderID_OrderQty
    @ProductID INT
   AS 
   
   SELECT SalesOrderDetailID, OrderQty
   FROM Sales.SalesOrderDetail
   WHERE ProductID = @ProductID OPTION(RECOMPILE);

4. 核武器 - DBCC FREEPROCCACHE 世界一下清净了，所有的都需要重新compile和cache。但这只是短暂的，等下一次糟糕的query plan被cache起来以后，问题还是会出现。
5. Dynamic query？这个看上去是个不错的选择。但实际上我还是发现，我需要加上OPTION(RECOMPILE)，否则，第一次执行的query plan会被cache起来被第二次执行用掉，问题依旧。而加上以后，其实就是第三个方案一样了。

   ALTER PROCEDURE Get_OrderID_OrderQty
    @ProductID INT
   AS 
   
   DECLARE @sql NVARCHAR(MAX) = N''
   
   SET @sql = 'SELECT SalesOrderDetailID, OrderQty
   FROM Sales.SalesOrderDetail
   WHERE ProductID = @ProductID OPTION(RECOMPILE)'
   
   EXEC sys.sp_executesql @sql, N'@ProductId INT', @ProductID
   
   GO

SET STATISTICS IO  ON

EXEC Get_OrderID_OrderQty @ProductID=870

EXEC Get_OrderID_OrderQty @ProductID=897

SET STATISTICS IO OFF

什么是parameter sniffing

拿AdventureWorks2012为例，执行下面的query

可以看到每个query的IO和执行计划。



创建一个SP。

CREATE PROCEDURE Get_OrderID_OrderQty
 @ProductID INT
AS 

SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID;

实际上，Sales.SalesOrderDetail也只有1237个page。

DBCC IND ('AdventureWorks2012', 'Sales.SalesOrderDetail', 0);

这就是parameter sniffing，由于传入的参数的不同，会倒是返回的结果集差异太大，而SQL SERVER却不能够正确的选择最优的执行计划。

1. 当没有cached query plan 的时候，执行query就会创建一个query plan并被cache起来。
2. 下面这些操作可以导致cached plan被清掉
• SERVER RESTART/DATABASE LEVEL CHANGES/CONFIGURATION CHANGES
• DBCC FREEPROCCACHE/DBCC FREESYSTEMCACHE/SP_RECOMPILE
• DROP & RECREATE SP
• Object changes，包括view，table，statistics
• 长时间没用

64.233.161.108 smtp.gmail.com

1. 计算不够精确。其实这个不能够怪新浪的计算器。实际上每个银行在计算每年1月份利率调整的方法各不一样。比如这篇博客里面解释了工商银行的计算方式，而我按照他的计算方式去算结果总是会有一些偏差。
2. 局限性。他只能够计算到3个利率变化的情况，一般5年以上的贷款利率变化很有可能超过3个。

• 关于等额本息以及相关计算，请转百科和这篇博客

require 'date'

# 贷款总额
total_loan_money = 50 * 10000
# 贷款年限
total_loan_month = 20 * 12
# 初始利率
initial_interest_rate = 4.7
# 第一次还贷时间
initial_pay_dt = '2011-05-08'
# 利率调整时间表
interest_rate_changes = [] 
interest_rate_changes << [initial_pay_dt, initial_interest_rate]
interest_rate_changes << ['2012-01-01', 4.9]
interest_rate_changes << ['2013-01-01', 4.5]
interest_rate_changes << ['2015-01-01', 4.25]
interest_rate_changes << ['2016-01-01', 3.5]

p interest_rate_changes

def averageInterest(n, money, rate, ini_pay_dt)
 list = []
 money_to_pay_per_month = (money * rate * (1 + rate) ** n /((1 + rate) ** n - 1)).round(2)

 n.times do |i|
  remaining_load_money = (i==0) ? money : list[i-1][4]
  interest_to_pay = (remaining_load_money * rate).round(2)
  load_money_to_pay = (money_to_pay_per_month - interest_to_pay).round(2)
  # [date, 本期利息，本期本金，本期本息，剩余本金]
  list << [ini_pay_dt.strftime('%Y-%m-%d'), interest_to_pay, load_money_to_pay, 

money_to_pay_per_month, (remaining_load_money - load_money_to_pay).round(2)]

  ini_pay_dt = ini_pay_dt.next_month
 end
 list
end

def averageInterestAdjust(list, date, total_loan_month, rate1, rate2)
 tmp_list = []
 last_list = list[-2]
 money = last_list[4]
 rate1_per_day = rate1 / 30
 rate2_per_day = rate2 / 30
 ini_pay_dt = Date.parse(last_list[0])

 rate1_days = date - ini_pay_dt
 rate2_days = ini_pay_dt.next_month - date

 # 本月利息
 interest_to_pay = (money * (rate1_per_day * rate1_days + 

rate2_per_day * rate2_days)).round(2)

 # 本月应还金额
 # 农业银行特色 应还本金还是按照上一年的利率计算的，但是利息是分段计算的
 money_to_pay = (interest_to_pay + list[-1][2]).round(2)

 tmp_list = [ini_pay_dt.strftime('%Y-%m-%d'), interest_to_pay, 

(money_to_pay - interest_to_pay), money_to_pay, 

money-(money_to_pay - interest_to_pay)]

 list[-1][1] = tmp_list[1].round(2)
 list[-1][2] = tmp_list[2].round(2)
 list[-1][3] = tmp_list[3].round(2)
 list[-1][4] = tmp_list[4].round(2)

 list
end

list = []
i = 0
ini_pay_dt = Date.parse(initial_pay_dt)

interest_rate_changes.each do |t|

 date = Date.parse(t[0])

 if ini_pay_dt == date
  i += 1
  next
 else
  diff = (date.year - ini_pay_dt.year) * 12 + (date.month - ini_pay_dt.month)
 end

 list += averageInterest(total_loan_month, total_loan_money, 

    interest_rate_changes[i-1][1]/100.0/12.0, ini_pay_dt).slice(0, diff+1)

 total_loan_month -= (diff) 

 # 每年1月1日利率调整，按日计息
 list = averageInterestAdjust(list, date, total_loan_month, 

    interest_rate_changes[i-1][1]/100.0/12.0, interest_rate_changes[i][1]/100.0/12.0)

 total_loan_month -= 1
 ini_pay_dt = Date.parse(list[-1][0]).next_month
 total_loan_money = list[-1][4]
 i += 1
end

list += averageInterest(total_loan_month, total_loan_money, 

    interest_rate_changes[-1][1]/100.0/12.0, ini_pay_dt)

total_interest = 0

list.each do |t|
 p t
 total_interest += t[1]
end

puts total_interest