原作者：edobnet

項目介紹
政府一個業(yè)務(wù)系統(tǒng)，
使用范圍全國
數(shù)據(jù)是區(qū)域還存儲,最大地方,主業(yè)務(wù)表，一年有600萬,工作流表，1年有幾千萬,迄今為至系統(tǒng)已經(jīng)運行三年。
其它全國還有46個地點,數(shù)據(jù)也比較多
新開發(fā)系統(tǒng)主要功能,對業(yè)務(wù)系統(tǒng)的數(shù)據(jù)進(jìn)行分析統(tǒng)計.

所以對性能要求比較高。

我們的簡要方案。

建立中間表，通過DTS調(diào)度每天共步數(shù)據(jù)。
中間表設(shè)計原則
 記錄數(shù)同原表一樣，減少多表連接，保存運算好的值，如果記錄修改，根據(jù)修改日志，重新計算中間值


增量同步數(shù)據(jù)(DTS)
 直接從每天的數(shù)據(jù)庫更改日志讀取記錄，更新中間表，根據(jù)服務(wù)器空間程度合理調(diào)度DTS，減少數(shù)據(jù)同步時間。

對中間數(shù)據(jù)進(jìn)行運算
 查詢不作字段運行，所以運算在生成中間表的過程中已經(jīng)計算

根據(jù)查詢,優(yōu)化索引設(shè)計
 根據(jù)數(shù)據(jù)查詢特性，對where ，GROUP BY等操作字段進(jìn)行索引設(shè)計，提高查詢速度 

 優(yōu)化數(shù)據(jù)類型
 大量采用Int提高查詢、統(tǒng)計速度 

 優(yōu)化中間表關(guān)鍵字
 采用Int，提高插入速度


數(shù)據(jù)文件優(yōu)化設(shè)計，一個主要業(yè)務(wù)，一個數(shù)據(jù)文件，建數(shù)據(jù)文件時，估計數(shù)據(jù)量，一次建一個比較大的文件，這樣所分配的文件就是一個連續(xù)文件塊，


sql server設(shè)置區(qū)別大小寫。初始內(nèi)存調(diào)到一個比較大的內(nèi)存。

使用我們的Toolkit開發(fā)簡單分頁，相關(guān)壓力測試，
 測試服務(wù)器配制
 2個至強3.0CPU
 2G內(nèi)存
 150G硬盤
 Window 2000 Advance Server中文版+SP4
 測試數(shù)據(jù)ENTRY_WORKFLOW表，數(shù)據(jù)量2,473,437

 頁數(shù)  界面顯示時間  CPU  Reads I/O  Writes I/O  Duration
 第1頁  2-3 S  642  10689  0 390
 第100頁  3-4S  626  128001  0 423
  
 ....后頁業(yè)數(shù)太多，沒有必要

 壓力測試

 并發(fā)數(shù) 平均每秒請求數(shù) 未字節(jié)響應(yīng)毫秒數(shù)
 50 45.28 20,095.65
 25 45.41 10,043.12

索引優(yōu)化測試， 

 對分量值小的數(shù)據(jù)建索引測試，測試語句，GROUP BY 分量值
  一個字段,大概有6個分量值,沒有建索引,4S,建索引<1s
 兩個分量,不建索引,3S,建索引,<1S
一般來說，對分量小的字段，不建索引，但是我們對性能要求太高，根據(jù)我們的測試，數(shù)據(jù)對分量范圍小的也要建索引。

因為一個統(tǒng)計，有一個很多組合的WHERE，比如有十個指標(biāo)這樣會有十次訪問原表，這樣性能太低，所以我們把where后的數(shù)據(jù)作



用中間數(shù)據(jù)，
十個指標(biāo)對中間數(shù)據(jù)作查詢，中間數(shù)據(jù)，我們使用臨時表，
經(jīng)測試，10萬條記錄，插入操作，臨時表需要，16s,表變量需要，40S




select ... into #temp from .......
速度極快，2，500，000條記錄，16S

一個存儲過程樣例，有興趣可以分析一下！

  1SET QUOTED_IDENTIFIER ON 
  2GO
  3SET ANSI_NULLS ON 
  4GO
  5
  6
  7
  8
  9
 10ALTER     Procedure sp_tg009
 11    /**//* Param List */
 12    @TE_I_E_FLAG varchar(4),/**//*進(jìn)出口方式*/
 13    @TE_PASS_RANGE varchar(4),/**//*關(guān)區(qū)范圍*/
 14    @TE_C_OUTPUT varchar(4),/**//*輸出方式退單理由輸出、申報單位輸出、全部輸出*/
 15    @TE_END_DATE datetime,/**//**********申報起止日期********/
 16    @TE_END_DATEEND datetime,/**//*************************/
 17    @TE_MONI_T varchar(4),/**//*監(jiān)控類型*/
 18    @USER_ID varchar(64),
 19    @CUSTOMER_CODE varchar(4),
 20    @PAGE_NUMBER int,
 21    @TOTAL_COUNT int OUTPUT
 22AS
 23
 24/**//******************************************************************************
 25**        File: 
 26**        Name: sp_tg009
 27**        Desc: 通關(guān)業(yè)務(wù)監(jiān)控－通關(guān)規(guī)范監(jiān)控－報關(guān)單退（拒）單管理
 28**
 29**        This template can be customized:
 30**              
 31**        Return values:
 32** 
 33**        Called by: 
 34**              
 35**        Parameters:
 36**        Input                            Output
 37**     ----------                            -----------
 38**        
 39**        Auth: chengdj
 40**        Date: 2005-4-7
 41*******************************************************************************
 42**        Change History
 43*******************************************************************************
 44**        Date:        Author:                Description:
 45**        --------    --------        -------------------------------------------
 46**    2005-04-11    chengdj                add HgDiv function
 47**      2005-04-11    chengdj                
 48*******************************************************************************/
 49SET NOCOUNT ON
 50
 51declare @sqlWhere varchar(4000)
 52set @sqlWhere = ‘‘
 53
 54set @TE_END_DATEEND = DATEADD(Day,1,@TE_END_DATEEND)    --結(jié)束日期加一天    
 55set @sqlWhere = @sqlWhere + ‘ TE_END_DATE  >= ‘‘‘+ CONVERT(varchar(20),@TE_END_DATE) +‘‘‘ and TE_END_DATE < ‘‘‘+CONVERT(varchar(20),@TE_END_DATEEND)+‘‘‘‘
 56
 57if @TE_I_E_FLAG <> ‘-2‘        ----進(jìn)出口
 58    set @sqlWhere = @sqlWhere + ‘ and TE_I_E_FLAG = ‘‘‘+@TE_I_E_FLAG+‘‘‘‘
 59
 60if @TE_PASS_RANGE <> ‘-2‘        --關(guān)區(qū)范圍
 61        set @sqlWhere = @sqlWhere + ‘ and TE_PASS_RANGE  in ( SELECT a.GCL_CODE
 62FROM GL_CUSTOMER_LIST a CROSS JOIN
 63      GL_CUSTOMER_LIST b
 64WHERE (a.GCL_LAYER LIKE b.GCL_LAYER + ‘‘%‘‘) AND (b.GCL_CODE = ‘‘‘+@TE_PASS_RANGE+‘‘‘)) ‘
 65
 66/**//* if @TE_C_OUTPUT<> ‘-2‘        --輸出方式
 67    set @sqlWhere = @sqlWhere + ‘a(chǎn)nd ‘
 68****************監(jiān)控類型**********************/
 69
 70    /**//*if @TE_MONI_T <> ‘-2‘    --監(jiān)控類型
 71        if @TE_MONI_T = ‘2‘
 72                 set @sqlWhere = @sqlWhere + ‘ AND  TE_SCENE_FLAG = 1 ‘
 73        else
 74                set @sqlWhere = @sqlWhere + ‘ AND  TE_WORKER_FLAG  =  ‘ +@TE_MONI_T + ‘ ‘*/
 75/**//*
 76CREATE TABLE #TG009(            
 77        TE_PASS_RANGE VARCHAR(4) PRIMARY KEY NOT NULL,
 78        MAN_CLE_COUNT int null,            --人工退單報關(guān)單總數(shù)
 79        MAN_CLE_TOTAL int null,            --人工退單報關(guān)單總數(shù)
 80        MAN_CLE_PCT float null,            --人工退單率
 81        ELC_CLE_COUNT int null,            --電子退單總數(shù)
 82        ELC_CLE_TOTAL int null,            --電子退單總次數(shù)
 83        MAN_MU_COUNT int null,            --多次人工退單報關(guān)單數(shù)
 84        ELC_CLE_PCT float null,            --電子退單率--
 85        RE_COUNT int null,                --現(xiàn)場拒單報關(guān)單總數(shù)--
 86        RE_TOTAL int null,                --現(xiàn)場拒單報關(guān)單總次數(shù)--
 87        RE_PCT float null,                --現(xiàn)場拒單報關(guān)單率--
 88        MU_ELC_CEL_EXP float null,        --多次電子退單指數(shù)
 89        MU_MAN_EXP float null,            --多次人工退單指數(shù)
 90        MU_SEC_EXP float null            --多次現(xiàn)場拒單指數(shù)
 91)
 92    */
 93    declare @sql varchar(8000)
 94    declare @groupby varchar(50)
 95    
 96    if @TE_C_OUTPUT  = ‘1‘    --按申報單位
 97         set @groupby = ‘TE_AGENT_CODE‘
 98    else                        --其它按關(guān)區(qū)
 99         set @groupby = ‘TE_PASS_RANGE‘
100         
101         declare @sql2 nvarchar(500)
102           set @sql2 = ‘select @COUNT = count(distinct(‘+@groupby+‘)) from TG_ENTRY where ‘+@sqlWhere
103    execute sp_executesql 
104          @sql2,
105          N‘@COUNT int output‘,
106          @TOTAL_COUNT output
107          
108DECLARE @START_ID INT
109DECLARE @END_ID INT
110SET @START_ID = (@PAGE_NUMBER - 1) * 15 + 1
111SET @END_ID = @PAGE_NUMBER * 15
112     
113     ----
114     ----
115     -----     
116     if @TE_MONI_T = ‘1‘    --人工退單
117                            set @sql = ‘
118                    select top ‘+convert(varchar(20),@END_ID)+ ‘ ‘+ @groupby +‘,IDENTITY(int,1,1) AS TID into #PAGE  from TG_ENTRY where ‘+@sqlWhere +‘ group by ‘+@groupby+ ‘;
119                    CREATE TABLE #TG009(            
120                        ‘+ @groupby+‘ VARCHAR(10) PRIMARY KEY NOT NULL,
121                        MAN_CLE_COUNT int null,    
122                        MAN_CLE_TOTAL int null,
123                        MAN_CLE_PCT numeric(10,2) null,
124                        MU_MAN_EXP numeric(10,2) null,
125                        MAN_CLE_TOTAL1 int null,
126                        MAN_CLE_TOTAL2 int null,
127                        MAN_CLE_TOTAL3 int null
128                        );
129
130                    SELECT ‘+ @groupby+‘,TE_CANCEL_FLAG,TE_WORKER_FLAG,TE_END_FLAG,TE_CANCEL_TIME INTO #temp1 FROM TG_ENTRY where‘+@sqlWhere+‘ AND ‘+@groupby+‘ in ( select  ‘+ @groupby +‘ from #PAGE where TID between ‘+ convert