SCPPO（九）:性能优化之停工装置按照开工装置更新价格

当年的春天

已于 2023-06-09 00:10:55 修改

阅读量1.8w

点赞数 13

分类专栏： ●【项目实战】 ● Data Base # 【SQLServer】文章标签：性能优化

于 2016-07-16 21:43:07 首次发布

本文链接：https://blog.csdn.net/zhanghan18333611647/article/details/51926184

版权

●【项目实战】同时被 3 个专栏收录

88 篇文章 21 订阅

订阅专栏

● Data Base

38 篇文章 15 订阅

订阅专栏

【SQLServer】

8 篇文章 10 订阅

订阅专栏

强烈推荐一个大神的人工智能的教程：http://www.captainai.net/zhanghan

【前言】

最近做的一个项目中客户提出了新的需求；在完成这次任务时候真是一波三折，当然在这个过程中收获许多，在此与大家共享！

【一波三折】

基础：1.企业中的装置会产生物料，物料价格是衡量物料的价值标准间接成为衡量装置的标准。

2.企业会每个月上传自己本月各个物料的价格，存在表开工装置表中；

3.由于各方面原因企业也会停工一部分装置，停工装置只会在某个月偶尔会发生，将停工时月份的该停工装置产生的物料价格村在停工装置表中；

需求：总部想了解下如果某企业的停工装置假如其是开工状态的话会产生多少效益。（比如：一个装置A它生产1号物品，它于2015年8月份申请停工，2015年8月份1号物品的价格是10000元【这时在停工装置表中该装置的1号物品价格为10000元，在开工装置表中2015年8月份的该装置的1号物品价格也为10000元】；一个企业不仅一个此装置还有许多开工的装置也在生产1号物品，到2016年6月份的时候其他装置生产的1号物品价格是15000元，此时在开工表中1号物品价格变为15000元，而停工装置表中的1号物品价格仍是10000元；现在需求是总部想看到假如装置A没有停工的话它在2016年6月份创造的价值应该是15000元）

思路：开工装置表写入新数据时将在开工表中的物品价格更新到停工表中，一图以蔽之！

实现方式：

1.一开始自己想到了数据量比较大，考虑到性能用一个存储过程搞定最好！当时由于时间等原因，最后决定用最简单的方式先实现功能再说！

（1）D层代码实现（由于其他层实现都较为简单在此不再赘述）：

        //组合下面的操作完成停工装置物料价格更新
        public bool changeMaterPrice(string companyCode, string companyName)
        {
            if (string.IsNullOrEmpty(companyCode) || string.IsNullOrEmpty(companyName)) 
            {
                return false;
            }
            else
            {
                #region 注释一条条更新性能太慢-zhanghan-2016年7月18日
                List<MACPStopInOutModel> GetAllStopInoutByComList = GetAllStopInoutByCom(companyCode, companyName).ToList(); //获取停工装置表中所有的数据
                if (GetAllStopInoutByComList.Count < 1)  //若停工装置表中为空则结束
                {
                    return false;
                }
                else
                {
                    for (int i = 0; i < GetAllStopInoutByComList.Count; i++)  //循环将停工装置表中的价格进行更新
                    {
                        List<MACPInOutModel> GetAllInoutByComUniMatlist = GetAllInoutByComUniMat(companyCode, companyName, GetAllStopInoutByComList[i].UnitCode, GetAllStopInoutByComList[i].MaterialCode).ToList(); //根据停工装置表中的条件查找开工装置表中对应的数据
                        if (GetAllInoutByComUniMatlist.Count > 0)   //开工装置表中有对应的物料价格对停工装置表价格进行更新
                        {
                            UpDateStopInout(companyCode, companyName, GetAllStopInoutByComList[i].UnitCode, GetAllStopInoutByComList[i].MaterialCode, GetAllInoutByComUniMatlist[0].MaterialPrice);
                        }
                        else  //开工装置表中没有对应的物料价格对停工装置表价格设置为0
                        {
                            UpDateStopInout(companyCode, companyName, GetAllStopInoutByComList[i].UnitCode, GetAllStopInoutByComList[i].MaterialCode, 0);
                        }
                    }
                    return true;
                }
                #endregion<span style="font-family:KaiTi_GB2312;">        </span><pre name="code" class="csharp">          }
     //根据企业ID和企业名称查询停工装置中的所有装置编码、物料编码
     public IList<MACPStopInOutModel> GetAllStopInoutByCom(string companyCode, string companyName)
     {
        if (string.IsNullOrEmpty(companyCode) || string.IsNullOrEmpty(companyName))
        {
             return null;
         }
       string sql = @"SELECT   [ID]
      ,[ModelCode]
      ,[ModelName]
      ,[CompanyCode]
      ,[CompanyName]
      ,[UnitCode]
      ,[UnitName]
      ,[UnitInOut]
      ,[MaterialCode]
      ,[MaterialName]
      ,[MaterialPrice]
      ,[CycleCode]
      ,[CycleName]
      ,[PriceResource]
      ,[InOut]
      ,[reportMonth]
      ,[flag]
      ,[Source]
FROM    dbo.T_MACP_Stop_InOut
WHERE   CompanyCode = '" + companyCode + "' AND CompanyName = '" + companyName + "'";
            ISQLQuery sqlLst = Session.CreateSQLQuery(sql);
            sqlLst.SetResultTransformer(Transformers.AliasToBean<MACPStopInOutModel>());
            return sqlLst.List<MACPStopInOutModel>();
     }

        //根据企业ID、企业名称、装置编码、物料编码、查询T_MACP_InOut中的离的最近的物料价格
        public IList<MACPInOutModel> GetAllInoutByComUniMat(string companyCode, string companyName, string unitCode, string materialCode)
        {
            if (string.IsNullOrEmpty(companyCode) || string.IsNullOrEmpty(companyName) || string.IsNullOrEmpty(unitCode) || string.IsNullOrEmpty(materialCode))
            {
                return null;
            }
            string sql = @"SELECT  *
FROM    [SCPPODB].[dbo].[T_MACP_InOut]
WHERE   ID IN ( SELECT  b.ID
                FROM    ( SELECT DISTINCT
                                    MAX(ID) ID ,
                                    [CompanyCode] ,
                                    [CompanyName] ,
                                    [UnitCode] ,
                                    [UnitName] ,
                                    [MaterialCode] ,
                                    [MaterialName] ,
                                    MAX(reportMonth) AS reportMonth
                          FROM      [SCPPODB].[dbo].[T_MACP_InOut]
                          GROUP BY  [CompanyCode] ,
                                    [CompanyName] ,
                                    [UnitCode] ,
                                    [UnitName] ,
                                    [MaterialCode] ,
                                    [MaterialName]
                        ) b )
        AND companycode =  '" + companyCode + "' AND CompanyName = '" + companyName + "' AND UnitCode = '" + unitCode + "' AND MaterialCode = '" + materialCode + "'";
            ;
            ISQLQuery sqlLst = Session.CreateSQLQuery(sql);
            sqlLst.SetResultTransformer(Transformers.AliasToBean<MACPInOutModel>());
            return sqlLst.List<MACPInOutModel>();
        }
        //根据企业ID、企业名称、装置编码、物料编码将从T_MACP_InOut查询出的物料价格更新到T_MACP_Stop_InOut中     
        public void UpDateStopInout(string companyCode, string companyName, string unitCode, string materialCode, decimal materialPrice)
        {
            string sql = @"UPDATE    T_MACP_Stop_InOut
SET MaterialPrice = '" + materialPrice + "' WHERE companycode = '" + companyCode + "'AND CompanyName = '" + companyName + "' AND UnitCode = '" + unitCode + "' AND MaterialCode = '" + materialCode + "'";
            Session.CreateSQLQuery(sql).ExecuteUpdate();
        }

（2）在本地运行没有问题，心中还是蛮高兴滴！只是时间比较长，查了一下更新406条数据时间大概是5分钟左右。

（3）发布到服务器上，进行测试：出现奇怪的现象（用我的笔记本的IE浏览器不报错更新成功406条数据大概5分钟左右，其他同事的笔记本都是卡死然后界面报一错误）

（4）在网上查的结果是服务太忙，查网络监控服务器等等，最后归结出写的这段代码的性能太差。

2.出现这么奇葩问题于是乎只能硬着头皮上；进行性能优化：

分析：

（1）上面的实现方式如果对406条数据进行价格更新需要进行流程：

①根据企业编码和名称去停工表中查询出所有装置信息

②循环406次：根据每条停工信息去开工表中查出价格——>将开工价格更新到停工表中

③数据库操作流程：打开数据库—>执行SQL语句—>关闭数据库

④执行次数：1+406+406=813次

（2）性能优化想法：将813次减少为1次。

实现思想：类似于存储过程，只需把企业的ID和名称传入，接下来都教给SQL来完成即可。

D层实现代码：

 //组合下面的操作完成停工装置物料价格更新
        public bool changeMaterPrice(string companyCode, string companyName)
        {
            if (string.IsNullOrEmpty(companyCode) || string.IsNullOrEmpty(companyName))
            {
                return false;
            }
            else
            {
                #region 该sql语句更新的条件是根据公司ID，公司名称，装置编码和物料编码-zhagnhan-2016年7月21日17:59:20
                string sql = @"UPDATE  [SCPPODB].[dbo].[T_MACP_Stop_InOut]
                SET     [SCPPODB].[dbo].[T_MACP_Stop_InOut].MaterialPrice = c.MaterialPrice
                FROM    ( SELECT    *
                          FROM      [SCPPODB].[dbo].[T_MACP_InOut] a
                          WHERE     ID IN (
                                    SELECT  b.ID
                                    FROM    ( SELECT DISTINCT
                                                        MAX(ID) ID ,
                                                        [CompanyCode] ,
                                                        [CompanyName] ,
                                                        [UnitCode] ,
                                                        [UnitName] ,
                                                        [MaterialCode] ,
                                                        [MaterialName] ,
                                                        MAX(reportMonth) AS reportMonth
                                              FROM      [SCPPODB].[dbo].[T_MACP_InOut]
                                              GROUP BY  [CompanyCode] ,
                                                        [CompanyName] ,
                                                        [UnitCode] ,
                                                        [UnitName] ,
                                                        [MaterialCode] ,
                                                        [MaterialName]
                                            ) b )
                        ) c
                        JOIN [SCPPODB].[dbo].[T_MACP_Stop_InOut] ON ( c.CompanyCode = [SCPPODB].[dbo].[T_MACP_Stop_InOut].CompanyCode
                                                                      AND c.CompanyName = [SCPPODB].[dbo].[T_MACP_Stop_InOut].CompanyName
                                                                      AND c.UnitCode = [SCPPODB].[dbo].[T_MACP_Stop_InOut].UnitCode
                                                                      AND c.MaterialCode = [SCPPODB].[dbo].[T_MACP_Stop_InOut].MaterialCode
                                                                      AND [SCPPODB].[dbo].[T_MACP_Stop_InOut].CompanyCode = '" + companyCode + "' AND [SCPPODB].[dbo].[T_MACP_Stop_InOut].CompanyName = '" + companyName + "')";
                Session.CreateSQLQuery(sql).ExecuteUpdate();
                return true;
            }
                #endregion
        }

（3）测试：同样的406条数据，用所有同事机器测试基本上秒更新。