最近个人做的一个项目主要是考虑大数据量，估计在不久的将来会达到亿级计算，取数据时，之前使用存储过程游标的方式插入或修改，速度很慢，经过个人修改后，速度提高了近50%，代码如下：
插入语句：
set @sql='insert drain.dbo.tOnline_InRealDataVerify(inid, Pdatetime, ph, cod, wt, inamount, ljll, phRpt, phQfd, codRpt, codQfd, wtRpt, wtQfd, inamountRpt, inamountQfd, ljllRpt, ljllQfd, 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 AN,a008 as TP,a010 as TN,a003 AS SL,a004 AS LJ,a002Rpt as PHRpt,a002Qfd as PHQfd,a001Rpt as CODRpt,a001Qfd as CODQfd,a005Rpt as SWRpt,a005Qfd as SWQfd,a009Rpt as ANRpt,a009Qfd as ANQfd,a008Rpt as TPRpt,a008Qfd as TPQfd,a010Rpt as TNRpt,a010Qfd as TNQfd,a003Rpt as SLRpt,a003Qfd 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
修改语句：
update tOnline_New_SSDatas set Std_id=Std_id2, Com_id=Com_id2,FACT_ID=FACT_ID2, JCKID=JCKID2, JCKTYPE=JCKTYPE2, Datetime=Datetime2, dsp_name=dsp_name2, JckName=JckName2, codtoctime=codtoctime2, codtocdt=codtocdt2, clr_codtocdt=clr_codtocdt2, d009=d0092, d010=d0102, d011=d0112, d012=d0122, clr_d009=clr_d0092, clr_d010=clr_d0102, clr_d011=clr_d0112, clr_d012=clr_d0122, CODTOCSTATE=CODTOCSTATE2, CODTOCSTVAL=CODTOCSTVAL2, CODTOCWARNING=CODTOCWARNING2, CODTOCWNVAL=CODTOCWNVAL2, CODTOCFAULT=CODTOCFAULT2, CODTOCFTVAL=CODTOCFTVAL2, 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 Std_id2, Com_id as Com_id2,FACT_ID as FACT_ID2, JCKID as JCKID2, JCKTYPE as JCKTYPE2, Datetime as Datetime2, dsp_name as dsp_name2, JckName as JckName2, codtoctime as codtoctime2, codtocdt as codtocdt2, clr_codtocdt as clr_codtocdt2, d009 as d0092, d010 as d0102, d011 as d0112, d012 as d0122, clr_d009 as clr_d0092, clr_d010 as clr_d0102, clr_d011 as clr_d0112, clr_d012 as clr_d0122, CODTOCSTATE as CODTOCSTATE2, CODTOCSTVAL as CODTOCSTVAL2, CODTOCWARNING as CODTOCWARNING2, CODTOCWNVAL as CODTOCWNVAL2, CODTOCFAULT as CODTOCFAULT2, CODTOCFTVAL as CODTOCFTVAL2, flag as flag2, data_dt as data_dt2, clr_datetime as clr_datetime2, a001 as a0012, a002 as 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, clr_codtocdt, d009, d010, d011, d012, clr_d009, clr_d010, clr_d011, clr_d012, CODTOCSTATE, CODTOCSTVAL, CODTOCWARNING, CODTOCWNVAL, CODTOCFAULT, CODTOCFTVAL, 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;
不过，大数据量的数据库如何优化的问题，确实令很多人都苦恼，我是碰到了！有朋友愿意交流的，大家聊聊！