SqlServer高级特性--存储过程

需求：

用户需要提交加密数据，提交之后需要解密还原，还原有两种结果：成功和失败！

100个用户，之前7天，判断是否有提交数据，如果有提交有数据，判断是否解密成功（分别存在两个表中）。如果没有提交，显示黄色；提交，未解密成功，红色；提交并解密成功，绿色，并显示对应时间。

分析：

测试过遍历所有单位，之后遍历每一天，去查询提交数据，然后在去查询解密数据。结果一个100家单位，竟然需要大概2-3分钟的时间来查询。（接收记录里面数据大约50w条，后期可能会更多），明显不合适，虽然加过缓存，但是不符合要求。

优化思路：

　　1. 我想用100家单位和7天同时去关联接收记录表，进行查询。也就是多条件左外连接查询。

　　2. 我要获取100家单位和7天的这样的表，需要100家单位和7个日期的笛卡尔积

　　3. 我需要一个7天日期的表，可以在存储过程中进行创建临时表。

整体思路就是这样的。

代码：

整体存储过程代码如下：

ALTER PROCEDURE [dbo].[AUTOTRANS]

    @xz   VARCHAR(10)='' ,

    @day INT =7,

    @industryCode varchar(10)=''

AS

BEGIN

    SET NOCOUNT ON;

    --定义一个变量：临时表，用户存储7天数据。

    DECLARE @days table (date date)

    DECLARE @today date = GETDATE()

    --定义一个循环，用于插入之前7天的数据，从当天开始

    WHILE @day >= 0

    BEGIN

        insert into @days

        select DATEadd(dd,-@day, @today );

        set @day =@day-1

    END

    --业务区域

        SELECT D.XZ_CODE,D.UNIT_CODE,D.UNIT_NAME,    CONVERT(varchar,D.RECEIVE_TIME,108) RECEIVE_TIME,

            CONVERT(varchar,MAX (E.RESTORE_TIME),108) RESTORE_TIME,D.date SELECT_DATE

        FROM (

        SELECT A.XZ_CODE,A.UNIT_CODE,A.UNIT_NAME,MAX(C.RECEIVE_TIME) RECEIVE_TIME,B.date FROM

        (SELECT XZ_CODE,UNIT_CODE,UNIT_NAME

        FROM T_UNIT_AUDIT WHERE XZ_CODE = @xz AND INDUSTRY_CODE = @industryCode

        ) A CROSS JOIN @days B

        LEFT JOIN T_FILE_RECEIVE_RECORD C ON A.UNIT_CODE = C.UNIT_CODE AND A.XZ_CODE = C.XZ_CODE

        AND CONVERT(VARCHAR,B.date,23) = CONVERT(VARCHAR,C.RECEIVE_TIME,23)

        GROUP BY A.XZ_CODE,A.UNIT_CODE,A.UNIT_NAME,B.date

        ) D LEFT JOIN T_FILE_RESTORE_RECORD E ON D.XZ_CODE = E.XZ_CODE AND D.UNIT_CODE = E.UNIT_CODE

            AND CONVERT(VARCHAR,D.RECEIVE_TIME,23) = CONVERT(VARCHAR,E.RESTORE_TIME,23)

        GROUP BY D.RECEIVE_TIME,D.XZ_CODE,D.UNIT_CODE,D.UNIT_NAME,D.date

END

结果测试：

目前，针对100家单位7天的数据，可以保证大约2秒内完成查询，当然这还包含了我在后期进行数据处理的时间。

写在最后：

由于项目是使用的jdbcTemplate进行的持久层操作，所以持久层操作代码附上：

    public List<Map<String, Object>> autoTrans(Map<String, Object> param) {

        String sql = "{call AUTOTRANS(?,?,?)} ";  //调用存储过程

        List<Map<String, Object>> resultList = jdbcTemplate.execute(

        //定义传入参数，返回statement

                (connection)->{

                 CallableStatement statement = connection.prepareCall(sql);

　　　　　　　　　　statement.setString(1,param.get("xzCode").toString());

　　　　　　　　　　statement.setString(2,param.get("days").toString());

　　　　　　　　　　statement.setString(3,param.get("industryCode").toString());

                        return statement;

                },

         //执行存储过程，拿到结果集，处理结果集。因为这里lambda表达式如果不设置参数类型会导致重复，就设置了参数类型

                (CallableStatement callableStatement) -> {

                        List<Map<String,Object>> resultListParam =new ArrayList<>();

                        callableStatement.execute();

                        ResultSet resultSet = callableStatement.getResultSet();

                        while (resultSet.next()){

                            Map<String,Object> result = new HashMap<>();                         
　　　　　　　　　　　　　　　　result.put("xzCode",resultSet.getString("XZ_CODE"));                         
　　　　　　　　　　　　　　　　result.put("unitCode",resultSet.getString("UNIT_CODE"));                       
　　　　　　　　　　　　　　　　result.put("unitName",resultSet.getString("UNIT_NAME"));                        
　　　　　　　　　　　　　　　　result.put("receiveTime",resultSet.getString("RECEIVE_TIME"));                        
　　　　　　　　　　　　　　　　result.put("restoreTime",resultSet.getString("RESTORE_TIME"));                       
　　　　　　　　　　　　　　　　result.put("selectDate",resultSet.getDate("SELECT_DATE"));

                            resultListParam.add(result);

                        }

                        return resultListParam;

                });

        return resultList;

    }

到此结束，本次查询优化完成，基本达到目标要求。

巴特西

SqlServer高级特性--存储过程

需求：

分析：

代码：

结果测试：

写在最后：

最新文章

热门文章