SQL 优化实例

将两张表要 join 的几个字段组合成一个 varchar类型的 字符串, 放到临时表里, 临时表可以带有索引, 再去join

CREATE TABLE #Remove_Plan_Detail
		(ID BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL
		,Plan_Detail_ID BIGINT)

	CREATE INDEX ix_Plan_Detail_ID ON #Remove_Plan_Detail(
		Plan_Detail_ID ASC
	)

	CREATE TABLE #audit(
		comkey NVARCHAR(255) not null
	)
	CREATE INDEX ix_audit_comkey ON #audit(
		comkey ASC
	)
	
	CREATE TABLE #plandetail(
		comkey NVARCHAR(255) not null,
		Plan_Detail_ID BIGINT not null
	)
	CREATE INDEX ix_plandetail ON #plandetail(
		comkey ASC
	)


	--combine the column together so the join can be faster
	INSERT INTO #audit
	SELECT
	 cast(isnull(Source_ID,-1) AS NVARCHAR(10))
	+cast(isnull(PCP_ID,-1) AS NVARCHAR(10))
	+cast(isnull(PCI_ID,-1) AS NVARCHAR(10))
	+cast(isnull(SIT_ID,-1) AS NVARCHAR(10))
	+Source_Record_KEY_NAME
	FROM dbo.Plan_Detail_Audit
	
	INSERT INTO #plandetail
	SELECT 
	 cast(isnull(Source_ID,-2) AS NVARCHAR(10))
	+cast(isnull(PCP_ID,-2) AS NVARCHAR(10))
	+cast(isnull(PCI_PCI_ID,-1) AS NVARCHAR(10))
	+cast(isnull(SIT_SIT_ID,-1) AS NVARCHAR(10))
	+Source_Record_KEY_NAME
	,Plan_Detail_ID
	FROM [$(ODS_Prod)].dbo.Plan_Detail

Chunk Delete, 分块删除:

WHILE 1=1
	BEGIN
	DELETE TOP(10000) pid
	FROM [$(ODS_Prod)].dbo.Plan_Detail pid
	WHERE pid.Plan_Detail_ID IN (SELECT Plan_Detail_ID FROM #Remove_Plan_Detail)
	IF @@rowcount < 10000 BREAK;
	END