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

所以對(duì)性能要求比較高。

我們的簡(jiǎn)要方案。

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


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

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

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

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

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

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

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

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

頁(yè)數(shù) 界面顯示時(shí)間 CPU Reads I/O Writes I/O Duration
第1頁(yè) 2-3 S 642 10689 0 390
第100頁(yè) 3-4S 626 128001 0 423

....后頁(yè)業(yè)數(shù)太多，沒有必要

壓力測(cè)試

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

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

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

因?yàn)橐粋€(gè)統(tǒng)計(jì)，有一個(gè)很多組合的WHERE，比如有十個(gè)指標(biāo)這樣會(huì)有十次訪問(wèn)原表，這樣性能太低，所以我們把where后的數(shù)據(jù)作


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

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

一個(gè)存儲(chǔ)過(guò)程樣例，有興趣可以分析一下！

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),/**//*輸出方式退單理由輸出、申報(bào)單位輸出、全部輸出*/
15 @TE_END_DATE datetime,/**//**********申報(bào)起止日期********/
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)控－報(bào)關(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 'and '
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, --人工退單報(bào)關(guān)單總數(shù)
79 MAN_CLE_TOTAL int null, --人工退單報(bào)關(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, --多次人工退單報(bào)關(guān)單數(shù)
84 ELC_CLE_PCT float null, --電子退單率--
85 RE_COUNT int null, --現(xiàn)場(chǎng)拒單報(bào)關(guān)單總數(shù)--
86 RE_TOTAL int null, --現(xiàn)場(chǎng)拒單報(bào)關(guān)單總次數(shù)--
87 RE_PCT float null, --現(xiàn)場(chǎng)拒單報(bào)關(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)場(chǎng)拒單指數(shù)
91)
92 */
93 declare @sql varchar(8000)
94 declare @groupby varchar(50)
95
96 if @TE_C_OUTPUT = '1' --按申報(bào)單位
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(varchar(20),@START_ID) ' AND ' convert(varchar(20),@END_ID) ');
131 INSERT INTO #TG009(
132 ' @groupby ',
133 MAN_CLE_COUNT,
134 MAN_CLE_TOTAL,
135 MAN_CLE_PCT,
136 MU_MAN_EXP,
137 MAN_CLE_TOTAL1,
138 MAN_CLE_TOTAL2,
139 MAN_CLE_TOTAL3)
140 SELECT a.' @groupby ',
141 (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 1 and ' @groupby ' =a.' @groupby '),
142 (SELECT SUM(TE_CANCEL_TIME) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 1 and ' @groupby ' =a.' @groupby '),
143 risk.HgDiv((SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 1 and ' @groupby ' =a.' @groupby '),(SELECT COUNT(*) FROM #temp1 WHERE TE_END_FLAG = 1 AND TE_WORKER_FLAG = 1 and ' @groupby ' =a.' @groupby ')),
144 risk.HgDiv((SELECT SUM(TE_CANCEL_TIME) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 and ' @groupby ' =a.' @groupby '),(SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 and ' @groupby ' =a.' @groupby ')),
145 (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 1 AND TE_CANCEL_TIME = 1 AND ' @groupby ' =a.' @groupby '),
146 (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 1 AND TE_CANCEL_TIME = 2 AND ' @groupby ' =a.' @groupby '),
147 (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 1 AND TE_CANCEL_TIME > 2 AND ' @groupby ' =a.' @groupby ')
148 from #temp1 a
149 group by a.' @groupby ';
150 DROP TABLE #temp1;
151 SELECT * FROM #TG009;
152 DROP TABLE #TG009'
153 else if @TE_MONI_T = '0' --電子
154 set @sql = '
155 select top ' convert(varchar(20),@END_ID) ' ' @groupby ',IDENTITY(int,1,1) AS TID into #PAGE from TG_ENTRY where ' @sqlWhere ' group by ' @groupby ';
156 CREATE TABLE #TG009(
157 ' @groupby ' VARCHAR(10) PRIMARY KEY NOT NULL,
158 ELC_CLE_COUNT int null,
159 ELC_CLE_TOTAL int null,
160 ELC_CLE_PCT numeric(10,2) null,
161 MU_ELC_CEL_EXP numeric(10,2) null,
162 ELC_CLE_COUNT1 int null,
163 ELC_CLE_COUNT2 int null,
164 ELC_CLE_COUNT2B int null
165 );
166 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(varchar(20),@START_ID) ' AND ' convert(varchar(20),@END_ID) ');
167 INSERT INTO #TG009(
168 ' @groupby ',
169 ELC_CLE_COUNT,
170 ELC_CLE_TOTAL,
171 ELC_CLE_PCT,
172 MU_ELC_CEL_EXP,
173 ELC_CLE_COUNT1,
174 ELC_CLE_COUNT2,
175 ELC_CLE_COUNT2B)
176 SELECT a.' @groupby ',
177 (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 and ' @groupby ' =a.' @groupby '),
178 (SELECT SUM(TE_CANCEL_TIME) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 and ' @groupby ' =a.' @groupby '),
179 risk.HgDiv((SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 and ' @groupby ' =a.' @groupby '),(SELECT COUNT(*) FROM #temp1 WHERE TE_END_FLAG = 1 AND TE_WORKER_FLAG = 0 and ' @groupby ' =a.' @groupby ')),
180 risk.HgDiv((SELECT SUM(TE_CANCEL_TIME) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 and ' @groupby ' =a.' @groupby '),(SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 and ' @groupby ' =a.' @groupby ')),
181 (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 AND TE_CANCEL_TIME = 1 AND ' @groupby ' =a.' @groupby '),
182 (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 AND TE_CANCEL_TIME = 2 AND ' @groupby ' =a.' @groupby '),
183 (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 AND TE_CANCEL_TIME > 2 AND ' @groupby ' =a.' @groupby ')
184 from #temp1 a
185 group by a.' @groupby ';
186 DROP TABLE #temp1;
187 SELECT * FROM #TG009;
188 DROP TABLE #TG009'
189
190 else if @TE_MONI_T = '2' --現(xiàn)場(chǎng)拒單
191 set @sql = '
192 select top ' convert(varchar(20),@END_ID) ' ' @groupby ',IDENTITY(int,1,1) AS TID into #PAGE from TG_ENTRY where ' @sqlWhere ' group by ' @groupby ';
193 CREATE TABLE #TG009(
194 ' @groupby ' VARCHAR(10) PRIMARY KEY NOT NULL,
195 RE_COUNT int null,
196 RE_TOTAL int null,
197 RE_PCT numeric(10,2) null,
198 MU_SEC_EXP numeric(10,2) null,
199 RE_TOTAL1 int null,
200 RE_TOTAL2 int null,
201 RE_TOTAL2B int null
202 );
203 SELECT ' @groupby ',TE_SCENE_TIME,TE_CANCEL_FLAG,TE_SCENE_FLAG,TE_MEET_FLAG INTO #temp1 FROM TG_ENTRY where' @sqlWhere ' AND ' @groupby ' in ( select ' @groupby ' from #PAGE where TID between ' convert(varchar(20),@START_ID) ' AND ' convert(varchar(20),@END_ID) ');
204 INSERT INTO #TG009(
205 ' @groupby ',
206 RE_COUNT,
207 RE_TOTAL,
208 RE_PCT,
209 MU_SEC_EXP,
210 RE_TOTAL1,
211 RE_TOTAL2,
212 RE_TOTAL2B)
213 SELECT a.' @groupby ',
214 (SELECT COUNT(*) FROM #temp1 WHERE TE_SCENE_FLAG = 1 and ' @groupby ' =a.' @groupby '),
215 (SELECT SUM(TE_SCENE_TIME) FROM #temp1 WHERE TE_SCENE_FLAG = 1 and ' @groupby ' =a.' @groupby '),
216 risk.HgDiv((SELECT SUM(TE_SCENE_TIME) FROM #temp1 WHERE TE_SCENE_FLAG = 1 and ' @groupby ' =a.' @groupby '),(SELECT COUNT(*) FROM #temp1 WHERE TE_MEET_FLAG = 1 and ' @groupby ' =a.' @groupby ')),
217 risk.HgDiv((SELECT SUM(TE_SCENE_TIME) FROM #temp1 WHERE TE_SCENE_FLAG = 1 and ' @groupby ' =a.' @groupby '),(SELECT COUNT(*) FROM #temp1 WHERE TE_SCENE_FLAG = 1 and ' @groupby ' =a.' @groupby ')),
218 (SELECT COUNT(*) FROM #temp1 WHERE TE_SCENE_FLAG = 1 AND TE_SCENE_TIME = 1 AND ' @groupby ' =a.' @groupby '),
219 (SELECT COUNT(*) FROM #temp1 WHERE TE_SCENE_FLAG = 1 AND TE_SCENE_TIME = 2 AND ' @groupby ' =a.' @groupby '),
220 (SELECT COUNT(*) FROM #temp1 WHERE TE_SCENE_FLAG = 1 AND TE_SCENE_TIME > 2 AND ' @groupby ' =a.' @groupby ')
221 from #temp1 a
222 group by a.' @groupby ';
223 DROP TABLE #temp1;
224 SELECT * FROM #TG009;
225 DROP TABLE #TG009'
226--print @sql
227
228exec(@sql)
229
230
231
232
233
234
235SET NOCOUNT OFF
236
237
238
239
240
241GO
242SET QUOTED_IDENTIFIER OFF
243GO
244SET ANSI_NULLS ON
245GO
246
247

中間表生成SQL，以后提供，

另！
sql源代碼管理，使用VS。NET，數(shù)據(jù)庫(kù)項(xiàng)目就可以與SOURCE SAFE一起管理了，并且支持單步調(diào)試