_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › 覦貎朱Μ

Contents

1
2 Oracle vs MS-SQL
3 螳 螻 覦貎朱Μ
4 覦貎朱Μ vs 貉れ
5 蟆磯


1 #

'覦貎朱Μ'朱 蟆 れ 覺る螳? 襷 DB覦 譬 襾轟る れ企瓦 蟆企. 襷 襷 'xx 貉ろ' 襷 . 蠏 覿れ 覿覈 讌 蠍磯 襭 蠍磯 襭(貉れ)覲企 覿覿 襴一 蠍一誤 蟆狩. 譴 蟆 '覿覿 襴讌 覈 蟆曙一 襴 蟆 '朱 襷 蠏 覿れ 讌 [1] 蟆企. DBMS襷 螳 蟆郁骸朱 焔ル覿 るゼ . 覯 螳 朱襦 企 襭 襴讌 100% 蟆 襷 .

Oracle Server 螳 譬 襷 蟇一 覿覿 讌 蠍磯 襭 襴. 讌襷 MS-SQL Server 蟆曙 讌 Oracle Server 覲企 讌 覩誤″. 蠏碁蠍 覓語 MS-SQL Server 螳覲企 襷 蟆曙 蠍磯 襭 襴 蟆曙郁 襷 . 覯 蟆曙 豌襴襯 伎 蠍磯 襭螻 觜訣蟆 豌襴襯 蟆 襴 蟆曙郁 .

蠍磯 豌襴襯 ろ れ螻讌 讌 襷. 一碁慨螻 壱 谿企ゼ 企 蠏碁 '願碓 企蟆 覦蠑碁 蟆 企至 螳語?'手 覓殊企.

2 Oracle vs MS-SQL #

蠍一旧朱 Oracle 8.1.6覿 覿 襯 . 蠏碁 螳ロ 襯 襷 讌伎 Query 讌蠍郁 覿 豢朱 螳覦 覓企 譟. 蠏碁 [http]Oracle 蟲 蟆曙磯 所 觜襯願 蟲 蟆 . 讌襷 MS-SQL 蠏碁 螳 譟伎讌 SELF JOIN 伎 . 譟一 一一 <, >, <=, <= 譴 蟆企. 蠏碁覩襦 Merge Join Hash Join . る讌 Loop Join襷(SQL Server 2005覿磯 蠎 '=' 朱 Hash Join 蟆 襷 覲伎. 襷 企朱 '='襦 覦蠖 蟆 覦蠑語 Hash Join 覲企. 碁 覦 .) 蟆 伎 , 蟲 覯螳 企襦 貎朱Μ 豌襴觜 X2 蠏碁襦 企 蟆 覲 . 襯 れ れ螻 螳 蟲 貎朱Μ 一危郁 襷朱 襷襦 焔レ 蠍蟆 觜讌.
with temp
as
(
	select 1 num union all
	select 2 union all
	select 3
)
select 
	b.num
,	sum(a.num) 
from temp a
	inner join temp b
		on a.num <= b.num
group by
	b.num


蠏碁 企 蟆曙磯 貉れ襯 襭 豢豌螻 . 誤 伎 [http]Inside Microsoft SQL Server 2005 T-SQL PROGARMMING 3 貉れ覿覿 谿瑚蠍 覦.

3 螳 螻 覦貎朱Μ #

覦貎朱Μ 豢覿 螳 襴. 襯 れ, 2008 覲 襷れ♀骸 蠏螳螻螳 炎骸 螳 讌螻 貎朱Μ襯 燕り 螳 覲伎. 貎朱Μ螳 覃覈襴 1GB. 讌襷 覯 伎 譴 覃覈襴 500MB. 蠏碁覃 DBMS 企至 伎 螳? 蠏碁Μ螻 OS? 覃覈襴螳 覿譟燕り 豌 貎朱Μ襯 伎 碁企. 蠏碁 伎 殊企螻 ろ 覃覈襴襯 螳ろ覃伎 貎朱Μ 焔レ 觜讌螻 一危磯ゼ ろ 覃覈襴襦 襴蠍 伎 ろ襯 蟆 ろ 覲覈 覦 讌螳 . (覓朱 伎 殊企 ろ 一危磯伎 ろ 一危壱 磯 覿襴 蟆 蠍磯蓋企.) 伎 覦貎朱Μ 豢覿 螳 豢覿 焔レ . 蠎 覦貎朱Μ螳 譬 蟆襷 . 螳 襷 覦貎朱Μ螳 讌 覦貎朱Μ.

4 覦貎朱Μ vs 貉れ #

覦貎朱Μ

DECLARE 
    @DateUnit char(1) --D, W, M
,    @CurrDT char(8)
,    @BeginDT char(8)
,    @EndDT char(8);

SET @DateUnit = 'D';
SET @CurrDT = '20091101';

DECLARE 
    @BeginDT char(8)
,    @EndDT char(8);

SET @BeginDT = 
        CASE
            WHEN @DateUnit = 'D' THEN @CurrDT 
            WHEN @DateUnit = 'W' THEN 
                CONVERT(int, CONVERT(char(8), DATEADD(dd, -(DATEPART(weekday, @CurrDT) - 1) , @CurrDT), 112))
            WHEN @DateUnit = 'M' THEN CONVERT(int, CONVERT(char(6), @CurrDT, 112) + '01')
        END    

SET @EndDT = 
        CASE
            WHEN @DateUnit = 'D' THEN @CurrDT 
            WHEN @DateUnit = 'W' THEN 
                CONVERT(int, CONVERT(char(8), DATEADD(dd, 7- DATEPART(weekday, @CurrDT), @CurrDT), 112)) 
            WHEN @DateUnit = 'M' THEN 
                CONVERT(int, CONVERT(char(8), DATEADD(mm, 1, CONVERT(char(6), @CurrDT, 112) + '01') - 1, 112))
        END;
--SELECT @BeginDT, @EndDT;
INSERT Common.Agg_ReiterationUU_ConnectHistory
    (DateKey, BasisServiceKey, ReiterationServiceKey, UU, AggUnit)
SELECT
    @BeginDT DateKey
,    B.BasisServiceKey
,    B.ReiterationServiceKey
,    B.UU
,    @DateUnit AggUnit
FROM Common.Dim_Service OutTable
    CROSS APPLY (
        SELECT
            A.ServiceKey BasisServiceKey
        ,    B.ServiceKey ReiterationServiceKey
        ,    COUNT(DISTINCT B.AccountKey) UU
        FROM 
            (
                SELECT
                    AccountKey
                ,    OutTable.Service_CD ServiceKey
                FROM Common.Fact_ConnectHistory
                WHERE DateKey BETWEEN @BeginDT AND @EndDT
                AND ServiceKey = OutTable.Service_CD
                GROUP BY
                    AccountKey
            ) A
            INNER JOIN Common.Fact_ConnectHistory B
                ON A.AccountKey = B.AccountKey
            WHERE B.DateKey BETWEEN @BeginDT AND @EndDT
        GROUP BY
            A.ServiceKey 
        ,    B.ServiceKey 
    ) B
WHERE Service_CD > 0
AND Site_CD = 1
AND @DateUnit IN ('D', 'W', 'M')

貉れ
/*
DECLARE 
    @DateUnit char(1) --D, W, M
,    @CurrDT char(8)
,    @BeginDT char(8)
,    @EndDT char(8);

SET @DateUnit = 'D';
SET @CurrDT = '20091101';
*/
DECLARE 
    @BeginDT char(8)
,    @EndDT char(8);

SET @BeginDT = 
        CASE
            WHEN @DateUnit = 'D' THEN @CurrDT 
            WHEN @DateUnit = 'W' THEN 
                CONVERT(int, CONVERT(char(8), DATEADD(dd, -(DATEPART(weekday, @CurrDT) - 1) , @CurrDT), 112))
            WHEN @DateUnit = 'M' THEN CONVERT(int, CONVERT(char(6), @CurrDT, 112) + '01')
        END    

SET @EndDT = 
        CASE
            WHEN @DateUnit = 'D' THEN @CurrDT 
            WHEN @DateUnit = 'W' THEN 
                CONVERT(int, CONVERT(char(8), DATEADD(dd, 7- DATEPART(weekday, @CurrDT), @CurrDT), 112)) 
            WHEN @DateUnit = 'M' THEN 
                CONVERT(int, CONVERT(char(8), DATEADD(mm, 1, CONVERT(char(6), @CurrDT, 112) + '01') - 1, 112))
        END;
--SELECT @BeginDT, @EndDT;

DECLARE 
    @ServiceKey int;

DECLARE cur CURSOR FOR
    SELECT Service_CD ServiceKey
    FROM Common.Dim_Service
    WHERE Service_CD > 0
    AND Site_CD = 1
    AND @DateUnit IN ('D', 'W', 'M');

CREATE TABLE #AccountKey(AccountKey bigint);

OPEN Cur;

FETCH NEXT FROM cur INTO @ServiceKey;
WHILE @@FETCH_STATUS not in (-1, -2)
BEGIN
    INSERT #AccountKey
    SELECT
        AccountKey
    FROM Common.Fact_ConnectHistory
    WHERE DateKey BETWEEN @BeginDT AND @EndDT
    AND ServiceKey = @ServiceKey
    GROUP BY
        AccountKey;

    INSERT Common.Agg_ReiterationUU_ConnectHistory
        (DateKey, BasisServiceKey, ReiterationServiceKey, UU, AggUnit)
    SELECT
        @BeginDT DateKey
    ,    @ServiceKey BasisServiceKey
    ,    A.ServiceKey ReiterationServiceKey
    ,    COUNT(DISTINCT A.AccountKey) UU
    ,    @DateUnit AggUnit
    FROM Common.Fact_ConnectHistory A
        INNER JOIN #AccountKey B
            ON A.AccountKey = B.AccountKey
    WHERE A.DateKey BETWEEN @BeginDT AND @EndDT
    GROUP BY 
        A.ServiceKey
    
    TRUNCATE TABLE #AccountKey
    FETCH NEXT FROM cur INTO @ServiceKey
END

CLOSE cur;
DEALLOCATE cur;

蟆曙 貉れ襯 伎 覿襴 豌襴 蟆 觜 覦貎朱Μ螳 碁. 螳 覃覈襴螳 企 覓伎 伎 殊企 蟆企. れ 朱襦 豌襴 譯朱襦 豌襴 谿伎企.

豌襴 豌襴覦 豌襴螳
1 覦貎朱Μ 120豐
1 貉れ 40豐
7 覦貎朱Μ 1200豐
7 貉れ 330豐
* 谿瑚: 8cpu, 8GB ram

豌襴覯螳 伎襦 覦貎朱Μ 豌襴螳 蠍壱蠍朱 企 蟆企. 覲覈蟆 覦貎朱Μ襯 讌 蟆 焔レ 譴 蟆.

5 蟆磯 #

襷 譬 蟆襷 . 覯暑 1豸旧 5豸旧朱 100レ 襯企 蟆 螳企慨. 蠏碁Μ螻 螳碁螻 讌 れ. 螳碁 100レ 1 5豸糾讌 襯 讌襷, 讌 3覯 蟇語 襯 覦 企ゼ 螳讌 蟆 谿企. 覦 貎朱Μ れ 螻ろ伎 .

  • 螳()
  • 讌覲伎(蟯襴觜)

覦 貎朱Μ 螳 覦 貎朱Μ襯 讌 襷 豢覿伎朱 譬 襭 . 覲旧″伎 讌覲伎螳 る 蠏瑚 譬 襭 .


る 蠍伎襷 襷 給. 襷讌襷 蠍蠏螳 酔給. -- achiara 2017-05-05 18:23:38
----
   [1]  企 蠍一旧 .
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:53

覈 襷 螻 企Μ 襷 .