请Sql Server大牛进来帮忙

[evasunny2008]

我这个项目每天晚上0点都要对今天所有的数据进行结算，结算写的是一个存储过程，用SQL的作业来定时执行
我现在遇到的问题是，每次在执行结算的时候，看观察windows任务管理器里的性能时，CPU使用记录同一时间只有一条线在执行(一个核心)，我这个服务器配置的是2个CPU，从性能里看，一共有40个核心，下面有截图，始终CPU使用只有2%，而后面的40个方块中同一时间只会有一个在走。因为现在的数据量多了，每天结算的时间就要1个半小时了，不知道有没有什么方法能在结算的时候多让几个核心同时运算呢？要不然这40个核心太浪费了。因为结算的时候会把系统关闭，所以需要能大幅度提高结算的速度，以前是以为硬件不太好，现在看来不是了。我们自己开发用的机器结算都比服务器要快，因为我们开发的机器CPU主频要比服务器的高。想请问大牛们，谁知道这个问题要怎么解决呢？最好是能在SQL里进行解决！！

[evasunny2008]

SQL Server 2008 r2的sp3补丁也打过了

CPU、Windows、SQL都装的是64位的

现在这个服务器还是空的，一个站点都没挂，IIS里是空的，每次看cpu使用情况都是2%，应该是没有什么其它程序高耗CPU

我把Boost SQL Server priority勾选上了，重新运行结算，还是只有一个核心在走

[唐诗三百首]

建议检查SQL Server有关处理器的设置是否合适.
SQL Server Management Studio --> SQL实例 --> 右键 -- 属性 --> 处理器 --> 如下图,

[唐诗三百首]

除2楼,10楼所述的SQL设定参数检查(参数含义请参考MSDN,2楼附有链接), 另建议: 1.检查安装SQL2008R2 SP3补丁. 2.检查安装Windows补丁. 3.检查服务器CPU, Windows, SQL Server的位数(32位/64位)是否一致. 4.检查服务器Windows任务管理器,是否其他高耗CPU的应用程序.

[tcmakebest]

最重要的是这个结算操作是如何进行的, 如果开多个程序同时算会不会出问题, 会不会进行重复的结算反而更浪费, 要结算的数据是否可以划分出多个独立的小块让多个核心各自运行, 然后就可以开多线程来操作了.

[唐诗三百首]

看各项配置应该没什么问题, 建议检查结算存储过程的算法是否可优化, 是否有多个单项操作,例如用先取出前一天的所有交易记录,然后用游标(或其他方式)逐条逐条的去结算处理... 这样是用不了多个核心的.可以考虑改为批量操作.

[唐诗三百首]

2、按每个会员的层级算出该会员今天应该得到多少奖励，最多9层。。 所有的时间都花在这一步上了，这一步要做的工作是先查出来能收奖励的人放到临时表里，通过while循环每次取到一个人，计算从他下面第一层到第9层各有多少人会给当前这个人产生奖励，然后按每一层的比率*人数，最后求出来这个人的总和，更新DB并插入一个记录。 --> ...通过while循环每次取到一个人... 请问是一个一个的按单个人去算的吗?

[evasunny2008]

引用 14 楼 ap0405140 的回复:

看各项配置应该没什么问题, 建议检查结算存储过程的算法是否可优化, 是否有多个单项操作,例如用先取出前一天的所有交易记录,然后用游标(或其他方式)逐条逐条的去结算处理... 这样是用不了多个核心的.可以考虑改为批量操作.

结算的存储过程大概有四个重要操作要处理 1、符合条件的人进行金额上的累加，累加金额每天的固定的，一个update语句就完成了，会员数据大概有12万左右，这个更新用时30秒左右。 2、按每个会员的层级算出该会员今天应该得到多少奖励，最多9层。。 所有的时间都花在这一步上了，这一步要做的工作是先查出来能收奖励的人放到临时表里，通过while循环每次取到一个人，计算从他下面第一层到第9层各有多少人会给当前这个人产生奖励，然后按每一层的比率*人数，最后求出来这个人的总和，更新DB并插入一个记录。 3、前两个都做完以后，对满足条件的会员进行升级，5秒以内完成 4、一个小的汇总，1秒以内完成 就拿第2步来说，这步的功能已经从以前的用parentId方式取层级改成了用hierarhicid类型来处理，对代码上也从以前的双重循环改成了单层循环，这些改动已经从1小时40分改到了半个小时的时间，难不成速度慢的原因会是更新DB和插入记录吗（每次循环都要操作两次数据库）

[ydzyjz]

楼主，搞传销是非法的。。。。。

[evasunny2008]

引用 16 楼 ap0405140 的回复:

2、按每个会员的层级算出该会员今天应该得到多少奖励，最多9层。。 所有的时间都花在这一步上了，这一步要做的工作是先查出来能收奖励的人放到临时表里，通过while循环每次取到一个人，计算从他下面第一层到第9层各有多少人会给当前这个人产生奖励，然后按每一层的比率*人数，最后求出来这个人的总和，更新DB并插入一个记录。 --> ...通过while循环每次取到一个人... 请问是一个一个的按单个人去算的吗?

是的呀，每次循环都要查到当前这个人往下9层，每一层各有多少人，用每一层的人数*每一层的比例，最后算出这个人的总和，因为每一层的比例是不一样的。 昨天我也试了一下，光是执行这一步内容且不更新数据库不插入记录，只是通过循环计算每个人的金额，总共所需要时间是20多分钟。

[唐诗三百首]

如果算法是一个一个的按单个人去算的话, 应该是无法并行使用CPU多线程的, 而且速度较慢. 建议优化算法为批量多人同时计算.

[evasunny2008]

怎么能批量计算呢？我这块的代码已经贴出来了，麻烦帮我看看吧~~

[evasunny2008]

DECLARE @i INT;--循环变量
DECLARE @cnt INT;--循环结束条件
DECLARE @autoId BIGINT;--当前会员的autoId
DECLARE @hongLiToal DECIMAL(18, 3);--当前会员的分红总数
DECLARE @tuiJianCnt INT;--当前会员的直推人数
DECLARE @maxLevel INT;--当前会员应该收取代利的最大层级数
DECLARE @curr HIERARCHYID;--当前会员层级对象
DECLARE @currMemDaiLi DECIMAL(18, 3);--当前会员应收代利总和
DECLARE @daiLi_jinBi DECIMAL(18, 3);--代利的金币
DECLARE @daiLi_zhongZiBi DECIMAL(18, 3);--代利的金种子
DECLARE @daiLi_liuTong DECIMAL(18, 3);--代利的流通市值
DECLARE @daiLi_xiaoFei DECIMAL(18, 3);--代利的消费市值

--所有需要收代利的人，放到这个临时表里
DECLARE @daiLiTempTable TABLE
    (
     id INT ,--row_number()
     autoId BIGINT ,--会员的自增长id
     hongLiTotal DECIMAL(18, 3) ,--总共分红的数字
     tuiJianCnt INT--直接推荐了几个人
    );
    
--分红未出局比例，出局与未出局比例是不一样的，所以会有两种比例，下面是第二种比例
--k的值是1-9层，v的值是1-9层对应的比例值
DECLARE @dlTable TABLE (k INT, v DECIMAL(18, 3));
INSERT  @dlTable
SELECT  [level] ,
        [rate]
FROM    [dbo].[S_DaiLiRate]
WHERE   [type] = 1;


--分红出局比例
DECLARE @dlTable2 TABLE (k INT, v DECIMAL(18, 3));
INSERT  @dlTable2
SELECT  [level] ,
        [rate]
FROM    [dbo].[S_DaiLiRate]
WHERE   [type] = 2;
    
--当前会员应收每层代利的人数
DECLARE @level_CntTempTable TABLE (level INT, cnt INT);

--初始化所有需要收代利的人
--这里是说除了公司(autoId=1)，其它所有有效并且激活的会员都可能收取代利
INSERT  @daiLiTempTable
SELECT  ROW_NUMBER() OVER (ORDER BY [T1].[autoId] DESC) AS id ,
        [T1].[autoId] ,
        [T1].[hongLiTotal] ,
        (
         SELECT COUNT(1)
         FROM   dbo.D_Member T2
         WHERE  T2.tuiJianId = T1.autoId
        ) AS tuiJianCnt
FROM    [dbo].[D_Member] T1
WHERE   [T1].[status] = 1
        AND [T1].[isActive] = 1
        AND [T1].[autoId] <> 1;

--初始化循环变量
SELECT  @i = 1 ,
        @cnt = COUNT(1)
FROM    @daiLiTempTable;

BEGIN TRANSACTION;  
SET @ERROR = 0;

--循环所有需要收代利的人
WHILE @i <= @cnt
    BEGIN
		--取到当前人的信息
        SELECT  @autoId = [autoId] ,
                @hongLiToal = [hongLiTotal] ,
                @tuiJianCnt = [tuiJianCnt]
        FROM    @daiLiTempTable
        WHERE   [id] = @i;
        
        --清空临时表
        DELETE  @level_CntTempTable;
        
        --如果当前人没有直推会员，则该会员不收代利，直接跳过
        IF @tuiJianCnt = 0
            BEGIN
                SET @i += 1;
                CONTINUE;
            END;
        
        --设置最大层级数
        --直推1人能收取第1层的代利，直推2人能收取第1、2层的代利，……，直推9人或以上，收取第1-9层的代利
        SET @maxLevel = CASE WHEN @tuiJianCnt < 9 THEN @tuiJianCnt
                             ELSE 9
                        END;
                        
        
        --得到当前会员应收代利每层的人数
        SELECT  @curr = hierarchy
        FROM    dbo.D_Member
        WHERE   autoId = @autoId;
        
        INSERT  @level_CntTempTable
        SELECT  hierarchy.GetLevel() - @curr.GetLevel() ,
                COUNT(1)
        FROM    dbo.D_Member
        WHERE   hierarchy.IsDescendantOf(@curr) = 1
                AND hongLiTotal < 3900
                AND status = 1
                AND isActive = 1
                AND hierarchy.GetLevel() <= @curr.GetLevel() + @maxLevel
        GROUP BY hierarchy.GetLevel();
        
        --得到当前会员应收代利总和
        --这里就是根据分红出局与未出局算出的当前会员应该能得到多少代利的总和放在@currMemDaiLi变量里
        IF @hongLiToal < @sys_maxHongLi
            BEGIN
				--分红未出局
                SELECT  @currMemDaiLi = SUM(T.s)
                FROM    (
                         SELECT lc.[cnt] * dt.[v] * @sys_hongLi AS s
                         FROM   @level_CntTempTable lc
                         INNER JOIN @dlTable dt ON [lc].[level] = dt.[k]
                        ) AS T;
                
            END;
        ELSE
            BEGIN
				--分红出局
                SELECT  @currMemDaiLi = SUM(T.s)
                FROM    (
                         SELECT lc.[cnt] * dt.[v] * @sys_hongLi AS s
                         FROM   @level_CntTempTable lc
                         INNER JOIN @dlTable2 dt ON [lc].[level] = dt.[k]
                        ) AS T;
            END;
            
        --这里已经得到当前这个人的应收代利放在@currMemDaiLi变量里
        --在下面就是要做插入记录，更新数据库的操作，代码就给省略了，感觉速度慢的原因不会在这里
        
        SET @ERROR += @@ERROR;
        SET @i += 1;
    END;

[唐诗三百首]

引用 20 楼 evasunny2008 的回复:

怎么能批量计算呢？我这块的代码已经贴出来了，麻烦帮我看看吧~~

不好意思,没有看懂, 只能说给你个解决问题的思路或方向, 再具体的涉及业务逻辑,这个应该就没法帮了.. 举个例子说明下批量计算, 例如需算1000人,每人20行交易记录,一个一个算的话,就得从数据库查询1000次(各返回20行), 然后再算1000次后继同样的逻辑, 这样做都是小查询(一次返回20行)和小型事务(一次处理20行), SQL引擎会认为完全不需要并行处理(给一个CPU内核足够处理了). 可以修改为, 一次从数据库取出1000人的交易记录(20000行)到临时表,然后分不同的情况,再做后继计算和处理, 最好一次性把结果更新回表里.

[唐诗三百首]

引用 22 楼 evasunny2008 的回复:

我不知道我理解的对不对。如果需要计算1000人，我现在的做法是每一次循环处理1个人的业务，那所谓的批量计算是不是一次循环处理10个人的业务呢？这样就只需要循环100次就够了？

是的, 大概思路是这样, 具体还是需靠你自己分析... 这样有利于提高多核CPU的利用率, 也就是本帖一开始提的问题. 程序执行速度应该也随之提升的.

[evasunny2008]

我不知道我理解的对不对。如果需要计算1000人，我现在的做法是每一次循环处理1个人的业务，那所谓的批量计算是不是一次循环处理10个人的业务呢？这样就只需要循环100次就够了？

[evasunny2008]

这种代码要怎么写呢？在一个while里面一次处理多条数据？然后修改循环变量一次累加10？

[唐诗三百首]

引用 24 楼 evasunny2008 的回复:

这种代码要怎么写呢？在一个while里面一次处理多条数据？然后修改循环变量一次累加10？

1.把待计算的人(假设1000人)存为临时表,并加入序号列(1-1000,row_number()函数实现), 2.设变量@id从1开始,第1次循环取出1-100, 然后变量@id+100, 第2次循环取出101-200, 以此类推..

[evasunny2008]

你说的这个我明白。我在临时表里已经加入的有row_number 循环我也会做，无非就是修改@id的事。。 我的问题是，当我一次取出100人的数据的时候，这100个人要怎么处理呢？ 我的感觉还是要一个一个去处理？因为每个人向下9层的人数都不一样，所收取的金额也是不一样的。

[唐诗三百首]

可以实现的,相信我没错的. 再讲下去...我觉得实在没什么意思了. 再次说明: 这里只能说给你个解决问题的思路或方向, 具体还是需靠你自己分析, 我相信你一定可以的!

[evasunny2008]

这100个人处理的思路能给说说吗？真的没有做过这样的处理，别说在SQL里了，在C#里也没有做过。