项目介绍
政府一个业务系统，
使用范围全国
数据是区域还存储,最大地方,主业务表，一年有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
一个存储过程样例，有兴趣可以分析一下！
1
SET
QUOTED_IDENTIFIER
ON
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
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****************监控类型**********************/
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
SET
QUOTED_IDENTIFIER
ON
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
/**/
/* 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
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
中间表生成SQL，以后提供，
另！
sql源代码管理，使用VS。NET，数据库项目就可以与SOURCE SAFE一起管理了，并且支持单步调试。
859
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
