/* TRUNCATE TABLE dbo.temp_new_skc; insert into dbo.temp_new_skc select A.SkuCode,a.Quantity, a.C_date from OmsSale a WHERE CONVERT(varchar(100), C_date, 23) >= dateadd(YY,1,@B_time) AND CONVERT(varchar(100), C_date, 23)<= dateadd(YY,1,@E_time) and not exists (select 1 from OmsSale b WHERE CONVERT(varchar(100), C_date, 23) >= @B_time AND CONVERT(varchar(100), C_date, 23)<= @E_time and a.SkuCode = b.SkuCode) and a.StoreName not like '%唯品会%'; --预测要排除唯品会的 wj 9-19 */ if(object_id('temp_new_skc','U')) is not null drop table temp_new_skc; select A.SkuCode,a.Quantity, a.C_date into temp_new_skc from OmsSale a WHERE CONVERT(varchar(100), C_date, 23) >= dateadd(YY,1,@B_time) AND CONVERT(varchar(100), C_date, 23)<= dateadd(YY,1,@E_time) and not exists (select 1 from OmsSale b WHERE CONVERT(varchar(100), C_date, 23) >= @B_time AND CONVERT(varchar(100), C_date, 23)<= @E_time and a.SkuCode = b.SkuCode) and a.StoreName not like '%唯品会%' ;
1、select ino(要求 表不存在) 比 insert into select(要求表存在) 快多了。看下图时间对比
2、truncate 比delete 快多了
3、复合索引 列顺序 必须和 where列顺序一致
4、就算建了索引 效率也不一定快
5、查询的消耗应该没有insert来的大,百万级的insert耗时近1分钟