1.原sql
| SQL | 执行次数 | 平均I_O | 总I_O | 逻辑写 | 逻辑读 | CPU平均耗时 | 总CPU耗时_秒 | 优化建议 |
| (@CustomerID int)SELECT e1.Currency, ( CASE WHEN setting.IsUseCustomerExchangeRMB = 1 AND customCurrency.ExchangeRMB IS NOT NULL THEN customCurrency.ExchangeRMB ELSE e1.ExchangeRMB END ) AS ExchangeRMB, ( CASE WHEN setting.IsUseCustomerExchangeRMB = 1 AND customCurrency.ExchangeUSD IS NOT NULL THEN customCurrency.ExchangeUSD ELSE ( e2.ExchangeRMB / e1.ExchangeRMB ) END ) AS ExchangeUSD FROM E_Currency e1 WITH(nolock) JOIN E_Currency e2 ON e2.Currency = 'USD' JOIN (SELECT Count(1) AS IsUseCustomerExchangeRMB FROM RB_Customer_Setting WITH(nolock) WHERE CustomerID = 1 AND IsUseCustomerExchangeRMB = 1) setting ON 1 = 1 LEFT JOIN (SELECT e1.Currency, e1.ExchangeRMB AS ExchangeRMB, ( e2.ExchangeRMB / e1.ExchangeRMB ) AS ExchangeUSD FROM RB_Customer_Currency e1 WITH(nolock) JOIN RB_Customer_Currency e2 ON e2.Currency = 'USD' AND e2.CustomerID = e1.CustomerID WHERE e1.ExchangeRMB > 0 AND e2.ExchangeRMB > 0 AND e1.CustomerID = 1) customCurrency ON customCurrency.Currency = e1.Currency WHERE e1.ExchangeRMB > 0 AND e2.ExchangeRMB > 0 AND e1.IsActive = 1 | 886 | 99 | 88505 | 0 | 88505 | 0 | 284 | 全表查询做缓存，其他数据从缓存中读取，找不到查一遍数据库 |
2.Erp系统对应位置
3.代码最后提交记录
