SQL instances for batch insertion and Modification

Recently, I am working on a project mainly to consider the large data volume. It is estimated that the computation will reach million in the near future. When retrieving data, I used the Stored Procedure cursor to insert or modify the data, the speed is very slow. After personal modification, the speed has increased by nearly 50%,CodeAs follows:

Insert statement:

Set @ SQL = 'insert drain. DBO. struct (inid, pdatetime, pH, cod, wt, inamount, Ljll, phrpt, phqfd, codrpt, codqfd, wtrpt, wtqfd, inamountrpt, struct, ljllrpt, struct, stateinfo, isuse) select jckid, stime, pH, cod, SW, SL, LJ, phrpt, phqfd, codrpt, codqfd, swrpt, swqfd, slrpt, slqfd, ljrpt, ljqfd, 1, 1 from (select datetime as stime, std_id as std_id, fact_id as fact_id, jcktype as jcktype, jckid as jckid, a002 as pH, a001 as cod, a005 as SW, a009 as, a008 as TP, a010 as TN, a003 as SL, a004 as LJ, a002rpt as phrpt, region as phqfd, region as codrpt, a001qfd as codqfd, a005rpt as swrpt, region as swqfd, region as anrpt, a009qfd as anqfd, region as tprpt, region as tpqfd, a010rpt as tnrpt, a010qfd as tnqfd, region as slrpt, region as slqfd, a004rpt as ljrpt, a004qfd as ljqfd from '+ @ targettable +' Where datetime >=@ flagstime and datetime <= @ flagstime2 and jcktype = 1) As AA where not exists (select * from drain. DBO. tonline_inrealdataverify as BB where AA. jckid = BB. inid and AA. stime = BB. pdatetime )'
Execute sp_executesql @ SQL, n' @ flagstime datetime, @ flagstime2 datetime ', @ flagstime, @ flagstime2

 

Modify statement:

Update into set std_id = response, com_id = response, fact_id = fact_id2, jckid = response, jcktype = jcktype2, datetime = datetime2, dsp_name = dsp_name2, jckname = jckname2, codtoctime = response, codtocdt = codtocdt2, latency = Hangzhou, d009 = d0092, d010 = d0102, d011 = d0112, d012 = d0122, latency = Hangzhou, clr_d010 = Beijing, latency = clr_d0112, cost = Beijing, codtocstate = codtocstate2, region = Hangzhou, codtocwarning = Beijing, region = Hangzhou, region = Hangzhou, flag = flag2, data_dt = data_dt2, clr_datetime = clr_datetime2, COD = a0012, pH = a0022, ssll = a0032, Ljll = a0042, Wt = a0052, YW = a0062, TOC = a0072, TP = a0082, nh3n = a0092, Tn = a0102, district = district2, info = info2 from (select std_id as Alias, com_id as com_id2, fact_id as fact_id2, jckid as Alias, jcktype as jcktype2, datetime as datetime2, dsp_name as dsp_name2, jckname as jckname2, codtoctime as hour, codtocdt as codtocdt2, encoded as hour, d009 as d0092, d010 as d0102, d011 as d0112, d012 as d0122, stored as hour, clr_d010 as hour, stored as hour, region as region, codtocstate as codtocstate2, codtocstval as region, codtocwarning as region, region as region, flag as flag2, data_dt as data_dt2, clr_datetime as clr_datetime2, a0022, a003 as a0032, a004 as a0042, a005 as a0052, a006 as a0062, a007 as a0072, a008 as a0082, a009 as a0092, a010 as a0102, district as district2, info as info2 from (select std_id, com_id, fact_id, jckid, jcktype, datetime, dsp_name, jckname, codtoctime, codtocdt, struct, d009, d010, d011, d012, response, clr_d010, response, response, codtocstate, response, codtocwarning, codtocwnval, codtocfault, response, flag, data_dt, clr_datetime, a001, a002, a003, a004, a005, a006, a007, a008, a009, a010, district, info from # mytemptable) as AA where not exists (select * From tonline_new_ssdatas as BB where AA. std_id = BB. std_id and AA. datetime = BB. datetime) as B where tonline_new_ssdatas.std_id = B. std_id2;

however, the problem of how to optimize a database with a large data volume is indeed a headache for many people. I met it! Some friends are willing to talk about it!