数据筛选和API优化

来源:http://www.98ju.net 作者:银河999游戏下载 人气:62 发布时间:2019-11-02
摘要:数据筛选和API优化。1、使用缓存 同三个社区的人在同一时间所看见的巨惠券记录应当是相通的,并且大家应当允许脏读,大家在12306上边买轻轨票的时候,平日也会见到展现有票,不过

数据筛选和API优化。1、使用缓存

同三个社区的人在同一时间所看见的巨惠券记录应当是相通的,并且大家应当允许脏读,大家在12306上边买轻轨票的时候,平日也会见到展现有票,不过下单又尚未了,或者是选用了缓存,那么大家这里其实相符的可以使用缓存来缓和并发难点。

在WebAPI上边加缓存,那么又分为顾客端缓存和服务器缓存。而大家了然,在ASP.NET WebForm和ASP.NET MVC中都以有页面输出缓存的,而在WebAPI中私下认可未有,从NuGet下边下载WebApi.OutputCache.V2,然后再API接口上增加 

[CacheOutput(ClientTimeSpan = 5)]//, ServerTimeSpan = 5

数据筛选和API优化。小编这里没有办法直接运用服务器输出缓存,那是因为不能够捕获缓存变量参数。因为大家API接口的伸手参数是string appParam,字符串类型的,它是几个json对象进过base陆10位编码,然后再进过url编码生成的字符串。

我们只能深入分析后,获取社区ID,然后依据社区ID来设置缓存,把社区ID+ pageIndex就视作缓存的Key,思量到要求缓存的数据量极小,这里笔者一贯使用.NET自带的缓存,引进命名空间:System.Web.Caching;

      private static System.Web.Caching.Cache ObjCache = HttpRuntime.Cache;
        /// <summary>
        /// 设置当前指定Key的Cache值,并限定过期时间 
        /// </summary>
        /// <param name="Key">缓存Key</param>
        /// <param name="Obj">缓存的值</param>
        /// <param name="TimeOuts">超时时间(秒)</param>
        public static void SetCacheSeconds(string Key, object Obj, double TimeOuts)
        {
            ObjCache.Insert(Key, Obj, null, System.DateTime.Now.AddSeconds(TimeOuts), TimeSpan.Zero);
        }
        /// <summary>
        /// 获取当前指定Key的Cache值
        /// </summary>
        /// <param name="Key">缓存Key</param>
        /// <returns>缓存的值</returns>
        public static object GetCache(string Key)
        {
            return ObjCache[Key];
        }

缓存操作类Cache完整代码如下:

图片 1图片 2

/*==================================
 * Author:
 * CreateTime:2014-7-15 17:26:29
 * Description:Cache操作类
 ===================================*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Caching;
using System.Runtime.CompilerServices;
using System.Web;
using System.Security.Policy;

namespace SSY.Util
{
    /// <summary>
    /// 缓存处理相关类
    /// </summary>
    public class Cache
    {
        private static System.Web.Caching.Cache ObjCache = HttpRuntime.Cache;
        private static short TimeOut = 720;

        #region 清除指定键值的缓存
        /// <summary>
        /// 清除指定键值的缓存
        /// </summary>
        /// <param name="Key">要清除的缓存的key值</param>
        public static void Clear(string Key)
        {
            if (ObjCache[Key] != null)
            {
                ObjCache.Remove(Key);
            }
        }
        #endregion

        #region 返回系统中缓存的个数
        /// <summary>
        /// 返回系统中缓存的个数
        /// </summary>
        /// <returns>缓存个数</returns>
        public static int Count()
        {
            return ObjCache.Count;
        }
        #endregion

        #region 获取当前指定Key的Cache值
        /// <summary>
        /// 获取当前指定Key的Cache值
        /// </summary>
        /// <param name="Key">缓存Key</param>
        /// <returns>缓存的值</returns>
        public static object GetCache(string Key)
        {
            return ObjCache[Key];
        }
        #endregion

        #region 设置当前指定Key的Cache值
        /// <summary>
        /// 设置当前指定Key的Cache值 
        /// </summary>
        /// <param name="Key">缓存Key</param>
        /// <param name="Obj">缓存的值</param>
        public static void SetCache(string Key, object Obj)
        {
            ObjCache.Insert(Key, Obj);
        }
        #endregion

        #region 设置当前指定Key的Cache值,并限定过期时间
        /// <summary>
        /// 设置当前指定Key的Cache值,并限定过期时间 
        /// </summary>
        /// <param name="Key">缓存Key</param>
        /// <param name="Obj">缓存的值</param>
        /// <param name="TimeOuts">超时时间(分钟)</param>
        public static void SetCache(string Key, object Obj, int TimeOuts)
        {
            ObjCache.Insert(Key, Obj, null, System.DateTime.Now.AddMinutes((double)TimeOuts), TimeSpan.Zero);
        }
        /// <summary>
        /// 设置当前指定Key的Cache值,并限定过期时间 
        /// </summary>
        /// <param name="Key">缓存Key</param>
        /// <param name="Obj">缓存的值</param>
        /// <param name="TimeOuts">超时时间(秒)</param>
        public static void SetCacheSeconds(string Key, object Obj, double TimeOuts)
        {
            ObjCache.Insert(Key, Obj, null, System.DateTime.Now.AddSeconds(TimeOuts), TimeSpan.Zero);
        }
        #endregion

        #region 设置当前指定Key的Cache值,依赖文件过期
        /// <summary>
        /// 设置当前指定Key的Cache值,依赖文件过期 
        /// </summary>
        /// <param name="Key">缓存Key</param>
        /// <param name="Obj">缓存的值</param>
        /// <param name="Files">相对地址,例如"~/files.xml"</param>
        public static void SetCache(string Key, object Obj, string Files)
        {
            CacheDependency cacheDep = new CacheDependency(System.Web.HttpContext.Current.Server.MapPath(Files),System.DateTime.Now);
            SetCache(Key, Obj, TimeOut, cacheDep, CacheItemPriority.High);
        }
        #endregion

        #region 设置当前指定Key的Cache值
        /// <summary>
        /// 设置当前指定Key的Cache值
        /// </summary>
        /// <param name="Key">缓存Key</param>
        /// <param name="Obj">缓存的值</param>
        /// <param name="Priority">撤销缓存的优先值,此参数的值取自枚举变量“CacheItemPriority”,优先级低的数据项将先被删除。此参数主要用在缓存退出对象时.</param>
        public static void SetCache(string Key, object Obj, CacheItemPriority Priority)
        {
            SetCache(Key, Obj, TimeOut, null, Priority);
        }
        #endregion

        #region 设置当前指定Key的Cache值
        /// <summary>
        /// 设置当前指定Key的Cache值
        /// </summary>
        /// <param name="Key">缓存Key</param>
        /// <param name="Obj">缓存的值</param>
        /// <param name="TimeOuts">一个TimeSpan,表示缓存参数将在多长时间以后被删除</param>
        /// <param name="CacheDep">缓存的依赖项,需要一个CacheDependency,可初始化一个</param>
        /// <param name="Priority">撤销缓存的优先值,此参数的值取自枚举变量“CacheItemPriority”,优先级低的数据项将先被删除。此参数主要用在缓存退出对象时</param>
        public static void SetCache(string Key, object Obj, int TimeOuts, CacheDependency CacheDep, CacheItemPriority Priority)
        {
            ObjCache.Insert(Key, Obj, CacheDep, System.DateTime.MaxValue, TimeSpan.FromHours((double)TimeOuts), Priority, null);
        }
        #endregion
    }
}

View Code

改过API接口代码:

           #region added by zouqj 2017-3-7
           var result = Util.Cache.GetCache(CommunityID+ pageIndex) as Result<List<GetAvailableCouponsModel>>;
           if (result==null) //不存在则写入缓存
           {
               //组装参数
               Dictionary<string, string> inParams = new Dictionary<string, string>();
               inParams.Add("UserId", userId);
               inParams.Add("PageIndex", pageIndex);
               inParams.Add("PageSize", pageSize);
               inParams.Add("CommunityID", CommunityID+ pageIndex);
               ...

               RequestParam RequestParam = GetRequestParam(methodName, inParams, AuthenticationId);
               var jsonContent = JsonConvert.SerializeObject(RequestParam);
               result = DoPost<List<GetAvailableCouponsModel>>(jsonContent, PostUrl);

               Util.Cache.SetCacheSeconds(CommunityID, result, 10); //写入缓存
           }
           return result;

数据筛选和API优化。2、改为存储进度完结

因为那么些接口的事情逻辑比较复杂,之前的Linq代码写了好长一大串,获取的记录数相当多,何况还是在内部存款和储蓄器中进行分页完成,所以自身将本来的LINQ实今世码改革为分页存款和储蓄进度实现。

积累进度代码如下:

图片 3图片 4

------------------------------------------创建领券中心存储过程 created by zouqj-2017-3-1-----------------------------------
IF EXISTS(Select Name From Sysobjects Where Name='usp_GetAvailableCoupons' And Xtype='P')
DROP PROCEDURE usp_GetAvailableCoupons
GO
CREATE PROC usp_GetAvailableCouponsl
(
@PageIndex int, --页码
@PageSize int, --每页容纳的记录数
@Sort NVARCHAR(50), --排序字段及规则,不用加order by
@hostName nvarchar(100),--服务器地址
@CommunityID UNIQUEIDENTIFIER, --社区
@IsGetCount BIT --是否得到记录总数,1为得到记录总数,0为不得到记录总数,返回记录集
)
AS
-------------------------------定义变量-----------------------
declare @strSql NVARCHAR(max);
DECLARE  @dt datetime2(7) --查询时间
SET @dt=GETDATE();
set nocount on;
----------------------------------------SQL开始--------------------------------------------无分类
IF @IsGetCount=1
BEGIN

SET @strSql=N'SELECT COUNT(*) FROM (SELECT DISTINCT t.CampaignID from
(SELECT 
c.ID AS CampaignID
 FROM MK_Campaign c WITH ( NOLOCK ) INNER JOIN MK_CouponConfig f WITH ( NOLOCK ) ON c.ID=f.CampaignID
           INNER JOIN MK_Coupon p WITH ( NOLOCK ) ON f.CouponID=p.ID
           WHERE f.CouponGetType=2 AND f.ReceiveStartTime <=@dt AND f.ReceiveEndTime>= @dt
           AND c.State=4 --执行中
           AND p.WholeNetwork=1 --全网优惠
UNION ALL
SELECT
    a.[ID] AS CampaignID
    FROM  [dbo].[MK_Campaign] AS a WITH ( NOLOCK )
        INNER JOIN [dbo].[MK_CouponConfig] AS b  WITH ( NOLOCK ) ON a.[ID] = b.[CampaignID]
        AND (b.[ReceiveStartTime] <= @dt) AND (b.[ReceiveEndTime] >= @dt) AND (2 = b.[CouponGetType]) AND (4 = a.[State]) 
        INNER JOIN [dbo].[MK_Coupon] AS c WITH ( NOLOCK ) ON b.[CouponID] = c.[ID]
        INNER JOIN [dbo].[MK_CouponRestriction] AS d  WITH ( NOLOCK ) ON c.[ID] = d.[CompainID]
        LEFT OUTER JOIN [dbo].[MK_CouponRestrictCategory] AS e WITH ( NOLOCK ) ON d.[ID] = e.[CouponRestrictionID]
        LEFT OUTER JOIN [dbo].[MK_CouponRestrictionOrg] AS f WITH ( NOLOCK ) ON d.[ID] = f.[CouponRestrictionID]
        INNER JOIN [dbo].[ViewOrganizationCommunityForInterface] AS v ON f.[OrgID] = v.[ID]    
    where v.CommunityID=@CommunityID  and 1 = d.[Type]
UNION ALL
SELECT 
   a.ID AS CampaignID
   FROM MK_Campaign a  WITH ( NOLOCK ) 
             INNER JOIN MK_CouponConfig b WITH ( NOLOCK ) ON a.ID=b.CampaignID AND (b.[ReceiveStartTime] <= @dt) AND (b.[ReceiveEndTime] >=@dt) 
             INNER JOIN [dbo].[MK_Coupon] AS c WITH ( NOLOCK ) ON b.[CouponID] = c.[ID] and (2 = b.[CouponGetType]) AND (4 = a.[State]) 
             INNER JOIN [dbo].[MK_CouponRestriction] AS d WITH ( NOLOCK ) ON c.[ID] = d.[CompainID]  AND (2 = d.[Type]) 
             LEFT OUTER JOIN MK_CouponRestrictionProduct AS e WITH ( NOLOCK ) ON d.[ID] = e.[CouponRestrictionID] 
             LEFT OUTER JOIN SL_Product AS f WITH ( NOLOCK ) ON (e.ProductID = f.ID)
             INNER JOIN ViewOrganizationCommunityForInterface v ON v.ID=f.PublisherID
   where v.CommunityID=@CommunityID
   ) t
 ) AS tt'
----------------------------------------------------------------------------------
END
ELSE
BEGIN
SET @strSql=N'SELECT DISTINCT t.* from(
SELECT 
          c.ID AS CampaignID,
          c.Name AS CampaignName,
          f.ValidityStartTime AS CampaignStartTime,
          f.ValidityEndTime AS CampaignEndTime,
          p.Name AS CouponsName,
          p.Price AS CouponsAmount,
          (CASE WHEN p.IsLimited =1 THEN 1 ELSE 2 END) AS IsLimited,
          p.FullAmount AS MinAmount,
          f.ValidityEndTime AS CouponsEndTime,
          f.ValidityStartTime AS CouponsStartTime,
          (CASE WHEN f.IsRepeateGet =1 THEN 1 ELSE 2 END) AS IsCanRepeatedReceive,
          f.ReceiveAddress AS ReceiveAddress,
          f.ReceiveEndTime AS ReceiveEndTime,
          f.ReceiveStartTime AS ReceiveStartTime,
          f.ReceiveMode AS ReceiveMethod,
          f.ProvideNum AS ReceiveNum,
          p.CreateTime AS CreateTime,
          p.Price AS Price,
          f.RemainCouponNum AS RemainCouponNum,
          f.ID AS CouponConfigId,
          p.WholeNetwork AS CouponsType,
          (CASE WHEN f.IconUrl IS NULL THEN N'''' WHEN f.IconUrl=N'''' THEN N'''' ELSE @hostName+f.IconUrl END) AS IconUrl 
FROM MK_Campaign c WITH ( NOLOCK ) 
                 INNER JOIN MK_CouponConfig f WITH ( NOLOCK ) ON c.ID=f.CampaignID
                 INNER JOIN MK_Coupon p WITH ( NOLOCK ) ON f.CouponID=p.ID
          WHERE f.CouponGetType=2 AND f.ReceiveStartTime <= @dt AND f.ReceiveEndTime>= @dt AND c.State=4 AND p.WholeNetwork=1 --全网优惠
--
UNION ALL
          SELECT a.[ID] AS CampaignID,
          a.[Name] AS CampaignName, 
          b.[ValidityStartTime] AS CampaignStartTime,
          b.[ValidityEndTime] AS CampaignEndTime, 
          c.[Name] AS CouponsName, 
          c.[Price] AS CouponsAmount, 
          CASE WHEN (c.[IsLimited] = 1) THEN 1 ELSE 2 END AS IsLimited, 
          c.[FullAmount] AS MinAmount, 
          b.[ValidityEndTime] AS CouponsEndTime, 
          b.[ValidityStartTime] AS CouponsStartTime, 
          CASE WHEN (b.[IsRepeateGet] = 1) THEN 1 ELSE 2 END AS IsCanRepeatedReceive, 
          b.[ReceiveAddress] AS [ReceiveAddress], 
          b.[ReceiveEndTime] AS [ReceiveEndTime], 
          b.[ReceiveStartTime] AS [ReceiveStartTime], 
          b.[ReceiveMode] AS ReceiveMethod, 
          b.[ProvideNum] AS ReceiveNum, 
          c.[CreateTime] AS CreateTime, 
          c.[Price] AS Price, 
          b.[RemainCouponNum] AS RemainCouponNum, 
          b.[ID] AS CouponConfigId, 
          e.[Type] AS CouponsType, 
          CASE WHEN (b.[IconUrl] = N'''' OR b.[IconUrl] IS NULL) THEN N'''' ELSE @hostName+b.[IconUrl] END AS IconUrl
                    FROM  [dbo].[MK_Campaign] AS a WITH ( NOLOCK )
                        INNER JOIN [dbo].[MK_CouponConfig] AS b  WITH ( NOLOCK ) ON a.[ID] = b.[CampaignID] AND (b.[ReceiveStartTime] <= @dt) AND (b.[ReceiveEndTime] >= @dt) AND (2 = b.[CouponGetType]) AND (4 = a.[State]) 
                        INNER JOIN [dbo].[MK_Coupon] AS c WITH ( NOLOCK ) ON b.[CouponID] = c.[ID]
                        INNER JOIN [dbo].[MK_CouponRestriction] AS d  WITH ( NOLOCK ) ON c.[ID] = d.[CompainID]
                        LEFT OUTER JOIN [dbo].[MK_CouponRestrictCategory] AS e WITH ( NOLOCK ) ON d.[ID] = e.[CouponRestrictionID]
                        LEFT OUTER JOIN [dbo].[MK_CouponRestrictionOrg] AS f WITH ( NOLOCK ) ON d.[ID] = f.[CouponRestrictionID]
                        INNER JOIN [dbo].[ViewOrganizationCommunityForInterface] AS v ON f.[OrgID] = v.[ID]    
                    where v.CommunityID=@CommunityID  and 1 = d.[Type]
UNION ALL
SELECT 
       a.ID AS CampaignID,
       a.Name AS CampaignName,
       b.ValidityStartTime AS CampaignStartTime,
       b.ValidityEndTime AS CampaignEndTime,
       c.Name AS CouponsName,
       c.Price AS CouponsAmount,
       (CASE WHEN c.IsLimited=1 then 1 else 2 END) AS IsLimited,
       c.FullAmount AS MinAmount,
       b.ValidityEndTime AS CouponsEndTime,
       b.ValidityStartTime AS CouponsStartTime,
       (case when b.IsRepeateGet=1 then 1 else 2 END) AS IsCanRepeatedReceive,
       b.ReceiveAddress AS ReceiveAddress,
       b.ReceiveEndTime AS ReceiveEndTime,
       b.ReceiveStartTime AS ReceiveStartTime,
       b.ReceiveMode AS ReceiveMethod,
       b.ProvideNum AS ReceiveNum,
       c.CreateTime AS CreateTime,
       c.Price AS Price,
       b.RemainCouponNum AS RemainCouponNum,
       b.ID AS CouponConfigId,
       d.[TYPE] AS CouponsType,
       (CASE WHEN b.IconUrl IS NULL THEN N'''' WHEN b.IconUrl=N'''' THEN N'''' ELSE @hostName+b.IconUrl END) AS IconUrl
   FROM MK_Campaign a  WITH ( NOLOCK ) 
                    INNER JOIN MK_CouponConfig b WITH ( NOLOCK ) ON a.ID=b.CampaignID AND (b.[ReceiveStartTime] <= @dt) AND (b.[ReceiveEndTime] >= @dt) 
                    INNER JOIN [dbo].[MK_Coupon] AS c WITH ( NOLOCK ) ON b.[CouponID] = c.[ID] and (2 = b.[CouponGetType]) AND (4 = a.[State]) 
                    INNER JOIN [dbo].[MK_CouponRestriction] AS d WITH ( NOLOCK ) ON c.[ID] = d.[CompainID]  AND (2 = d.[Type]) 
                    LEFT OUTER JOIN MK_CouponRestrictionProduct AS e WITH ( NOLOCK ) ON d.[ID] = e.[CouponRestrictionID] 
                    LEFT OUTER JOIN SL_Product AS f WITH ( NOLOCK ) ON (e.ProductID = f.ID)
                    INNER JOIN ViewOrganizationCommunityForInterface v ON v.ID=f.PublisherID
                    where v.CommunityID=@CommunityID
   ) t
ORDER BY t.Price--@Sort
offset (@PageIndex-1)*@PageSize ROWS  FETCH NEXT @PageIndex*@PageSize ROWS ONLY'
end
--执行SQL
exec sp_executesql @strSql,N'@PageIndex int,@PageSize int,@Sort nvarchar(50),@hostName nvarchar(100),@CommunityID UNIQUEIDENTIFIER,@IsGetCount bit,@dt datetime2(7)',@PageIndex =@PageIndex,@PageSize =@PageSize,@Sort=@Sort,@hostName=@hostName,@CommunityID=@CommunityID,@IsGetCount=@IsGetCount,@dt=@dt
set nocount off;

View Code

此地必要在乎的是,存款和储蓄施行是,先关闭计数,set nocount on;,然后再张开set nocount off;,这样能够荣升质量。还会有正是运用WITH ( NOLOCK )允许脏读,提高查询效能。

此处境遇多少个很新奇的标题,作者使用exec sp_executesql @strSql,N'....'的措施来实行是未曾难点的,而风流倜傥旦本人利用拼接sql的不二秘诀,会报错,因为sql字符串被截断了,只截取到了4000个字符长度,尽管作者把字符串变量长度设置为nvarchar(max)也没用。

分页情势利用Sqlserver贰零壹叁以上版本才支撑的迅猛方式:offset ... FETCH NEXT ...ROWS ONLY

本来Linq的施行时间测量检验:

图片 5BLW.png)图片 6

作者修正为存储进程之后的实行时间测验:

图片 7

质量差别极度明显。

筛选数据

急需:即便数据库中设有OrderNum相通,且IsDefault差异的记录,那么IsDefault值为0的笔录将替换值为1的笔录(IsDefault值为1的记录不显得)。

图片 8

是因为查出来的数目十分的少,100条以内,所以自个儿是直接全体询问到List内部存款和储蓄器中,然后在内部存款和储蓄器中张开数量过滤的操作,思来想去都认为本身如下的落到实处方式很low,可是自身后生可畏世又没悟出好的不二秘诀,不驾驭我们有未有好的情势?

            var newList = list.ToList();
            //筛选出哪些排序号有重复
            var orderNumList = newList.GroupBy(g => g.OrderNum).Select(g => new { orderNum = g.Key, count = g.Count() }).Where(g => g.count > 1).Select(s => s.orderNum).ToList();
            var cfList = newList.Where(w => orderNumList.Contains(w.OrderNum)); //获取有重复排序号的记录

            var cfDefaultList = cfList.Where(w => w.IsDefault);  //默认模块记录
            var cfNoDefaultList = cfList.Where(w => w.IsDefault == false); //非默认模块记录

            var intersectedList = from d in cfDefaultList join f in cfNoDefaultList on d.OrderNum equals f.OrderNum where d.IsDefault!= f.IsDefault select d;

            var newIntersectedList = intersectedList.Distinct().ToArray(); //排序号相同,既存在默认记录也存在非默认记录的数据 

                if (newIntersectedList != null && newIntersectedList.Length > 0)
                {
                    for (int i = 0; i < newIntersectedList.Length; i++)
                    {
                        if (newList.Contains(newIntersectedList[i]))
                        {
                            newList.Remove(newIntersectedList[i]);
                        }
                    }
                }
           newList = newList.OrderBy(x => x.OrderNum).ToList();

 以上的newList就代码截图中的数据。

优化API接口

有二个API接口平日卡顿,何况非常不平稳,快的时候2~3秒,慢的时候10秒去了。

接口必要:依照社区ID获取降价券记录。

分析:

  • 承受给API接口提供数据的系统中,缺乏相当多索引,存在不菲慢查询视图。
  • 原来的LINQ完成形式是在内部存款和储蓄器中分页,响应速度太慢。
  • 并发必要的情事下,财富占用。

优化思路:

本文由银河999游戏下载发布于银河999游戏下载,转载请注明出处:数据筛选和API优化

关键词:

频道精选

最火资讯