需求的提出：
这个月月初在项目开发的时候，需要做一个折线图的统计图形，类似于下图这样的效果：
，但是项目的需求又不一样，具体如下图 
由上图可知道，具体的某一个项目是不确定的，统计的指标也是不确定的，而且统计的数据类型，时间区间都是不确定的，也就是说在数据获取的过程中，所要涉及到的数据表是非常复杂的，那么用户场景我们就可以确定了。
解决方案的提出：
最开始我以为只是一个简单的数据统计问题，解决的方案为将用户统计的各种指标，数据类型作为查询条件，用户的统计时间区间按照具体的时间长度分为按天，周，月，季度作为时间指标，每次从数据库查询，然后将返回的datatable进行遍历，取得相应的数据，再将相应的数据放回到一个dictionary<key,list<float>>中去，返回这个dic的json格式的数据，进行展示就行了。
方案的实现：
主要的代码如下：
存储过程：
USE [FDCZHGLXT] GO /****** Object: StoredProcedure [dbo].[LDCX_Select_XMGK_QZQK_HZ] Script Date: 11/14/2015 23:13:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ------------------------------------- --用途： --项目名称： --说明： exec LDCX_Select_XMGK_QZQK_HZ ‘d099ec96-e79f-405a-a70b-311bd5e952a2‘,‘2001/1/1 0:00:00‘,‘2016/1/1 0:00:00‘,‘‘,‘‘ --时间：2015-7-3 ------------------------------------ ALTER PROCEDURE [dbo].[LDCX_Select_XMGK_QZQK_HZ] @ProjGUID uniqueidentifier, @QS_QYSJ datetime, @JZ_QYSJ datetime , @QZLB varchar(200), @HJ varchar(200) AS begin select ServiceItem,HJOrder,HJ ,cast(isnull(SUM(BldArea),0.00) as numeric(18,2)) AS BldArea ,COUNT(*) as JS, cast(isnull(SUM(HTJE)/10000,0.00) as numeric(18,2)) as HtJE ,isnull(SUM(DKJS),0) as DKJS,cast(isnull(SUM(DKJE)/10000,0.00) as numeric(18,2)) AS DKJE,cast(isnull(SUM(FKJE)/10000,0.00) as numeric(18,2)) as FKJE,isnull(SUM(FKJS),0) AS FKJS from ( select distinct s_SaleService.ServiceItem,dbo.s_SaleService_Get_Tache_By_Type(s_SaleService.ServiceItem,s_SaleService.Sequence) as HJ ,dbo.s_SaleService_Get_TacheOrder_By_Type(s_SaleService.ServiceItem,s_SaleService.Sequence) as HJOrder ,s_SaleService.Sequence ,p_Project.ProjGUID ,s_Contract.BldArea ,s_Contract.HtTotal AS HTJE ,s_Contract.ContractGUID ,(case s_SaleService.ServiceItem when ‘按揭服务‘then s_Contract.AjTotal when ‘公积金服务‘ then s_Contract.GjjTotal else 0 end ) as DKJE, (case s_SaleService.ServiceItem when ‘按揭服务‘then 1 when ‘公积金服务‘ then 1 else 0 end ) as DKJS , (case s_SaleService.ServiceItem when ‘按揭服务‘ then isnull((select SUM(Amount) from s_Getin where (s_Getin.ItemName=‘银行按揭‘ ) and s_Getin.[Status]=‘‘ and s_Getin.SaleGUID = s_Contract.TradeGUID),0.00) when ‘公积金服务‘then isnull((select SUM(Amount) from s_Getin where (s_Getin.ItemName=‘公积金贷款‘ ) and s_Getin.[Status]=‘‘ and s_Getin.SaleGUID = s_Contract.TradeGUID),0.00) else 0 end ) as FKJE, (case s_SaleService.ServiceItem when ‘按揭服务‘ then 1 when ‘公积金服务‘ then 1 else 0 end ) as FKJS from s_Contract inner join p_Room on p_Room.RoomGUID=s_Contract.RoomGUID inner join p_Building on p_Building.BldGUID=p_Room.BldGUID inner join p_Project on p_Project.ProjGUID=p_Building.ProjGUID inner join s_SaleService on s_SaleService.ContractGUID=s_Contract.ContractGUID where s_Contract.[Status]=‘激活‘ and s_Contract.Bz<>‘企业自留房‘ and (@ProjGUID=‘00000000-0000-0000-0000-000000000000‘ or p_Project.ProjGUID =@ProjGUID) and (Year(@QS_QYSJ)=1900 or s_Contract.QSDate>=@QS_QYSJ) and (Year(@JZ_QYSJ)=1900 or s_Contract.QSDate<@JZ_QYSJ) and (@QZLB=‘‘ or ServiceItem=@QZLB) and (@HJ=‘‘ or dbo.s_SaleService_Get_Tache_By_Type(ServiceItem,Sequence)=@HJ) ) as temp group by ServiceItem,HJOrder,HJ order by ServiceItem,HJOrder,HJ end
c#代码：
/// <summary> /// 返回数据列表 /// </summary> /// <param name="dtList">数据源</param> /// <param name="tjlb">统计类别</param> /// <param name="tjsj">统计时间</param> /// <returns>key-List</returns> public System.Collections.Generic.Dictionary<string, System.Collections.Generic.List<float>> CreatData(System.Collections.Generic.List<System.Data.DataTable> dtList, string tjlb, string tjsj) { System.Collections.Generic.Dictionary<string, System.Collections.Generic.List<float>> dc = new System.Collections.Generic.Dictionary<string, System.Collections.Generic.List<float>>(); if (tjlb == "ServiceItem") { for (int j = 0; j < dtList.Count; j++) { if (dtList[j].Rows.Count > 0) { dtList[j] = AddHeJ(dtList[j]); for (int i = 0; i < dtList[j].Rows.Count; i++) { if (dtList[j].Rows[i]["HJ"].ToString() == "") { System.Collections.Generic.List<float> listFloat = new System.Collections.Generic.List<float>(); try { dc.Add(dtList[j].Rows[i]["ServiceItem"].ToString(), listFloat); } catch (Exception e) { } } } } } for (int j = 0; j < dtList.Count; j++) { foreach (var item in dc) { dc[item.Key].Add(0); } } for (int j = 0; j < dtList.Count; j++) { if (dtList[j].Rows.Count > 0) { dtList[j] = AddHeJ(dtList[j]); for (int i = 0; i < dtList[j].Rows.Count; i++) { if (dtList[j].Rows[i]["HJ"].ToString() == "") { dc[dtList[j].Rows[i]["ServiceItem"].ToString()][j]=(float.Parse(dtList[j].Rows[i][tjsj].ToString())); } } } } } else if (tjlb == "ServiceItem♀HJ") { for (int j = 0; j < dtList.Count; j++) { if (dtList[j].Rows.Count > 0) { for (int i = 0; i < dtList[j].Rows.Count; i++) { System.Collections.Generic.List<float> listFloat = new System.Collections.Generic.List<float>(); try { dc.Add(dtList[j].Rows[i]["ServiceItem"].ToString()+":"+dtList[j].Rows[i]["HJ"].ToString(), listFloat); } catch (Exception e) { } } } } for (int j = 0; j < dtList.Count; j++) { foreach (var item in dc) { dc[item.Key].Add(0); } } for (int j = 0; j < dtList.Count; j++) { if (dtList[j].Rows.Count > 0) { for (int i = 0; i < dtList[j].Rows.Count; i++) { dc[dtList[j].Rows[i]["ServiceItem"].ToString() + ":" + dtList[j].Rows[i]["HJ"].ToString()][j] = (float.Parse(dtList[j].Rows[i][tjsj].ToString())); } } } } else if (tjlb == "HJ") { for (int j = 0; j < dtList.Count; j++) { dtList[j] = AddHJ(dtList[j]); if (dtList[j].Rows.Count > 0) { for (int i = 0; i < dtList[j].Rows.Count; i++) { if (dtList[j].Rows[i]["ServiceItem"].ToString() == "") { System.Collections.Generic.List<float> listFloat = new System.Collections.Generic.List<float>(); try { dc.Add(dtList[j].Rows[i]["HJ"].ToString(), listFloat); } catch (Exception e) { } } } } } for (int j = 0; j < dtList.Count; j++) { foreach (var item in dc) { dc[item.Key].Add(0); } } for (int j = 0; j < dtList.Count; j++) { if (dtList[j].Rows.Count > 0) { dtList[j] = AddHeJ(dtList[j]); for (int i = 0; i < dtList[j].Rows.Count; i++) { if (dtList[j].Rows[i]["ServiceItem"].ToString() == "") { dc[dtList[j].Rows[i]["HJ"].ToString()][j] = (float.Parse(dtList[j].Rows[i][tjsj].ToString())); } } } } } return dc; }
然后的运行的结果是差不多要3分钟才能处理完成。但是这个时间是不能接受的。
问题的出现：
在进行数据统计汇总的过程中，时间复杂度与空间复杂度非常高，占有的计算机资源和时间资源非常高。
分析原因：
每张表大约有10k的数据量，统计过程中涉及到5张数据表，所以在一次取出数据之后大约有5X10k的数据量，在一次性进行处理，那么数据的空间复杂度一定会很高。时间复杂度也会很高。
解决方向：
1：寻找更优越的算法，（由于我愚笨，到目前还没找到）。
2：采用lambda表达式，进行数据处理。
3：在数据库中就将数据统计处理完，返回需要的数据。
方法尝试：
在将问题向老大提出之后，老大给出一个解决问题的方向，由于每条数据的都有对应的时间，可以将每条数据的时间也返回，在进行遍历，将相应时间的数据放到相应的时间区间上去，使用lambda表达式，
存储过程：
USE [FDCZHGLXT] GO /****** Object: StoredProcedure [dbo].[LDCX_Select_XMGK_QZQK_HZ_Date] Script Date: 11/14/2015 22:58:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ------------------------------------- --用途： --项目名称： --说明： exec LDCX_Select_XMGK_QZQK_HZ ‘d099ec96-e79f-405a-a70b-311bd5e952a2‘,‘2001/1/1 0:00:00‘,‘2016/1/1 0:00:00‘,‘‘,‘‘ --时间：2015-7-3 ------------------------------------ ALTER PROCEDURE [dbo].[LDCX_Select_XMGK_QZQK_HZ_Date] @ProjGUID uniqueidentifier, @QS_QYSJ datetime, @JZ_QYSJ datetime , @QZLB varchar(200), @HJ varchar(200), @TJLB varchar(100) AS if @TJLB=‘ServiceItem♀HJ‘ begin select ServiceItem,HJOrder,HJ ,cast(isnull(SUM(BldArea),0.00) as numeric(18,2)) AS BldArea ,COUNT(*) as JS,QSDate, cast(isnull(SUM(HTJE)/10000,0.00) as numeric(18,2)) as HtJE ,isnull(SUM(DKJS),0) as DKJS,cast(isnull(SUM(DKJE)/10000,0.00) as numeric(18,2)) AS DKJE,cast(isnull(SUM(FKJE)/10000,0.00) as numeric(18,2)) as FKJE,isnull(SUM(FKJS),0) AS FKJS from ( select distinct s_Contract.QSDate, s_SaleService.ServiceItem,dbo.s_SaleService_Get_Tache_By_Type(s_SaleService.ServiceItem,s_SaleService.Sequence) as HJ ,dbo.s_SaleService_Get_TacheOrder_By_Type(s_SaleService.ServiceItem,s_SaleService.Sequence) as HJOrder ,s_SaleService.Sequence ,p_Project.ProjGUID ,s_Contract.BldArea ,s_Contract.HtTotal AS HTJE ,s_Contract.ContractGUID ,(case s_SaleService.ServiceItem when ‘按揭服务‘then s_Contract.AjTotal when ‘公积金服务‘ then s_Contract.GjjTotal else 0 end ) as DKJE, (case s_SaleService.ServiceItem when ‘按揭服务‘then 1 when ‘公积金服务‘ then 1 else 0 end ) as DKJS , (case s_SaleService.ServiceItem when ‘按揭服务‘ then isnull((select SUM(Amount) from s_Getin where (s_Getin.ItemName=‘银行按揭‘ ) and s_Getin.[Status]=‘‘ and s_Getin.SaleGUID = s_Contract.TradeGUID),0.00) when ‘公积金服务‘then isnull((select SUM(Amount) from s_Getin where (s_Getin.ItemName=‘公积金贷款‘ ) and s_Getin.[Status]=‘‘ and s_Getin.SaleGUID = s_Contract.TradeGUID),0.00) else 0 end ) as FKJE, (case s_SaleService.ServiceItem when ‘按揭服务‘ then 1 when ‘公积金服务‘ then 1 else 0 end ) as FKJS from s_Contract inner join p_Room on p_Room.RoomGUID=s_Contract.RoomGUID inner join p_Building on p_Building.BldGUID=p_Room.BldGUID inner join p_Project on p_Project.ProjGUID=p_Building.ProjGUID inner join s_SaleService on s_SaleService.ContractGUID=s_Contract.ContractGUID where s_Contract.[Status]=‘激活‘ and s_Contract.Bz<>‘企业自留房‘ and (@ProjGUID=‘00000000-0000-0000-0000-000000000000‘ or p_Project.ProjGUID =@ProjGUID) and (Year(@QS_QYSJ)=1900 or s_Contract.QSDate>=@QS_QYSJ) and (Year(@JZ_QYSJ)=1900 or s_Contract.QSDate<@JZ_QYSJ) and (@QZLB=‘‘ or ServiceItem=@QZLB) and (@HJ=‘‘ or dbo.s_SaleService_Get_Tache_By_Type(ServiceItem,Sequence)=@HJ) ) as temp group by ServiceItem,HJOrder,HJ,QSDate order by ServiceItem,HJOrder,HJ,QSDate end else if @TJLB=‘ServiceItem‘ begin select ServiceItem, cast(isnull(SUM(BldArea),0.00) as numeric(18,2)) AS BldArea ,COUNT(*) as JS,QSDate, cast(isnull(SUM(HTJE)/10000,0.00) as numeric(18,2)) as HtJE ,isnull(SUM(DKJS),0) as DKJS,cast(isnull(SUM(DKJE)/10000,0.00) as numeric(18,2)) AS DKJE,cast(isnull(SUM(FKJE)/10000,0.00) as numeric(18,2)) as FKJE,isnull(SUM(FKJS),0) AS FKJS from ( select distinct s_Contract.QSDate, s_SaleService.ServiceItem,dbo.s_SaleService_Get_Tache_By_Type(s_SaleService.ServiceItem,s_SaleService.Sequence) as HJ ,dbo.s_SaleService_Get_TacheOrder_By_Type(s_SaleService.ServiceItem,s_SaleService.Sequence) as HJOrder ,s_SaleService.Sequence ,p_Project.ProjGUID ,s_Contract.BldArea ,s_Contract.HtTotal AS HTJE ,s_Contract.ContractGUID ,(case s_SaleService.ServiceItem when ‘按揭服务‘then s_Contract.AjTotal when ‘公积金服务‘ then s_Contract.GjjTotal else 0 end ) as DKJE, (case s_SaleService.ServiceItem when ‘按揭服务‘then 1 when ‘公积金服务‘ then 1 else 0 end ) as DKJS , (case s_SaleService.ServiceItem when ‘按揭服务‘ then isnull((select SUM(Amount) from s_Getin where (s_Getin.ItemName=‘银行按揭‘ ) and s_Getin.[Status]=‘‘ and s_Getin.SaleGUID = s_Contract.TradeGUID),0.00) when ‘公积金服务‘then isnull((select SUM(Amount) from s_Getin where (s_Getin.ItemName=‘公积金贷款‘ ) and s_Getin.[Status]=‘‘ and s_Getin.SaleGUID = s_Contract.TradeGUID),0.00) else 0 end ) as FKJE, (case s_SaleService.ServiceItem when ‘按揭服务‘ then 1 when ‘公积金服务‘ then 1 else 0 end ) as FKJS from s_Contract inner join p_Room on p_Room.RoomGUID=s_Contract.RoomGUID inner join p_Building on p_Building.BldGUID=p_Room.BldGUID inner join p_Project on p_Project.ProjGUID=p_Building.ProjGUID inner join s_SaleService on s_SaleService.ContractGUID=s_Contract.ContractGUID where s_Contract.[Status]=‘激活‘ and s_Contract.Bz<>‘企业自留房‘ and (@ProjGUID=‘00000000-0000-0000-0000-000000000000‘ or p_Project.ProjGUID =@ProjGUID) and (Year(@QS_QYSJ)=1900 or s_Contract.QSDate>=@QS_QYSJ) and (Year(@JZ_QYSJ)=1900 or s_Contract.QSDate<@JZ_QYSJ) and (@QZLB=‘‘ or ServiceItem=@QZLB) and (@HJ=‘‘ or dbo.s_SaleService_Get_Tache_By_Type(ServiceItem,Sequence)=@HJ) ) as temp group by ServiceItem,QSDate order by ServiceItem,QSDate end else if @TJLB=‘HJ‘ begin select HJOrder,HJ ,cast(isnull(SUM(BldArea),0.00) as numeric(18,2)) AS BldArea ,COUNT(*) as JS,QSDate, cast(isnull(SUM(HTJE)/10000,0.00) as numeric(18,2)) as HtJE ,isnull(SUM(DKJS),0) as DKJS,cast(isnull(SUM(DKJE)/10000,0.00) as numeric(18,2)) AS DKJE,cast(isnull(SUM(FKJE)/10000,0.00) as numeric(18,2)) as FKJE,isnull(SUM(FKJS),0) AS FKJS from ( select distinct s_Contract.QSDate, s_SaleService.ServiceItem,dbo.s_SaleService_Get_Tache_By_Type(s_SaleService.ServiceItem,s_SaleService.Sequence) as HJ ,dbo.s_SaleService_Get_TacheOrder_By_Type(s_SaleService.ServiceItem,s_SaleService.Sequence) as HJOrder ,s_SaleService.Sequence ,p_Project.ProjGUID ,s_Contract.BldArea ,s_Contract.HtTotal AS HTJE ,s_Contract.ContractGUID ,(case s_SaleService.ServiceItem when ‘按揭服务‘then s_Contract.AjTotal when ‘公积金服务‘ then s_Contract.GjjTotal else 0 end ) as DKJE, (case s_SaleService.ServiceItem when ‘按揭服务‘then 1 when ‘公积金服务‘ then 1 else 0 end ) as DKJS , (case s_SaleService.ServiceItem when ‘按揭服务‘ then isnull((select SUM(Amount) from s_Getin where (s_Getin.ItemName=‘银行按揭‘ ) and s_Getin.[Status]=‘‘ and s_Getin.SaleGUID = s_Contract.TradeGUID),0.00) when ‘公积金服务‘then isnull((select SUM(Amount) from s_Getin where (s_Getin.ItemName=‘公积金贷款‘ ) and s_Getin.[Status]=‘‘ and s_Getin.SaleGUID = s_Contract.TradeGUID),0.00) else 0 end ) as FKJE, (case s_SaleService.ServiceItem when ‘按揭服务‘ then 1 when ‘公积金服务‘ then 1 else 0 end ) as FKJS from s_Contract inner join p_Room on p_Room.RoomGUID=s_Contract.RoomGUID inner join p_Building on p_Building.BldGUID=p_Room.BldGUID inner join p_Project on p_Project.ProjGUID=p_Building.ProjGUID inner join s_SaleService on s_SaleService.ContractGUID=s_Contract.ContractGUID where s_Contract.[Status]=‘激活‘ and s_Contract.Bz<>‘企业自留房‘ and (@ProjGUID=‘00000000-0000-0000-0000-000000000000‘ or p_Project.ProjGUID =@ProjGUID) and (Year(@QS_QYSJ)=1900 or s_Contract.QSDate>=@QS_QYSJ) and (Year(@JZ_QYSJ)=1900 or s_Contract.QSDate<@JZ_QYSJ) and (@QZLB=‘‘ or ServiceItem=@QZLB) and (@HJ=‘‘ or dbo.s_SaleService_Get_Tache_By_Type(ServiceItem,Sequence)=@HJ) ) as temp group by HJOrder,HJ,QSDate order by HJOrder,HJ,QSDate end
c#代码
/// <summary> /// 根据时间段列表返回数据源 /// </summary> /// <param name="Project">项目guid</param> /// <param name="bgDate">开始时间</param> /// <param name="edDate">结束时间</param> /// <returns>数据源集合</returns> public Dictionary<string, List<float>> GetDataResult(Guid Project, DateTime bgDate, DateTime edDate, string tjzb, string tjsj) { System.Collections.Generic.List<string> xAis = new System.Collections.Generic.List<string>(); System.Data.DataTable dt = new System.Data.DataTable(); List<DataRow> datarowList = new List<DataRow>(); xAis = GetListDate(bgDate, edDate); dt = GetData(Project, bgDate, edDate, tjzb); Dictionary<string, List<float>> dc = new Dictionary<string, List<float>>(); List<float> listXzero = new List<float>(); for (int i = 0; i < xAis.Count-1 ; i++) { listXzero.Add(0); } for (int i = 0; i < xAis.Count-1; i++) { float x = 0; string ServiceItem = string.Empty; var a = dt.AsEnumerable().Where(d => d.Field<DateTime>("QSDate") < DateTime.Parse(xAis[i + 1]) && d.Field<DateTime>("QSDate") > DateTime.Parse(xAis[i])).Select(t => new { DataType = t.Field<decimal>(tjsj), ServiceItem = t.Field<string>("ServiceItem") }).ToList(); foreach (var item in a) { try { dc.Add(item.ServiceItem, listXzero); } catch (Exception e) { } } foreach (var item in a) { if (ServiceItem != item.ServiceItem) { //if (item.ServiceItem == "入伙服务") //{ // string aaaa = "hahha"; //} if(ServiceItem!="") try { dc[ServiceItem][i] = x; } catch (Exception e) { //第一次会出现异常 } ServiceItem = item.ServiceItem; x = 0; } x = x +float.Parse( item.DataType.ToString()); } dc[ServiceItem][i] = x; //最后一项数据。 } return dc; }
如上所示，代码也清晰多了，经过优化之后时间减少到只用30s左右，效果是很明显的，但是我对lambda使用还不是很熟练，所以只能达到这个时间，我相信如果我对lambda非常熟练的话，还能减少很多。
最后采用的是将需要统计的数据都放到数据库中进行处理，大致思路是将统计的时间区间通过一个函数处理为一张表，在用游标对这张表的每条数据进行循环，对每条循环的数据的起始时间和截止时间作为统计时间的条件进行查询，并将查询的结果在数据库中进行统计，得到多张表，每个表的数据就是需要得最终数据，最后服务端只需要从每张表中拿到需要的数据就行。
时间分区函数如下
USE [FDCZHGLXT] GO /****** Object: UserDefinedFunction [dbo].[GetDateTableWithBDateAndEDate] Script Date: 11/14/2015 23:20:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: 张杰 -- Create date:2015，11，2 -- Description: 获取开始时间和结束时间按照特定的规律生成一张表 -- ============================================= ALTER FUNCTION [dbo].[GetDateTableWithBDateAndEDate] ( @QS_QYSJ datetime, @JZ_QYSJ datetime ) RETURNS @DateTmp table( QS_QYSJ datetime,JZ_QYSJ datetime ) AS BEGIN if @JZ_QYSJ-@QS_QYSJ<14 begin while @QS_QYSJ<@JZ_QYSJ begin insert into @DateTmp values(@QS_QYSJ,@QS_QYSJ+1) set @QS_QYSJ=@QS_QYSJ+1 end end else if @JZ_QYSJ-@QS_QYSJ<60 begin while @QS_QYSJ<@JZ_QYSJ begin if @JZ_QYSJ-@QS_QYSJ<7 begin insert into @DateTmp values(@QS_QYSJ,@JZ_QYSJ) end else begin insert into @DateTmp values(@QS_QYSJ,@QS_QYSJ+7) end set @QS_QYSJ=@QS_QYSJ+7 end end else if @JZ_QYSJ-@QS_QYSJ<365 begin while @QS_QYSJ<@JZ_QYSJ begin if dateadd(month,1, @QS_QYSJ)>@JZ_QYSJ begin insert into @DateTmp values(@QS_QYSJ,@JZ_QYSJ) end else begin insert into @DateTmp values(@QS_QYSJ,dateadd(month,1, @QS_QYSJ) ) end set @QS_QYSJ=dateadd(month,1, @QS_QYSJ) end end else begin while @QS_QYSJ<@JZ_QYSJ begin if dateadd(month,4, @QS_QYSJ)>@JZ_QYSJ begin insert into @DateTmp values(@QS_QYSJ,@JZ_QYSJ) end else begin insert into @DateTmp values(@QS_QYSJ,dateadd(month,4, @QS_QYSJ) ) end set @QS_QYSJ=dateadd(month,4, @QS_QYSJ) end end RETURN END
存储过程如下：
USE [FDCZHGLXT] GO /****** Object: StoredProcedure [dbo].[GetDatasetByDate] Script Date: 11/14/2015 23:24:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: 张杰 -- Create date: 11.3 -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[GetDatasetByDate] -- Add the parameters for the stored procedure here @ProjGUID uniqueidentifier, @QS_QYSJ datetime, @JZ_QYSJ datetime, @TJLB varchar(100), @HJ varchar(200), @TJSJ varchar(100) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. declare DateCursor CURSOR FOR (select * from GetDateTableWithBDateAndEDate(@QS_QYSJ,@JZ_QYSJ)) OPEN DateCursor; fetch next from DateCursor into @QS_QYSJ,@JZ_QYSJ; while @@FETCH_STATUS = 0 begin select @QS_QYSJ,@JZ_QYSJ, (case @TJLB WHEN ‘ServiceItem‘ THEN ServiceItem WHEN ‘HJ‘ THEN HJ ELSE ServiceItem+‘-‘+HJ END), --ServiceItem,HJ, (case @TJSJ when ‘BldArea‘ then cast(isnull(SUM(BldArea),0.00) as numeric(18,2)) when ‘JS‘ then COUNT(*) when ‘HtJE‘ then cast(isnull(SUM(HTJE)/10000,0.00) as numeric(18,2)) when ‘DKJS‘ then isnull(SUM(DKJS),0) when ‘DKJE‘ then cast(isnull(SUM(DKJE)/10000,0.00) as numeric(18,2)) when ‘FKJE‘ then cast(isnull(SUM(FKJE)/10000,0.00) as numeric(18,2)) when ‘FKJS‘ then isnull(SUM(FKJS),0) else ‘‘ end ) as JG from (select distinct s_SaleService.ServiceItem, dbo.s_SaleService_Get_Tache_By_Type(s_SaleService.ServiceItem,s_SaleService.Sequence) as HJ ,p_Project.ProjGUID ,s_Contract.BldArea ,s_Contract.HtTotal AS HTJE ,s_Contract.ContractGUID, (case s_SaleService.ServiceItem when ‘按揭服务‘then s_Contract.AjTotal when ‘公积金服务‘ then s_Contract.GjjTotal else 0 end ) as DKJE, (case s_SaleService.ServiceItem when ‘按揭服务‘then 1 when ‘公积金服务‘ then 1 else 0 end ) as DKJS , (case s_SaleService.ServiceItem when ‘按揭服务‘ then isnull((select SUM(Amount) from s_Getin where (s_Getin.ItemName=‘银行按揭‘ ) and s_Getin.[Status]=‘‘ and s_Getin.SaleGUID = s_Contract.TradeGUID),0.00) when ‘公积金服务‘then isnull((select SUM(Amount) from s_Getin where (s_Getin.ItemName=‘公积金贷款‘ ) and s_Getin.[Status]=‘‘ and s_Getin.SaleGUID = s_Contract.TradeGUID),0.00) else 0 end ) as FKJE, (case s_SaleService.ServiceItem when ‘按揭服务‘ then 1 when ‘公积金服务‘ then 1 else 0 end ) as FKJS from s_Contract inner join p_Room on p_Room.RoomGUID=s_Contract.RoomGUID inner join p_Building on p_Building.BldGUID=p_Room.BldGUID INNER JOIN p_Project on p_Project.ProjGUID=p_Building.ProjGUID inner join s_SaleService on s_SaleService.ContractGUID=s_Contract.ContractGUID where s_Contract.QSDate>=@QS_QYSJ and s_Contract.QSDate<@JZ_QYSJ and s_Contract.Status=‘激活‘ and s_Contract.BZ<>‘企业自留房‘ and p_Project.ProjGUID=@ProjGUID and dbo.s_SaleService_Get_Tache_By_Type(ServiceItem,Sequence)<>@HJ) as temp group by (case @TJLB WHEN ‘ServiceItem‘ THEN ServiceItem WHEN ‘HJ‘ THEN HJ ELSE ServiceItem+‘-‘+HJ END) order by (case @TJLB WHEN ‘ServiceItem‘ THEN ServiceItem WHEN ‘HJ‘ THEN HJ ELSE ServiceItem+‘-‘+HJ END) fetch next from DateCursor into @QS_QYSJ,@JZ_QYSJ; end close DateCursor;--关闭游标 DEALLOCATE DateCursor;--释放游标 END
服务器端代码如下：
/// <summary> /// 根据时间段列表返回数据源 /// </summary> /// <param name="Project">项目guid</param> /// <param name="bgDate">开始时间</param> /// <param name="edDate">结束时间</param> /// <returns>数据源集合</returns> public Dictionary<string, List<float>> GetDataResult(Guid Project, DateTime bgDate, DateTime edDate, string tjzb, string tjsj) { System.Collections.Generic.List<string> xAis = new System.Collections.Generic.List<string>(); DataSet ds = new DataSet(); List<DataRow> datarowList = new List<DataRow>(); xAis = GetListDate(bgDate, edDate); ds = GetData(Project, bgDate, edDate, tjzb, tjsj); Dictionary<string, List<float>> dc = new Dictionary<string, List<float>>(); //这个地方添加key for (int i = 0; i < ds.Tables.Count; i++) { if (ds.Tables[i].Rows.Count > 0) { for (int j = 0; j < ds.Tables[i].Rows.Count; j++) { try { dc.Add(ds.Tables[i].Rows[j][2].ToString(), null); } catch (Exception e) { } } } } List<float>[] listXzero1 = new List<float>[dc.Count]; for (int i = 0; i < dc.Count; i++) { listXzero1[i] = new List<float>(); for (int j = 0; j < xAis.Count; j++) { try { listXzero1[i].Add(0); } catch (Exception e) { } } } List<string> test = new List<string>(dc.Keys); for (int i = 0; i < dc.Count; i++) { dc[test[i]] = listXzero1[i]; } //就是这块代码 for (int i = 0; i < ds.Tables.Count; i++) { if (ds.Tables[i].Rows.Count > 0) { DataTable dt1 = ds.Tables[i]; List<float> listTemp = new List<float>(); for (int j = 0; j < dt1.Rows.Count; j++) { //这里向dc中对应的key添加数据 listTemp = dc[dt1.Rows[j][2].ToString()]; listTemp[i] = float.Parse(dt1.Rows[j][3].ToString()); dc[dt1.Rows[j][2].ToString()] = listTemp; listTemp = null; } } } return dc; }
从上面可以看出，数据库和服务端分工很明确，所以代码很简单，逻辑很清晰，并且由于是在数据库中将数据处理好了，所以最后的处理时间只有3秒左右，并且由于分工的原因，服务器的资源也只用到了很少一部分。
总结：
1：算法很重要，如果我能想到很牛逼的算法，那么就没有后面什么事了。
2：解决问题的方向是多方面的。
3：lambda很强大，在解决问题的过程中，有幸遇到大牛的指点，他写的lambda，简直惊为天人。
4：数据与业务逻辑的分离非常重要，合理的利用可以给服务器减少非常多的压力。
5：在想不到牛逼的算法的时候，就要多会解决问题的方法。更多的思路，更广的知识面。
最后：
在处理问题的时候，前期都做好了，最后的时候，在将数据放到dictionary<key,list<float>>中时，出问题了，将某一个数据插入对应的key中时，结果发现其他key对应的list<float>中的值也被改变了，最后花了半天的时间才解决掉。原来是值类型和引用类型的区别没理解深刻，不同的key都指向了同一个list<float>。再次证明，基础知识没弄清楚，关键时刻还是会坑你一下。
希望以上数据优化的思路对您有所帮助。
原文：http://www.cnblogs.com/swpuzhangjie/p/4965922.html