Contents

1 Spool 螳
2 牛磯伎 覈 磯殊 Spool 蠍
3 Distinct Count
4 SQL Server Spool 譬襯
5 Spool 伎


覓語 譴..

1 Spool 螳 #

Spool 覦覲旧朱 一危磯ゼ 螳 朱 牛磯伎螳 tempdb 一危磯ゼ ロ 暑 一一 襷. 覦覲旧 所鍵 Spool 一一 覦 伎 '覦覲旧 所鍵' . 覦覲旧 所鍵 覦覲旧 螻旧(S)襯 蟇瑚 螻, 企 觚襦轟朱 伎伎 煙 企襴蠍 覓語企. 煙 伎る 蟆 焔レ 觜讌る 蟆 覩誤. 讀, 觚襦 磯曙 蟇碁Μ蟆 れ 蠍壱蟆 襷. 觚襦 豌願 蟆 レ螳 蟇語 觚襦轟 覦覃 襷 蠍壱蟆 . 豕 蟆曙磯 觚襦轟 襴覃伎 る朱 ろ 覲覈 覦蟆 . 覦覲旧 所鍵 朱Μ企讌 覓朱Μ企讌 る I/O襯 覦蟆 ろ 觜蟆 .

朱朱 覲 Spool一一 Table Spool螻 Index Spool企. 螳讌 襦 SUM()螻 螳 るジ 讌螻 COUNT(DISTINCT ) 螳 覃 覦 Spool 覲 . SQL Server Spool 覦蟆 覃 tempdb襯 蟆 . ( 螳語 螳朱 Memory Distinct 一一 讌 tempdb襯 伎讌 旧 朱 覦企. COUNT(DISTINCT ColumnName) 焔レ 覓 蠑語.)

一危磯伎る 螻旧朱 蟆願鍵 覓語 Spool 煙 伎 覦 . Spool 牛磯伎螳 伎朱 殊 'る'企, '螳誤'手 螳覃 . Spool 豌企 I/O螳 襷 覦 一一企襦 OLTPろ Spool豌願 蟆 SQL覓語 燕 蟆 譬.

2 牛磯伎 覈 磯殊 Spool 蠍 #

る狩伎 蟆曙 觜 蠍磯 牛磯伎 覈(螳 蠍磯 Oracle 10g覯蟾讌 企)螳 3螳讌螳 . (3螳讌語 2螳讌手 伎 讌 覈襯願)
  • FIRST_ROWS
  • FIRST_ROWS_n
  • ALL_ROWS
FIRST_ROWS 豌 1 企殊伎誤語 觜襴 覦 ろ螻 語磯 覦願, FIRST_ROWS_n 豌 n 企殊伎誤語 觜襴 覦 ろ螻 語磯 覦企. ALL_ROWS 豌 蟆郁骸讌 豕襯 . (PL/SQL襦 焔 襦 牛磯伎 覈螳 企 蟆企 ALL_ROWS 覈襦 蟆朱 螻 ) SQL Server 企 牛磯伎 覈襯 讌襷 碁ゼ 牛 企ゼ . 覓語 蟆 讌襷 螳 ろ 牛 覲 覦襦 SQL Server 蠍磯蓋朱 ALL_ROWS 覈豌 豌 蟆郁骸讌 豕襯 . れ UPDATE 覓語 覲伎.
UPDATE a
SET
	a.AccountKey = b.AccountKey
,	a.BirthDT7 = b.BirthDT7
FROM dbo.Character a
	OUTER APPLY (
		SELECT TOP 1 AccountKey, BirthDT7
		FROM dbo.Dim_Customer 
		WHERE a.AccountID = AccountID
	) b
WHERE a.BirthDT7 IS NULL

UPDATE覓語 れ螻 螳 ろ. 譴螳 覲企 Index Spool .
spool01.jpg

ろ螻 豌 蟆郁骸讌 觜襴 覦襦 豕 螻企. 襷 旧螳(企殊伎誤語 豌 豌 覦 螳) 觜襯願 螻 る れ螻 螳 OPTION(FAST 1)襯 譯手碓 FastFirstRow(願唄 譴 伎り ) 碁ゼ 牛磯伎 覲蟆曙 螳 .

UPDATE a
SET
	a.AccountKey = b.AccountKey
,	a.BirthDT7 = b.BirthDT7
FROM dbo.Character a
	OUTER APPLY (
		SELECT TOP 1 AccountKey, BirthDT7
		FROM dbo.Dim_Customer 
		WHERE a.AccountID = AccountID
	) b
WHERE a.BirthDT7 IS NULL
OPTION(FAST 1) --瑚 豢螳!!!

牛磯伎 覲蟆 れ れ ろ螻豌 Index Spool 伎 蟆 覲 . FIRST_ROWS_n 企 OPTION(FAST 100)螻 螳 企企 . Index Spool 蠍磯讌 蠍一 讌 糾覲伎 蠍磯 觜 れ朱襦 蟆曙 磯 殊. 豌伎襴 觜 譴讌 旧螳 譴讌 螳讌 蟆曙 磯 るゴ. 覓朱 牛磯伎螳 100% 覦襯 企れ殊 朱襦 螳讌 襴れ ろ碁ゼ 企慨 蟆 譴. (1螳 覓語 1螳 襭襷 譟伎 蟆 讌 襷.)
spool02.jpg

3 Distinct Count #

企蟆 覃 spool 蠍一 .

select
    date_key
,   count(distinct id)
--,   sum(amt)
from table_a
group by
    date_Key

讌襷, count(distinct ..) るジ 讌螻螳 企 spool 蠍企.

select
    date_key
,   count(distinct id)
,   sum(amt)
from table_a
group by
    date_Key

覃覈 豌襴覃 譬蟆, 企ゼ CLR襦 蟲 覲 蟆 . --> DistinctCount 蟇 谿瑚

[http](http://www.sqlservercentral.com/Forums/Topic873124-338-5.aspx#bm1013407)襯 覲企 CTE襯 伎 襭 distinct group by覲企 觜 蟆 蟆 覲 . CTE螳 覲旧″ 觜 . 讌蟯伎 讌襷, 讒譴.

DROP    TABLE #test;
GO
CREATE  TABLE 
        #test 
        (
        data            INTEGER NOT NULL,
        );
GO
CREATE  CLUSTERED INDEX c ON #test (data);
GO
-- Lots of duplicated values
INSERT  #test WITH (TABLOCK)
        (data)
SELECT  TOP (5000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 117329
FROM    master.sys.columns C1,
        master.sys.columns C2,
        master.sys.columns C3;
GO





SET     STATISTICS TIME ON;

-- 1591ms CPU
SELECT  DISTINCT 
        data
FROM    #test;


-- 15ms CPU
WITH    RecursiveCTE
AS      (
        SELECT  data = MIN(T.data)
        FROM    #test T
        UNION   ALL
        SELECT  R.data
        FROM    (
                -- A cunning way to use TOP in the recursive part of a CTE :)
                SELECT  T.data,
                        rn = ROW_NUMBER() OVER (ORDER BY T.data)
                FROM    #test T
                JOIN    RecursiveCTE R
                        ON  R.data < T.data
                ) R
        WHERE   R.rn = 1
        )
SELECT  *
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0);

SET     STATISTICS TIME OFF;
GO
DROP    TABLE #test;

4 SQL Server Spool 譬襯 #

Table Spool (Physical Operator)
Noclustered Index Spool (Physical Operator)
Eager Spool (Logical Operator)
Lazy Spool (Logical Operator)
Row count Spool (Physical Operator)


5 Spool 伎 #


spool企 螻襯旧 ..
tempdb 郁, 所鍵襯 觜 蟒 襷 り鍵 讌襷..
覦覲旧 所鍵螳 覦 蟆曙磯 煙 レ 朱襦 spool ..
讌襷 企 覃伎煙 伎螻(OLTP蟆)襷 覦る朱 蟆伎..
覲願(DW) spool 蟾??

test

譬 覲 螳. -- 曙 2020-04-14 10:06:52