SQL Server大數據量統(tǒng)計系統(tǒng)的經驗總結_Mssql數據庫教程

推薦：SQL Server日志清除的兩種方法教程簡介
方法一 一般情況下，SQL數據庫的收縮并不能很大程度上減小數據庫大小，其主要作用是收縮日志大小，應當定期進行此操作以免數據庫日志過大 1、設置數據庫模式為簡單模式：打開SQL企業(yè)管理器，

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

所以對性能要求比較高。

我們的簡要方案。

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


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

對中間數據進行運算
查詢不作字段運行，所以運算在生成中間表的過程中已經計算

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

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

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

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

sql server設置區(qū)別大小寫。初始內存調到一個比較大的內存。

使用我們的Toolkit開發(fā)簡單分頁，相關壓力測試，
測試服務器配制
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

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

壓力測試

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

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

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

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


用中間數據，
十個指標對中間數據作查詢，中間數據，我們使用臨時表，
經測試，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),/**//*進出口方式*/
13 @TE_PASS_RANGE varchar(4),/**//*關區(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: 通關業(yè)務監(jiān)控－通關規(guī)范監(jiā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) --結束日期加一天
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' ----進出口
58 set @sqlWhere = @sqlWhere ' and TE_I_E_FLAG = ''' @TE_I_E_FLAG ''''
59
60if @TE_PASS_RANGE <> '-2' --關區(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, --人工退單報關單總數
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 --其它按關區(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' --現場拒單
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，數據庫項目就可以與SOURCE SAFE一起管理了，并且支持單步調試

分享：怎樣用SQL 2000 生成XML
　　以前在介紹SQL2k的時候已經提到了SQL2k對XML的支持，使用for XML語句就可以很容易的把執(zhí)行的結果轉化為一個XML，這樣可以在很大程度上提高系統(tǒng)運行效率和開發(fā)速度，詳細的內容請參見Books O