插入语句脚本几乎耗时40分钟来执行

本文介绍了插入语句脚本几乎耗时40分钟来执行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

要求是插入从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;

推荐答案

Quote：

要求插入从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[^]