如何多线程分批处理千万级数据?

Question

/***
SQL Server 2019数据库里已经有2个数据表  History10_77A  和  F27X9_133a;

表[ History10_77A ]里的数据有 1.2亿行数据,行转列后,#JCHtory10里的数据有 12亿 条数据;

表[ F27X9_133a ]里的数据有4,686,825行数据(约470万行记录),行转列后,#JCHtory10里的数据有42,181,425条数据(约4200万条记录);

查询要求:查询表[ History10_77A ]里的n1、n2、n3、n4、n5、n6、n7、n8、n9、n10,要求和表[ F27X9_133a ]里的S1、S2、S3、S4、S5、S6、S7、S8、S9这9个数字全部相同;
查询结果输入到表JCResult_9;

问题一
如何多线程分段处理批量数据?

问题二
对于刚刚学习多线程分段处理批量数据的菜鸟搭建哪一种运行环境比较合适?

***/
-- 执行脚本

DROP TABLE IF  EXISTS #JCHtory10;
DROP TABLE IF  EXISTS #JCF27X9;
DROP TABLE IF  EXISTS #JCResult;

create table JCResult_9([ID] [int] IDENTITY(1,1) NOT NULL primary key,n1 INT,n2 INT,n3 INT,n4 INT,n5 INT,n6 INT,n7 INT,n8 INT,n9 INT,n10 INT);
 
select ID,n1 as  gid 
into #JCHtory10 
FROM History10_77A
union all
select ID,n2 FROM History10_77A
union all
select ID,n3 FROM History10_77A
union all
select ID,n4 FROM History10_77A
union all
select ID,n5 FROM History10_77A
union all
select ID,n6 FROM History10_77A
union all
select ID,n7 FROM History10_77A
union all
select ID,n8 FROM History10_77A
union all
select ID,n9 FROM History10_77A
union all
select ID,n10 FROM History10_77A;
 
select ID,s1 as  cid  
into #JCF27X9
FROM [F27X9_133a]
union all
select ID,s2 FROM [F27X9_133a]
union all
select ID,s3 FROM [F27X9_133a]
union all
select ID,s4 FROM [F27X9_133a]
union all
select ID,s5 FROM [F27X9_133a]
union all
select ID,s6 FROM [F27X9_133a]
union all
select ID,s7 FROM [F27X9_133a];

 
SELECT #JCHtory10.id into #JCResult
    FROM #JCHtory10 
        JOIN #JCF27X9 ON #JCHtory10.gid = #JCF27X9.cid 
            GROUP BY #JCHtory10.id, #JCF27X9.ID 
                HAVING COUNT( 1 ) = 9 ;
              
INSERT INTO JCResult_9

SELECT n1,n2,n3,n4,n5,n6,n7,n8,n9,n10 FROM History10_77A a 
        WHERE EXISTS( SELECT 1 FROM #JCResult b WHERE a.id = b.id );
 

Answer 1

问题一
如何多线程分段处理批量数据?
多开些线程
如果有抢占的话new thread（）时记得加个锁，
建议对数据结构进行工厂和迭代优化

问题二
对于刚刚学习多线程分段处理批量数据的菜鸟搭建哪一种运行环境比较合适?
运行环境指的是什么呢？C#就.net/net core都行 Java 就一个jre C 都不需要 runtimeWIndows自备 C++同理 其他自查........不一一列举
sqlServer比较建议C#.net看效果比较好些