最近开发一个SQL server大数据量统计系统的经验总结

最新推荐文章于 2019-08-10 10:30:00 发布

huchenhui1981

最新推荐文章于 2019-08-10 10:30:00 发布

阅读量2.4k

点赞数

分类专栏：转帖文章标签： sql server sql output 测试 date null

本文链接：https://blog.csdn.net/huchenhui1981/article/details/1432655

版权

转帖专栏收录该内容

3 篇文章 0 订阅

订阅专栏

项目介绍
政府一个业务系统，
使用范围全国
数据是区域还存储,最大地方,主业务表，一年有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

一个存储过程样例，有兴趣可以分析一下！

SET QUOTED_IDENTIFIER ON
2

GO
3

SET ANSI_NULLS ON
4

GO
5

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

/******************************************************************************
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

declare @sqlWhere varchar ( 4000 )
52

set @sqlWhere = ''
53

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

if @TE_I_E_FLAG <> ' -2 ' -- --进出口
58

set @sqlWhere = @sqlWhere + ' and TE_I_E_FLAG = ''' + @TE_I_E_FLAG + ''''
59

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

/* if @TE_C_OUTPUT<> '-2' --输出方式
67

set @sqlWhere = @sqlWhere + 'and '
68

****************监控类型**********************/
69

/*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

/*
76

CREATE 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

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

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

(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