if @GisNum <>'' begin set @RowInfo = @RowInfo+'F_TGSM' set @Content =@Content+@GisNum end
if @RType <>'' begin set @RowInfo = @RowInfo+', R_Type' set @Content =@Content+','+CHAR(39)+ @RType+CHAR(39) end
if @STIME <>'' begin set @RowInfo = @RowInfo+', S_TIME' set @Content =@Content+','+@STIME end
if @SLat <>'' begin set @RowInfo = @RowInfo+', S_Lat' set @Content =@Content+','+@SLat end if @SLon <>'' begin set @RowInfo = @RowInfo+', S_Lon' set @Content =@Content+','+@SLon end if @SDirection <>'' begin set @RowInfo = @RowInfo+', S_Direction' set @Content =@Content+','+ @SDirection end
if @SWeek <>'' begin set @RowInfo = @RowInfo+', S_Week' set @Content =@Content+','+@SWeek end if @SSpeed <>'' begin set @RowInfo = @RowInfo+', S_Speed' set @Content =@Content+','+@SSpeed end if @SHei <>'' begin set @RowInfo = @RowInfo+', S_Hei' set @Content =@Content+','+@SHei end if @SFlags <>'' begin set @RowInfo = @RowInfo+', F_S_Flags' set @Content =@Content+','+ @SFlags end if @SS <>'' begin set @RowInfo = @RowInfo+' , S_S' set @Content =@Content+','+@SS end if @AFlags <>'' begin set @RowInfo = @RowInfo+', F_A_Flags' set @Content =@Content+','+ @AFlags end
if @SA <>'' begin set @RowInfo = @RowInfo+', S_A' set @Content =@Content+','+@SA end if @SREV <>'' begin set @RowInfo = @RowInfo+', S_REV' set @Content =@Content+','+@SREV end if @FNUM <>'' begin set @RowInfo = @RowInfo+', F_NUM' set @Content =@Content+','+CHAR(39)+ @FNUM+CHAR(39) end
select @F_Ter_Car_No = F_Car_Number,@F_OIL =F_Car_Oil, @F_vclen =F_Car_Type_ID,@F_TDW = F_Company_ID ,@F_Car_Type_Name= F_Car_Type ,@F_Company_Name= F_Company_Name, @F_ID= F_Terminal_ID ,@Terminal_Num=F_Terminal_Num, @F_TName=F_Terminal_Type_ID,@F_Terminal_Name = F_Terminal_Factory from Car_Static_Info where F_Terminal_SIM_Num= @GisNumif @F_ID <>'' begin set @RowInfo = @RowInfo+', F_ID' set @Content =@Content+','+@F_ID end
if @F_TName <>'' begin set @RowInfo = @RowInfo+', F_TName' set @Content =@Content+','+@F_TName end
if @F_Terminal_Name <>'' begin set @RowInfo = @RowInfo+', F_Terminal_Factory_Name' set @Content =@Content+','+CHAR(39)+@F_Terminal_Name+CHAR(39) end if @F_Company_Name <>'' begin set @RowInfo = @RowInfo+', F_Company_Name' set @Content =@Content+','+CHAR(39)+@F_Company_Name+CHAR(39) endif @F_Car_Type_Name <>'' begin set @RowInfo = @RowInfo+', F_Car_Type_Name' set @Content =@Content+','+CHAR(39)+@F_Car_Type_Name+CHAR(39) endif @F_TDW <>'' begin set @RowInfo = @RowInfo+', F_TDW' set @Content =@Content+','+@F_TDW end
if @F_vclen <>''begin set @RowInfo = @RowInfo+', F_vclen' set @Content =@Content+','+@F_vclen end if @F_Ter_Car_No <>'' begin set @RowInfo = @RowInfo+', F_Ter_Car_No' set @Content =@Content+','+@F_Ter_Car_No end if @F_OIL <>'' begin set @RowInfo = @RowInfo+', F_OIL' set @Content =@Content+','+@F_OIL end
if @FDATA <>'' begin set @RowInfo = @RowInfo+', F_DATA' set @Content =@Content+','+CHAR(39)+ @FDATA+CHAR(39) end set @RUN_EXEC ='Insert into [Gps_State ] ('+ @RowInfo+ ') values (' + @Content + ')' exec(@RUN_EXEC) set nocount OFF GO
1.recordset addnew() updatebatch()
2.存储过程
3.批量导入
1,添加记录集速度很快,最后update相当于发送多条sql,速度不会快于10楼
2,存储过程,个人理解:相当于已经准备好了存储过程,一次解析终生适用,速度不会快于10楼
3,只有这个,有可能超过10楼,毕竟insert 100次和insert 100条记录一次速度不同吧
用记录集open,获得该表的数据
然后反复用filter过滤记录集避免频繁访问数据库
那就只open一次,在循环外面filter在循环内用来过滤
相当于把整个表放到了内存中
在内存中查找当然比那样快
@STIME varchar(50),
@SLat varchar(50),
@SLon varchar(50),
@SDirection varchar(50),
@SWeek varchar(50),
@SSpeed varchar(50),
@SHei varchar(50),
@SFlags varchar(50),
@SS varchar(50),
@AFlags varchar(50),
@SA varchar(50),
@FTID varchar(50),
@GisNum varchar(50), @SREV varchar(50),
@FNUM varchar(50),
@FDATA varchar(50)
as
set nocount ON
DECLARE @Terminal_Num varchar(50)DECLARE @F_ID varchar(50)
DECLARE @F_TName varchar(50)
DECLARE @F_Terminal_Name varchar(50)
DECLARE @F_TDW varchar(50)
DECLARE @F_Company_Name varchar(50)
DECLARE @F_vclen varchar(50)
DECLARE @F_Car_Type_Name varchar(50)
DECLARE @F_Ter_Car_No varchar(50)
DECLARE @F_OIL varchar(50)
--
DECLARE @F_Driver_Name varchar(50)
DECLARE @F_Work_Num varchar(50)
DECLARE @F_DIS varchar(50)
DECLARE @F_TEMP varchar(50)
DECLARE @F_WorkID varchar(50)
DECLARE @F_SURESTATE varchar(50)
DECLARE @S_STATE varchar(50) DECLARE @RowInfo varchar(2048)
DECLARE @Content varchar(2048)
DECLARE @RUN_EXEC varchar(4056)
set @RowInfo =''
set @Content=''--下面是判断的,都是检测条件是否为空,如果为空的话,就默认为NULL 否则输入值
if @FTID <>''
begin
set @RowInfo = @RowInfo+'F_TID,'
set @Content =@Content+@FTID+','
end
if @GisNum <>''
begin
set @RowInfo = @RowInfo+'F_TGSM'
set @Content =@Content+@GisNum
end
if @RType <>''
begin
set @RowInfo = @RowInfo+', R_Type'
set @Content =@Content+','+CHAR(39)+ @RType+CHAR(39)
end
if @STIME <>''
begin
set @RowInfo = @RowInfo+', S_TIME'
set @Content =@Content+','+@STIME
end
if @SLat <>''
begin
set @RowInfo = @RowInfo+', S_Lat'
set @Content =@Content+','+@SLat
end
if @SLon <>''
begin
set @RowInfo = @RowInfo+', S_Lon'
set @Content =@Content+','+@SLon
end
if @SDirection <>''
begin
set @RowInfo = @RowInfo+', S_Direction'
set @Content =@Content+','+ @SDirection
end
if @SWeek <>''
begin
set @RowInfo = @RowInfo+', S_Week'
set @Content =@Content+','+@SWeek
end
if @SSpeed <>''
begin
set @RowInfo = @RowInfo+', S_Speed'
set @Content =@Content+','+@SSpeed
end
if @SHei <>''
begin
set @RowInfo = @RowInfo+', S_Hei'
set @Content =@Content+','+@SHei
end
if @SFlags <>''
begin
set @RowInfo = @RowInfo+', F_S_Flags'
set @Content =@Content+','+ @SFlags
end
if @SS <>''
begin
set @RowInfo = @RowInfo+' , S_S'
set @Content =@Content+','+@SS
end
if @AFlags <>''
begin
set @RowInfo = @RowInfo+', F_A_Flags'
set @Content =@Content+','+ @AFlags
end
if @SA <>''
begin
set @RowInfo = @RowInfo+', S_A'
set @Content =@Content+','+@SA
end
if @SREV <>''
begin
set @RowInfo = @RowInfo+', S_REV'
set @Content =@Content+','+@SREV
end
if @FNUM <>''
begin
set @RowInfo = @RowInfo+', F_NUM'
set @Content =@Content+','+CHAR(39)+ @FNUM+CHAR(39)
end
select @F_Ter_Car_No = F_Car_Number,@F_OIL =F_Car_Oil, @F_vclen =F_Car_Type_ID,@F_TDW = F_Company_ID ,@F_Car_Type_Name= F_Car_Type ,@F_Company_Name= F_Company_Name, @F_ID= F_Terminal_ID ,@Terminal_Num=F_Terminal_Num, @F_TName=F_Terminal_Type_ID,@F_Terminal_Name = F_Terminal_Factory from Car_Static_Info where F_Terminal_SIM_Num= @GisNumif @F_ID <>''
begin
set @RowInfo = @RowInfo+', F_ID'
set @Content =@Content+','+@F_ID
end
if @F_TName <>''
begin
set @RowInfo = @RowInfo+', F_TName'
set @Content =@Content+','+@F_TName
end
if @F_Terminal_Name <>''
begin
set @RowInfo = @RowInfo+', F_Terminal_Factory_Name'
set @Content =@Content+','+CHAR(39)+@F_Terminal_Name+CHAR(39)
end
if @F_Company_Name <>''
begin
set @RowInfo = @RowInfo+', F_Company_Name'
set @Content =@Content+','+CHAR(39)+@F_Company_Name+CHAR(39)
endif @F_Car_Type_Name <>''
begin
set @RowInfo = @RowInfo+', F_Car_Type_Name'
set @Content =@Content+','+CHAR(39)+@F_Car_Type_Name+CHAR(39)
endif @F_TDW <>''
begin
set @RowInfo = @RowInfo+', F_TDW'
set @Content =@Content+','+@F_TDW
end
if @F_vclen <>''begin
set @RowInfo = @RowInfo+', F_vclen'
set @Content =@Content+','+@F_vclen
end
if @F_Ter_Car_No <>''
begin
set @RowInfo = @RowInfo+', F_Ter_Car_No'
set @Content =@Content+','+@F_Ter_Car_No
end
if @F_OIL <>''
begin
set @RowInfo = @RowInfo+', F_OIL'
set @Content =@Content+','+@F_OIL
end
if @FDATA <>''
begin
set @RowInfo = @RowInfo+', F_DATA'
set @Content =@Content+','+CHAR(39)+ @FDATA+CHAR(39)
end
set @RUN_EXEC ='Insert into [Gps_State ] ('+ @RowInfo+ ') values (' + @Content + ')'
exec(@RUN_EXEC) set nocount OFF
GO
条,用了我接近5秒的时间。