走向DBA[MSSQL篇] 针对大表 设计高效的存储过程【原理篇】 附最差性能sql语句进化过程客串

SELECT TOP 500 a.KeyName FROM bug01 a LEFT JOIN bug02 b on a.KeyName = b.KeyName 
WHERE (a.KeyName not in (select distinct b.KeyName From bug02)) 
ORDER BY a.KeyName asc

　　　　   CREATE TABLE #p
	(      
		OrderID varchar(50), 
		primary key (OrderID)      
	);
	SET @temp_text = ‘INSERT INTO #p ‘+@KeyText
	--PRINT @temp_text
	EXEC (@temp_text)	
	
	SET @KeyText = ‘SELECT OrderID FROM #p‘
	--如果一级表关联的操作次数比较多那么可以访源表操作 以临时表取代物理表
	SET @SubKeyText = ‘select 一级表_A_被关联键 From 一级表_A with(nolock) where 一级表_A_关联源表键 in (‘ + @KeyText+‘)‘
	
	CREATE TABLE #q
	(      
		OrderID varchar(50), 
		primary key (OrderID)      
	);
	SET @temp_text = ‘INSERT INTO #q ‘+@SubKeyText
	EXEC (@temp_text)	
	SET @SubKeyText =‘SELECT OrderID FROM #q‘
	
	--如果一级表关联的操作次数不多可以直接生成数据过滤池
	SET @SubKeyTextforA =‘select 一级表_B_被二级关联键 From 一级表_B with(nolock) where 一级表_B_关联源表键 in (‘ + @KeyText+‘)‘
	SET @SubKeyTextforB =‘select 一级表_C_被二级关联键 From 一级表_C with(nolock) where 一级表_C_关联源表键 in (‘ + @KeyText+‘)‘
	
	--如果存在更多层操作在此处可以继续关联资源过滤池 Demo只做到三层
SET @THKeyTextforA =‘select 二级表_A_被三级关联键 From 二级表_A with(nolock) where 二级表_A_关联一级表键 in (‘ + @SubKeyTextforA+‘)‘

--将错误的批次订单号入异常表
	Insert into 异常表(@ExTable) SELECT OrderID FROM #p
--@ExTable用来存放异常数据 如果当期批次出错 则将本次批次订单信息入库@ExTable下一批次则过滤这些数据再执行
	SET @KeyText = ‘SELECT TOP ‘+CAST(@SynSize AS VARCHAR(10))+‘ ‘+@Base_Key+‘ FROM +

‘+@BaseTable+‘+ WHERE ‘+@Base_Key+‘ not in (select ‘+@Base_Key+‘ From ‘+@ExTable+‘) ‘ 

--一级 直接关联源表主键 或为二级被关联的主表
	INSERT INTO #k VALUES (‘一级表_A‘,@Base_Key,@KeyText,‘‘)					--一级表_A
	INSERT INTO #k VALUES (‘一级表_B‘,@Base_Key,@KeyText,‘‘)					--一级表_B
	INSERT INTO #k VALUES (‘一级表_C‘,@Base_Key,@KeyText,‘‘)					--一级表_C
--二级 规则间接关联
	--@SubKeyText相关
	INSERT INTO #k VALUES (‘二级表_A‘,‘二级表_A_关联一级键‘,@SubKeyText,‘‘)				--二级表_A
	INSERT INTO #k VALUES (‘二级表_B‘,‘二级表_B_关联一级键‘,@SubKeyText,‘‘)				--二级表_B	
	INSERT INTO #k VALUES (‘二级表_C‘,‘二级表_C_关联一级键‘,@SubKeyText,‘‘)				--二级表_C
--特殊处理 
	--自定义操作
	INSERT INTO #k VALUES (‘特殊表‘,‘特殊表关联键‘,‘自定义数据过滤方式‘,‘‘)			
	
	--其他 自增列处理
	--修改订单,及其取消修改订单状态历史表
	INSERT INTO #k VALUES (‘自增表‘,@Base_Key,@KeyText,‘自定义字段‘)

DECLARE CUR_ORDERHEDER INSENSITIVE CURSOR FOR SELECT TableName,KeyName,temptext,colname FROM #k 
	OPEN CUR_ORDERHEDER
	FETCH CUR_ORDERHEDER INTO @Cur_Table,@Cur_Key,@Cur_W,@Cur_K
		WHILE @@FETCH_STATUS = 0
			BEGIN				
				 EXECUTE P_Task_Sub_Synchronization
				 @OutParam  = @OutParam OUT, @OutMessage = @OutMessage OUT,
			@KeyText =  @Cur_W,@Table= @Cur_Table,@Extension=@Extension,@IsDelSource=@IsDelSource,@KeyName=@Cur_Key,@ColName=@Cur_K
				 --SET @OutMessage = @OutMessage+@OutMessage
				 --PRINT @OutMessage
				 IF @OutParam <> 0  
					 BEGIN
						SET @OutMessage = @OutMessage + @Cur_Table +‘操作失败‘						
						ROLLBACK TRAN
						--将错误的批次订单号入异常表
						Insert into 异常表(@ExTable) SELECT OrderID FROM #p
						DROP TABLE #k 
						DROP TABLE #p 
						DROP TABLE #q
						RETURN
					 END	
				 FETCH CUR_ORDERHEDER INTO @Cur_Table,@Cur_Key,@Cur_W,@Cur_K
			END
	ClOSE CUR_ORDERHEDER
	DEALLOCATE CUR_ORDERHEDER