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

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

jimlong

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

阅读量3.6k

点赞数

分类专栏： SQL的文摘文章标签： sql server null sql table output 数据库服务器

SQL的文摘专栏收录该内容

55 篇文章 0 订阅

订阅专栏

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

项目介绍
政府一个业务系统，
使用范围全国
数据是区域还存储,最大地方,主业务表，一年有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  
GO 
SET ANSI_NULLS ON  
GO 
 
 
 
 
 
ALTER     Procedure sp_tg009 
    /**//* Param List */ 
    @TE_I_E_FLAG varchar(4),/**//*进出口方式*/ 
    @TE_PASS_RANGE varchar(4),/**//*关区范围*/ 
    @TE_C_OUTPUT varchar(4),/**//*输出方式退单理由输出、申报单位输出、全部输出*/ 
    @TE_END_DATE datetime,/**//**********申报起止日期********/ 
    @TE_END_DATEEND datetime,/**//*************************/ 
    @TE_MONI_T varchar(4),/**//*监控类型*/ 
    @USER_ID varchar(64), 
    @CUSTOMER_CODE varchar(4), 
    @PAGE_NUMBER int, 
    @TOTAL_COUNT int OUTPUT 
AS 
 
/**//****************************************************************************** 
**        File:  
**        Name: sp_tg009 
**        Desc: 通关业务监控－通关规范监控－报关单退（拒）单管理 
** 
**        This template can be customized: 
**               
**        Return values: 
**  
**        Called by:  
**               
**        Parameters: 
**        Input                            Output 
**     ----------                            ----------- 
**         
**        Auth: chengdj 
**        Date: 2005-4-7 
******************************************************************************* 
**        Change History 
******************************************************************************* 
**        Date:        Author:                Description: 
**        --------    --------        ------------------------------------------- 
**    2005-04-11    chengdj                add HgDiv function 
**      2005-04-11    chengdj                 
*******************************************************************************/ 
SET NOCOUNT ON 
 
declare @sqlWhere varchar(4000) 
set @sqlWhere = '' 
 
set @TE_END_DATEEND = DATEADD(Day,1,@TE_END_DATEEND)    --结束日期加一天     
set @sqlWhere = @sqlWhere + ' TE_END_DATE  >= '''+ CONVERT(varchar(20),@TE_END_DATE) +''' and TE_END_DATE < '''+CONVERT(varchar(20),@TE_END_DATEEND)+'''' 
 
if @TE_I_E_FLAG <> '-2'        ----进出口 
    set @sqlWhere = @sqlWhere + ' and TE_I_E_FLAG = '''+@TE_I_E_FLAG+'''' 
 
if @TE_PASS_RANGE <> '-2'        --关区范围 
        set @sqlWhere = @sqlWhere + ' and TE_PASS_RANGE  in ( SELECT a.GCL_CODE 
FROM GL_CUSTOMER_LIST a CROSS JOIN 
      GL_CUSTOMER_LIST b 
WHERE (a.GCL_LAYER LIKE b.GCL_LAYER + ''%'') AND (b.GCL_CODE = '''+@TE_PASS_RANGE+''')) ' 
 
/**//* if @TE_C_OUTPUT<> '-2'        --输出方式 
    set @sqlWhere = @sqlWhere + 'and ' 
****************监控类型**********************/ 
 
    /**//*if @TE_MONI_T <> '-2'    --监控类型 
        if @TE_MONI_T = '2' 
                 set @sqlWhere = @sqlWhere + ' AND  TE_SCENE_FLAG = 1 ' 
        else 
                set @sqlWhere = @sqlWhere + ' AND  TE_WORKER_FLAG  =  ' +@TE_MONI_T + ' '*/ 
/**//* 
CREATE TABLE #TG009(             
        TE_PASS_RANGE VARCHAR(4) PRIMARY KEY NOT NULL, 
        MAN_CLE_COUNT int null,            --人工退单报关单总数 
        MAN_CLE_TOTAL int null,            --人工退单报关单总数 
        MAN_CLE_PCT float null,            --人工退单率 
        ELC_CLE_COUNT int null,            --电子退单总数 
        ELC_CLE_TOTAL int null,            --电子退单总次数 
        MAN_MU_COUNT int null,            --多次人工退单报关单数 
        ELC_CLE_PCT float null,            --电子退单率-- 
        RE_COUNT int null,                --现场拒单报关单总数-- 
        RE_TOTAL int null,                --现场拒单报关单总次数-- 
        RE_PCT float null,                --现场拒单报关单率-- 
        MU_ELC_CEL_EXP float null,        --多次电子退单指数 
        MU_MAN_EXP float null,            --多次人工退单指数 
        MU_SEC_EXP float null            --多次现场拒单指数 
) 
    */ 
    declare @sql varchar(8000) 
    declare @groupby varchar(50) 
     
    if @TE_C_OUTPUT  = '1'    --按申报单位 
         set @groupby = 'TE_AGENT_CODE' 
    else                        --其它按关区 
         set @groupby = 'TE_PASS_RANGE' 
          
         declare @sql2 nvarchar(500) 
           set @sql2 = 'select @COUNT = count(distinct('+@groupby+')) from TG_ENTRY where '+@sqlWhere 
    execute sp_executesql  
          @sql2, 
          N'@COUNT int output', 
          @TOTAL_COUNT output 
           
DECLARE @START_ID INT 
DECLARE @END_ID INT 
SET @START_ID = (@PAGE_NUMBER - 1) * 15 + 1 
SET @END_ID = @PAGE_NUMBER * 15 
      
     ---- 
     ---- 
     -----      
     if @TE_MONI_T = '1'    --人工退单 
                            set @sql = ' 
                    select top '+convert(varchar(20),@END_ID)+ ' '+ @groupby +',IDENTITY(int,1,1) AS TID into #PAGE  from TG_ENTRY where '+@sqlWhere +' group by '+@groupby+ '; 
                    CREATE TABLE #TG009(             
                        '+ @groupby+' VARCHAR(10) PRIMARY KEY NOT NULL, 
                        MAN_CLE_COUNT int null,     
                        MAN_CLE_TOTAL int null, 
                        MAN_CLE_PCT numeric(10,2) null, 
                        MU_MAN_EXP numeric(10,2) null, 
                        MAN_CLE_TOTAL1 int null, 
                        MAN_CLE_TOTAL2 int null, 
                        MAN_CLE_TOTAL3 int null 
                        ); 
 
                    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)+'); 
                    INSERT INTO #TG009( 
                            '+ @groupby+', 
                            MAN_CLE_COUNT, 
                            MAN_CLE_TOTAL, 
                            MAN_CLE_PCT, 
                            MU_MAN_EXP, 
                            MAN_CLE_TOTAL1, 
                            MAN_CLE_TOTAL2, 
                            MAN_CLE_TOTAL3)  
                    SELECT a.'+ @groupby+', 
                    (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 1 and '+ @groupby+' =a.'+ @groupby+'), 
                    (SELECT SUM(TE_CANCEL_TIME) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 1 and '+ @groupby+' =a.'+ @groupby+'), 
                    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+')), 
                    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+')), 
                    (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 1 AND TE_CANCEL_TIME = 1 AND '+ @groupby+' =a.'+ @groupby+'), 
                    (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 1 AND TE_CANCEL_TIME = 2 AND '+ @groupby+' =a.'+ @groupby+'), 
                    (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 1 AND TE_CANCEL_TIME > 2 AND '+ @groupby+' =a.'+ @groupby+') 
                    from #temp1 a 
                    group by a.'+ @groupby+'; 
                    DROP TABLE #temp1; 
                    SELECT * FROM #TG009; 
                    DROP TABLE #TG009' 
    else if        @TE_MONI_T = '0'    --电子 
                set @sql = ' 
                select top '+convert(varchar(20),@END_ID)+ ' '+ @groupby +',IDENTITY(int,1,1) AS TID into #PAGE  from TG_ENTRY where '+@sqlWhere +' group by '+@groupby+ '; 
                CREATE TABLE #TG009(             
                    '+ @groupby+' VARCHAR(10) PRIMARY KEY NOT NULL, 
                    ELC_CLE_COUNT int null, 
                    ELC_CLE_TOTAL int null, 
                    ELC_CLE_PCT numeric(10,2)  null, 
                    MU_ELC_CEL_EXP numeric(10,2) null, 
                    ELC_CLE_COUNT1 int null, 
                    ELC_CLE_COUNT2 int null, 
                    ELC_CLE_COUNT2B int null 
                ); 
                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)+'); 
                INSERT INTO #TG009( 
                        '+ @groupby+', 
                        ELC_CLE_COUNT, 
                        ELC_CLE_TOTAL, 
                        ELC_CLE_PCT, 
                        MU_ELC_CEL_EXP, 
                        ELC_CLE_COUNT1, 
                        ELC_CLE_COUNT2, 
                        ELC_CLE_COUNT2B)  
                SELECT a.'+ @groupby+', 
                (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 and '+ @groupby+' =a.'+ @groupby+'), 
                                (SELECT SUM(TE_CANCEL_TIME) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 and '+ @groupby+' =a.'+ @groupby+'), 
                                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+')), 
                                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+')), 
                                (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 AND TE_CANCEL_TIME = 1 AND '+ @groupby+' =a.'+ @groupby+'), 
                                (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 AND TE_CANCEL_TIME = 2 AND '+ @groupby+' =a.'+ @groupby+'), 
                                (SELECT COUNT(*) FROM #temp1 WHERE TE_CANCEL_FLAG = 1 AND TE_WORKER_FLAG = 0 AND TE_CANCEL_TIME > 2 AND '+ @groupby+' =a.'+ @groupby+') 
                from #temp1 a 
                group by a.'+ @groupby+'; 
                DROP TABLE #temp1; 
                SELECT * FROM #TG009; 
                DROP TABLE #TG009' 
         
    else if        @TE_MONI_T = '2'    --现场拒单 
            set @sql = ' 
            select top '+convert(varchar(20),@END_ID)+ ' '+ @groupby +',IDENTITY(int,1,1) AS TID into #PAGE  from TG_ENTRY where '+@sqlWhere +' group by '+@groupby+ '; 
            CREATE TABLE #TG009(             
                '+ @groupby+' VARCHAR(10) PRIMARY KEY NOT NULL, 
                RE_COUNT int null, 
                RE_TOTAL int null, 
                RE_PCT numeric(10,2) null, 
                MU_SEC_EXP numeric(10,2) null, 
                RE_TOTAL1  int null, 
                RE_TOTAL2  int null, 
                RE_TOTAL2B  int null 
            ); 
            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)+'); 
            INSERT INTO #TG009( 
                    '+ @groupby+', 
                    RE_COUNT, 
                    RE_TOTAL, 
                    RE_PCT, 
                    MU_SEC_EXP, 
                    RE_TOTAL1, 
                    RE_TOTAL2, 
                    RE_TOTAL2B)  
            SELECT a.'+ @groupby+', 
            (SELECT COUNT(*) FROM #temp1 WHERE TE_SCENE_FLAG = 1 and '+ @groupby+' =a.'+ @groupby+'), 
            (SELECT SUM(TE_SCENE_TIME) FROM #temp1 WHERE TE_SCENE_FLAG = 1 and '+ @groupby+' =a.'+ @groupby+'), 
            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+')), 
            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+')), 
            (SELECT COUNT(*) FROM #temp1 WHERE TE_SCENE_FLAG = 1 AND TE_SCENE_TIME = 1 AND '+ @groupby+' =a.'+ @groupby+'), 
            (SELECT COUNT(*) FROM #temp1 WHERE TE_SCENE_FLAG = 1 AND TE_SCENE_TIME = 2 AND '+ @groupby+' =a.'+ @groupby+'), 
            (SELECT COUNT(*) FROM #temp1 WHERE TE_SCENE_FLAG = 1 AND TE_SCENE_TIME > 2 AND '+ @groupby+' =a.'+ @groupby+') 
            from #temp1 a 
            group by a.'+ @groupby+'; 
            DROP TABLE #temp1; 
            SELECT * FROM #TG009; 
            DROP TABLE #TG009' 
--print @sql 
 
exec(@sql) 
 
 
 
 
 
 
SET NOCOUNT OFF 
 
 
 
 
 
GO 
SET QUOTED_IDENTIFIER OFF  
GO 
SET ANSI_NULLS ON  
GO

中间表生成SQL，以后提供，

另！
sql源代码管理，使用VS。NET，数据库项目就可以与SOURCE SAFE一起管理了，并且支持单步调试。

评论:

#1楼 2005-04-24 22:15 | birdshome

为什么不用olap?!

#2楼 2005-04-24 22:15 | Jason.NET

这种实用的东东非常有用

我没有看完,我的理解就是,你们利用各处的资源使计算分布式

从而缩短最后的统计时间,是吗?

支持(0) 反对(0)

#3楼 [ 楼主] 2005-04-24 22:55 | edobnet

主要是项目的一些因素决定，
项目数据库服务器和业务的一个分险管理系统的数据库服务器是同一吧，原因的风险系统，已经定性了一些设计模式。

OLAP，这东西的确，使用多维来管理。

支持(0) 反对(0)

#4楼 2005-04-24 23:41 | 天天

我也在做类似的项目，受益匪浅，不用OLAP的后果，就是要面对很复杂的条件查询，不知道你是如何处理的。
还有政府部门喜欢的同比，环比，BT到按月，按旬，按周，不知有什么好的处理模式呢？

#5楼 2005-04-25 09:18 | 强把忧郁再掩盖

.........楼上的

支持(0) 反对(0)

#6楼 2006-09-29 16:05 | 夏天愛[未注册用户]

請問你們中間表用DTS來調度，是用SQL自身帶的DTS功能，手動來完成這一功能，還是編寫代碼將DTS的功能實現到程序中，我用的C#，郵箱是summerlove521521@163.com 謝謝!
另外，針對分步式的架構是如何設計的，我現在有一系統，用於生產的，多個客戶端，Windows程序。要求：1、界面輸入新值時，需從數據庫中查詢是否存在，若存在，則不允許輸入。數據量特別大，查詢速度相當慢，請問這樣的問題，你是如何解決的？2、由於經常做數據表的打開及關閉，因為要存取數據，所以贊成死鎖了，這樣的問題又是如何解決？
非常感謝^_^

#7楼 2006-09-29 16:08 | 夏天愛[未注册用户]

3、是不是需要用到線程來提升速度，沒有用過，若用線程，系統用到連接硬件，讀取通訊數據，會不會有沖突？

#8楼 2006-10-10 10:53 | 数据量统[未注册用户]

总结

#9楼 2006-10-10 10:54 | 数据量统[未注册用户]

十直

#10楼 2006-10-10 10:55 | 数据量统[未注册用户]

@数据量统
@数据量统
@数据量统

#11楼 2010-01-11 14:18 | jes

我也做类似的项目，其中一个就是根据报关单库的统计。

我说下我看你这篇文章的感受，以及我自己的一些经历。
1.SQL里面还是不要拼SQL的方式，虽然有时候有些统计很复杂，用拼sql的方式不方便后面的人维护，当然外包项目一个好处就是不用管维护。
2.临时表不一定比表变量好。我这是针对大量数据而言。海关项目内有很多个系统，数据库的配置也是五花八门，有些排序规则不同，甚至同个数据库的master库和业务库的排序规则都可能不一样。这样的话，用临时表就要考虑排序规则的问题了。我曾经遇过这个问题，尝试很多次包括制定临时表的排序规则，结果还是不行，最终是通过表变量解决的，因为表变量不用考虑排序规则。当然，数据量大的话，性能会怎么样？没测过不好说，而且影响的因素也很多。
3.涉及到很大的表，将数据load到中间载体（如临时表、表变量）再做处理会快很多。特别是涉及到联表查询、链接服务区之类的时候。有时候你根本没有权限访问远程数据库查看数据库的索引设计等，把需要的数据取到本地再处理、统计，速度很可能不在同个级别。

支持(0) 反对(0)

#12楼 1744362 2010/1/11 15:02:16 2010-01-11 15:02 | 卡通一下

原来这是多年前发表的。

这是结合实际，难得一见的好文章。

比那些关起门来，成天“务虚”的文章强多了。

建议现在的人好好地读一读，想一想...

支持(0) 反对(0)