项目介绍
政府一个业务系统,
使用范围全国
数据是区域还存储,最大地方,主业务表,一年有600万,工作流表,1年有几千万,迄今为至系统已经运行三年。
其它全国还有46个地点,数据也比较多
新开发系统主要功能,对业务系统的数据进行分析统计.
所以对性能要求比较高。
我们的简要方案。
建立中间表,通过DTS调度每天共步数据。
中间表设计原则
记录数同原表一样,减少多表连接,保存运算好的值,如果记录修改,根据修改日志,重新计算中间值
增量同步数据(DTS)
直接从每天的数据库更改日志读取记录,更新中间表,根据服务器空间程度合理调度DTS,减少数据同步时间。
对中间数据进行运算
查询不作字段运行,所以运算在生成中间表的过程中已经计算
根据查询,优化索引设计
根据数据查询特性,对where ,GROUP BY等操作字段进行索引设计,提高查询速度
优化数据类型
大量采用Int提高查询、统计速度
优化中间表关键字
采用Int,提高插入速度
数据文件优化设计,一个主要业务,一个数据文件,建数据文件时,估计数据量,一次建一个比较大的文件,这样所分配的文件就是一个连续文件块,
sql server设置区别大小写。初始内存调到一个比较大的内存。
使用我们的Toolkit开发简单分页,相关压力测试,
测试服务器配制
2个至强3.0CPU
2G内存
150G硬盘
Window 2000 Advance Server中文版+SP4
测试数据ENTRY_WORKFLOW表,数据量2,473,437
页数 界面显示时间 CPU Reads I/O Writes I/O Duration
第1页 2-3 S 642 10689 0 390
第100页 3-4S 626 128001 0 423
....后页业数太多,没有必要
压力测试
并发数 平均每秒请求数 未字节响应毫秒数
50 45.28 20,095.65
25 45.41 10,043.12
索引优化测试,
对分量值小的数据建索引测试,测试语句,GROUP BY 分量值
一个字段,大概有6个分量值,没有建索引,4S,建索引<1s
两个分量,不建索引,3S,建索引,<1S
一般来说,对分量小的字段,不建索引,但是我们对性能要求太高,根据我们的测试,数据对分量范围小的也要建索引。
因为一个统计,有一个很多组合的WHERE,比如有十个指标这样会有十次访问原表,这样性能太低,所以我们把where后的数据作
用中间数据,
十个指标对中间数据作查询,中间数据,我们使用临时表,
经测试,10万条记录,插入操作,临时表需要,16s,表变量需要,40S
select ... into #temp from .......
速度极快,2,500,000条记录,16S
一个存储过程样例,有兴趣可以分析一下!
2 GO
3 SET ANSI_NULLS ON
4 GO
5
6
7
8
9
10 ALTER Procedure sp_tg009
11 /**/ /* Param List */
12 @TE_I_E_FLAG varchar ( 4 ), /**/ /*进出口方式*/
13 @TE_PASS_RANGE varchar ( 4 ), /**/ /*关区范围*/
14 @TE_C_OUTPUT varchar ( 4 ), /**/ /*输出方式退单理由输出、申报单位输出、全部输出*/
15 @TE_END_DATE datetime , /**/ /**********申报起止日期********/
16 @TE_END_DATEEND datetime , /**/ /*************************/
17 @TE_MONI_T varchar ( 4 ), /**/ /*监控类型*/
18 @USER_ID varchar ( 64 ),
19 @CUSTOMER_CODE varchar ( 4 ),
20 @PAGE_NUMBER int ,
21 @TOTAL_COUNT int OUTPUT
22 AS
23
24 /**/ /******************************************************************************
25** File:
26** Name: sp_tg009
27** Desc: 通关业务监控-通关规范监控-报关单退(拒)单管理
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*******************************************************************************/
49 SET NOCOUNT ON
50
51 declare @sqlWhere varchar ( 4000 )
52 set @sqlWhere = ''
53
54 set @TE_END_DATEEND = DATEADD ( Day , 1 , @TE_END_DATEEND ) -- 结束日期加一天
55 set @sqlWhere = @sqlWhere + ' TE_END_DATE >= ''' + CONVERT ( varchar ( 20 ), @TE_END_DATE ) + ''' and TE_END_DATE < ''' + CONVERT ( varchar ( 20 ), @TE_END_DATEEND ) + ''''
56
57 if @TE_I_E_FLAG <> ' -2 ' -- --进出口
58 set @sqlWhere = @sqlWhere + ' and TE_I_E_FLAG = ''' + @TE_I_E_FLAG + ''''
59
60 if @TE_PASS_RANGE <> ' -2 ' -- 关区范围
61 set @sqlWhere = @sqlWhere + ' and TE_PASS_RANGE in ( SELECT a.GCL_CODE
62 FROM GL_CUSTOMER_LIST a CROSS JOIN
63 GL_CUSTOMER_LIST b
64 WHERE (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****************监控类型**********************/
69
70 /**/ /*if @TE_MONI_T <> '-2' --监控类型
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, --人工退单报关单总数
79 MAN_CLE_TOTAL int null, --人工退单报关单总数
80 MAN_CLE_PCT float null, --人工退单率
81 ELC_CLE_COUNT int null, --电子退单总数
82 ELC_CLE_TOTAL int null, --电子退单总次数
83 MAN_MU_COUNT int null, --多次人工退单报关单数
84 ELC_CLE_PCT float null, --电子退单率--
85 RE_COUNT int null, --现场拒单报关单总数--
86 RE_TOTAL int null, --现场拒单报关单总次数--
87 RE_PCT float null, --现场拒单报关单率--
88 MU_ELC_CEL_EXP float null, --多次电子退单指数
89 MU_MAN_EXP float null, --多次人工退单指数
90 MU_SEC_EXP float null --多次现场拒单指数
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 -- 其它按关区
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
108 DECLARE @START_ID INT
109 DECLARE @END_ID INT
110 SET @START_ID = ( @PAGE_NUMBER - 1 ) * 15 + 1
111 SET @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 ' -- 现场拒单
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
228 exec ( @sql )
229
230
231
232
233
234
235 SET NOCOUNT OFF
236
237
238
239
240
241 GO
242 SET QUOTED_IDENTIFIER OFF
243 GO
244 SET ANSI_NULLS ON
245 GO
246
247