当前位置: 首页 > 图文教程 > 数据库 > MSSQL > SQL Server大数据量统计系统的经验总结

MSSQL
SQL Server--全文本检索的应用（一）
SQL 2005的SSIS与Oracle的迁移性能
SQL优化实例：从运行30分钟到运行只要30秒
无法在SQL Server2005 Manger Studio 中录入中文的问题
SQL Artisan多表查询和统计
SQL Server数据库开发人员在应聘时经常被问到哪些问题
一个完整的SQL SERVER数据库全文索引的示例
SQL Server安全之加密术和SQL注入攻击
如何对SQL Server中的tempdb“减肥”
SQL Server 2005升级的十个步骤
如何在SQL Server开发中融入极限编程技术
SQL Server应用程序高级SQL注入(下)
SQL Server应用程序高级SQL注入(上)
SQL Server连接中的常见错误
IIS中SQL Server数据库的安全问题
SQL Server 2005区域配置和安全工具
保护 SQL Server 的十个步骤
如何利用SQL Server 2000的复制选项
SQL Server 数据库使用备份还原造成的孤立用户和对象名‘xxx’无效的错误的解决办法
SQL SERVER 2005同步复制技术的应用

No. « 100 101 102 103 »

技术文章搜索

关键字

MSSQL 中的 SQL Server大数据量统计系统的经验总结

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2009-10-17   浏览: 44 ::
收藏到网摘: n/a

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

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

  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),/*关区范围*/
 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
 22AS
 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*******************************************************************************/
 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'        --关区范围
 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          
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一起管理了，并且支持单步调试