问题
第一条 1.去除 Charsplitforin ，采用表传值的方式 2.针对价格计算器的调用是否能采用缓存
(@CountryID int,@CustomerID int,@TransportIDs nvarchar(44)) SELECT ac.AreaID, ac.ProvincesCN, Country_cn, Shorthand, ac.CustomerID, CountryID, tf.TransportID, ra.AreaName, isnull(ra.AreaType,1) as AreaType, ac.PostCodeAccurat, ac.PostCodeSection, tf.Factor FROM RB_Transport_Fee tf WITH(nolock) INNER JOIN RB_Area ra (nolock) ON tf.AreaID = ra.ID AND tf.CustomerID = ra.CustomerID INNER JOIN RB_Area_Country ac (nolock) ON tf.AreaID = ac.AreaID AND ac.CustomerID = tf.CustomerID LEFT JOIN dbo.E_Country e WITH(nolock) ON e.ID = ac.CountryID LEFT JOIN RB_Transport t(NOLOCK) ON tf.TransportID=t.ID AND tf.CustomerID=t.CustomerID AND tf.SupplierID=t.DefaultSupplierID WHERE 1=1 AND t.DefaultSupplierID>0 AND CountryID=@CountryID AND tf.CustomerID=@CustomerID AND tf.TransportID IN (SELECT CAST(item AS INT) FROM dbo.CharSplitForIn(@TransportIDs,',')) ORDER BY tf.ID ASC
第二条 1.去除 Charsplitforin ，采用表传值的方式 2.针对价格计算器的调用是否能采用缓存
(@CustomerID int,@IsActive int,@IDs nvarchar(4))SELECT [ID], [CustomerID], [WareHouseName], [WareHouseType] FROM RB_WareHouse (NOLOCK) WHERE CustomerID = @CustomerID AND IsActive= @IsActive AND ID IN (SELECT CAST(ITEM AS INT) FROM Charsplitforin(@IDs, ','))
第三条 1.去除Charsplitforin 函数 采用表传值的方式。2.校验是否能合理的采用缓存
(@CustomerID int,@RuleIDs varchar(max) )SELECT [ID],[CustomerID],[RuleTitle],[WareHouseRuleType],[WareHouseType],[TransportRuleType],[TransportIDs],[IsRegistered],[TransferType],[RequiredTimeLimit],[IsCheckStock],[PriceMatchType],[IsActive],[AddAdminID],[AddUserName],[AddTime],[UpdateAdminID],[UpdateUserName],[UpdateTime],[OrderSourceType],[MatchOrderStatus] FROM RB_AutoMatchWarehouseAndTransportRule wat(nolock) WHERE CustomerID = @CustomerID AND ID in (SELECT CAST(ITEM AS INT) FROM Charsplitforin(@RuleIDs, ','))
第四条 针对CustomerID 的值判断，如果只有一个，请用等于。2 去除Charsplitforin 函数 采用表传值的方式。3.校验是否能合理的采用缓存
(@TransportIDs nvarchar(24))SELECT t.ID AS TransportID, t.CustomerID, t.TransferAttribute, t.DefaultSupplierID, t.EnableWeightSize, t.LengthSize, t.WidthSize, t.HightSize, t.PerimeterSize, t.CustomLength, t.CustomWidth, t.CustomHeight FROM RB_Transport t WITH(NOLOCK) WHERE t.CustomerID IN (1) AND t.ID IN ( SELECT CAST(ITEM AS INT) FROM Charsplitforin(@TransportIDs, ',') ) AND t.IsActive = 1
目标
1针对所有IN 的内容判断数量，如果只有一个 采用等于。如果是多个用表传值的方式。2.重点查看是否可以采用等于，以减少对数据库的依赖。
优化方案
4个查询sql结果放到服务端缓存，缓存时效60分钟。每次有从缓存读取数据，如果缓存没有数据，则先读取数据库的数据放到缓存