/*
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分钟