SqlServer進階特性--預存程序

標籤：預存程序   add   class   code   變數   receive   temp   end   var   

需求：

使用者需要提交加密資料，提交之後需要解密還原，還原有兩種結果：成功和失敗！

100個使用者，之前7天，判斷是否有提交資料，如果有提交有資料，判斷是否解密成功（分別存在兩個表中）。如果沒有提交，顯示黃色；提交，未解密成功，紅色；提交並解密成功，綠色，並顯示對應時間。

分析：

測試過遍曆所有單位，之後遍曆每一天，去查詢提交資料，然後在去查詢解密資料。結果一個100家單位，竟然需要大概2-3分鐘的時間來查詢。（接收記錄裡面資料大約50w條，後期可能會更多），明顯不合適，雖然加過緩衝，但是不符合要求。

最佳化思路：

　　1. 我想用100家單位和7天同時去關聯結收記錄表，進行查詢。也就是多條件左外串連查詢。

　　2. 我要擷取100家單位和7天的這樣的表，需要100家單位和7個日期的笛卡爾積

　　3. 我需要一個7天日期的表，可以在預存程序中進行建立暫存資料表。

整體思路就是這樣的。

代碼：

整體預存程序代碼如下：

ALTER PROCEDURE [dbo].[AUTOTRANS]    @xz   VARCHAR(10)=‘410000‘ ,    @day INT =7,    @industryCode varchar(10)=‘6001‘ASBEGIN    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.dateEND

 結果測試：

目前，針對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進階特性--預存程序