插入语句脚本几乎耗时40分钟来执行 [英] Insert statement script almost consumes 40 mins to execute
问题描述
要求是插入从AAAAA到ZZZZZ的所有可能组合
即组合总数为11881376（26 * 26 * 26 * 26 * 26）
我尝试过：
DECLARE我编号：= 65;
BEGIN
WHILE（i< = 90）LOOP
DECLARE j号码：= 65;
BEGIN
WHILE （j <= 90）LOOP
DECLARE k number：= 65;
BEGIN
WHILE（k <= 90）LOOP
DECLARE l number：= 65;
BEGIN
WHILE（l< = 90）LOOP
DECLARE m number：= 65;
BEGIN
WHILE（m< ; = 90）LOOP
INSERT IN MAPS.ALPHABETS_CHARCTER（ALPHABETS）
VALUES（CHR（i）|| CHR（j）|| CHR（k）|| CHR （l）|| CHR（m））;
m：= m + 1;
END LOOP;
END;
l：= l + 1;
END LOOP;
END;
k：= k + 1;
END LOOP;
END;
j：= j + 1;
END LOOP;
END;
i：= i + 1;
END LOOP;
END;
Requirement is to Insert every possible combination starting from AAAAA to ZZZZZ
i.e total no of combinations are 11881376 (26*26*26*26*26)
What I have tried:
DECLARE i number :=65;
BEGIN
WHILE (i<=90) LOOP
DECLARE j number :=65;
BEGIN
WHILE (j<=90) LOOP
DECLARE k number :=65;
BEGIN
WHILE (k<=90) LOOP
DECLARE l number :=65;
BEGIN
WHILE (l<=90) LOOP
DECLARE m number :=65;
BEGIN
WHILE (m<=90) LOOP
INSERT INTO MAPS.ALPHABETS_CHARCTER (ALPHABETS)
VALUES(CHR(i) || CHR(j) || CHR(k) || CHR(l) || CHR(m));
m :=m + 1;
END LOOP;
END;
l :=l + 1;
END LOOP;
END;
k :=k + 1;
END LOOP;
END;
j :=j + 1;
END LOOP;
END;
i := i + 1;
END LOOP;
END;
推荐答案
要求插入从AAAAA到ZZZZZ的所有可能组合
总共没有组合是11881376（26 * 26 * 26 * 26 * 26）
Requirement is to Insert every possible combination starting from AAAAA to ZZZZZ
i.e total no of combinations are 11881376 (26*26*26*26*26)
所以你要插入11881376条记录，你想知道为什么需要时间？
$ b $ 40 11881376在40分钟内意味着每分钟297k记录或每秒5k记录，还不错。
你的剧本很大！
So you are inserting 11881376 records and you wonder why it takes time ?
11881376 in 40 minutes means 297k records per minute or 5k record per second, not so bad.
Your script is huge !
你正在运行11.8百万INSERT语句，你想知道为什么它需要40分钟运行？根据SQL Server的规格，我不会感到惊讶它需要那么长时间。
这似乎是要解决的竞赛问题，其他一些挑战 代码需要在很短的时间内完成，或者这是作业。
如果你把循环放在循环中而不能得到它时间限制，这是因为你正在接近问题的解决方案完全错误。
阅读组合和排列 - SQLServerCentral [ ^ ]
You're running 11.8 million INSERT statements and you're wondering why it's taking 40 minutes to run? Depending on the specs of the SQL Server, I'm not surprised it takes that long.
This seems like it's either a contest problem to solve, some other "challenge" where the code needs to complete in a small amount of time, or this is homework.
If you're putting loops inside loops and not able to get it under the time limit, it's because you're approaching the solution to the problem entirely wrong.
Read Combinations and Permutations - SQLServerCentral[^]
这篇关于插入语句脚本几乎耗时40分钟来执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
