走向DBA[MSSQL篇] 针对大表 设计高效的存储过程

      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