SQL Server advanced Features--stored procedures

Demand:

The user needs to submit the encrypted data, after the commit needs to decrypt the restore, the restore has two kinds of results: success and Failure!

100 users, before 7 days, to determine whether there is committed data, if there is data submitted, to determine whether the decryption succeeds (there are two tables respectively). If no commit, show yellow, commit, not decrypt succeeded, red, commit and decrypt succeeded, green, and show the corresponding time.

Analysis:

The test traverses all units, then iterates through each day, queries the commit data, and then queries the decrypted data. Results a 100 units, unexpectedly need about 2-3 minutes of time to inquire. (The data received in the record about 50w, later may be more), obviously inappropriate, although the cache, but does not meet the requirements.

Optimization ideas:

1. I want to use 100 units and 7 days at the same time to correlate to receive the record table, to query. That is, the multi-conditional left OUTER join query.

2. I want to get 100 home units and 7 days of such a table, need 100 home units and 7 dates of the Cartesian product

3. I need a table with a 7 day date to create a temporary table in the stored procedure.

The whole idea is this.

Code:

The overall stored procedure code is as follows:

ALTER PROCEDURE [dbo].[Autotrans]    @xz   VARCHAR(Ten)='410000' ,    @day INT =7,    @industryCode varchar(Ten)='6001' asBEGIN    SETNOCOUNT on; --define a variable: a temporary table where the user stores 7 days of data.     DECLARE @days Table(date date)DECLARE @todayDate= GETDATE()    --define a loop to insert data from the previous 7 days, starting today     while @day >= 0    BEGIN        Insert  into @days        Select DATEadd(DD,-@day,@today ); Set @day =@day-1            END    --Business Area        SELECTD.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 (        SELECTA.xz_code,a.unit_code,a.unit_name,MAX(C.receive_time) Receive_time,b.date from         (SELECTXz_code,unit_code,unit_name fromT_unit_auditWHEREXz_code= @xz  andIndustry_code= @industryCode) A Cross JOIN @daysB Left JOINT_file_receive_record C onA.unit_code=C.unit_code andA.xz_code=C.xz_code and CONVERT(VARCHAR, B.date, at)= CONVERT(VARCHAR, C.receive_time, at)        GROUP  bya.xz_code,a.unit_code,a.unit_name,b.date) D Left JOINT_file_restore_record E onD.xz_code=E.xz_code andD.unit_code=E.unit_code and CONVERT(VARCHAR, D.receive_time, at)= CONVERT(VARCHAR, E.restore_time, at)        GROUP  byd.receive_time,d.xz_code,d.unit_code,d.unit_name,d.dateEND

Results test:

At present, for 100 units of 7 days of data, can be guaranteed to complete the query in about 2 seconds, of course, this also contains my data in the late processing time.

Written at the end:

Because the project is a persistent layer operation using the JdbcTemplate, the persistence layer operation code is attached:

     PublicList<map<string, object>> Autotrans (map<string, object>param) {String SQL= "{call Autotrans (?,?,?)}";//call a stored procedurelist<map<string, object>> resultlist =Jdbctemplate.execute (//define incoming parameters, return 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 ()); returnstatement; },         //executes the stored procedure, gets the result set, and processes the result set. Because the lambda expression here causes a repetition if the parameter type is not set, the parameter type is set(CallableStatement callablestatement){List<Map<String,Object>> Resultlistparam =NewArraylist<>();                        Callablestatement.execute (); ResultSet ResultSet=Callablestatement.getresultset ();  while(Resultset.next ()) {Map<String,Object> result =NewHashmap<> ();
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); }                        returnResultlistparam;        }); returnresultlist; }       

To this end, this query optimization completed, basically meet the target requirements.

SQL Server advanced Features--stored procedures