一次成功的sql优化，2个表joinI/O 极大，大约9million

原始sql：

更新#result表，更新warr_bal.I/O大的原因是CIS..trans_acd_bal数据量太大，

并且#result表的数据量并不大，且如果有重复的数据，sum出来的结果呈倍数增长。

	CREATE TABLE #scm(
		scm_no int NULL
		,amt money NULL
		)

	INSERT INTO #scm
	SELECT scm_no
		,sum(isnull(gl_amt, 0))
	FROM #result t
	INNER JOIN CIS..trans_acd_bal ta
		ON ta.gl_project = t.scm_no
	WHERE ta.gl_year = @pre_year
		AND ta.gl_month = @pre_month
	GROUP BY scm_no

	UPDATE #result
	SET warr_bal = scm.amt
	FROM #result t
	INNER JOIN #scm scm
		ON t.scm_no = scm.scm_no

修改后的sql如下：I/O小了，并且时间也减少。

循环的时候最重要的是红色的sql语句

	-----------update trans_bal from CIS..trans_acd_bal begin
	DECLARE @max_idn int
		,@min_idn int
		,@bal_amt money
		,@scm_no int

	SELECT @min_idn = isnull(min(idn), 0)
	FROM #result

	SELECT @max_idn = isnull(max(idn), 0) + 1
	FROM #result

	WHILE (@min_idn < @max_idn)
	BEGIN
		SELECT @scm_no = isnull(scm_no, 0)
			,@bal_amt = warr_bal
		FROM #result t
		WHERE t.idn = @min_idn

		IF (@bal_amt IS NULL)
		BEGIN
			SELECT @bal_amt = sum(gl_amt)
			FROM CIS..trans_acd_bal ta
			WHERE ta.gl_year = @pre_year
				AND ta.gl_month = @pre_month
				AND ta.gl_project = @scm_no

			UPDATE #result
			SET warr_bal = @bal_amt
			FROM #result t
			WHERE t.idn = @min_idn
				OR t.scm_no = @scm_no
		END

		SELECT @min_idn = min(idn)
		FROM #result
		WHERE idn > @min_idn
	END

	-----------update trans_bal from CIS..trans_acd_bal end

第二个例子：

优化前sql,2个表直接inner join I/O 异常大。

journal_entry是个大表，且有索引。

 SELECT b.dept_no
            ,max(b.dept_name) AS dept_name
    FROM journal_entry a
    INNER JOIN department_info b
            ON a.gl_department = b.dept_no
    WHERE a.gl_acct_no > 700000
    GROUP BY b.dept_no

优化后i/O降低了大概只有原来1/10的I/O，

 SELECT a.gl_department as dept_no
            ,(select max(b.dept_name) from department_info b where a.gl_department = b.dept_no)  AS dept_name
    FROM journal_entry a
    WHERE a.gl_acct_no > 700000
    GROUP BY a.gl_department

第三个例子，普通取模循环

declare @iloop int,@ibatch int
SET @iloop = 0

	SELECT @ibatch = (count(*) / 200000) + 1
	FROM #pay_tmp

	WHILE @iloop < @ibatch
	BEGIN
		EXEC (
				"
		UPDATE #pay_tmp
		SET payment = payment + pay_amt
		FROM #pay_tmp a1701250030(index id_1)
		INNER JOIN #temp_pay b(index id_1)
			ON a1701250030.order_no = b.order_no
				AND a1701250030.order_type = b.order_type
		WHERE