MS SQL 2008, margin notes. A lot of random numbers
In the solution of many problems, it is necessary to insert a large number of similar lines containing previously specified information, for example, testing weak points in an application. The main difficulty is not so much the algorithm for creating data, as the mechanism allows you to create such a number of lines.
Everyone interested in the topic, welcome under the cat ...
Problem : on MS SQL 2008, add 10,000,000 (ten million) random primes from 1 to 100,000 to the table. I would like to do this as quickly as possible.
In the simplicity of the task, there is a large field for creativity.
')
Solution 1.
The easiest way is to organize a cycle and add a random number to the table at each iteration.
The solution is not original, simple and ... very long, very long indeed ...
T-sql
On my machine (CPU: AMD Phenom II X2 550 / RAM: 8Gb / HDD: WD5000AAKS / MS SQL2008R2 / Win7x64), when the execution time exceeded 25 minutes, the request had to be interrupted.
Solution 2.
The weakest point in the previous solution is the number of iterations equal to the number of rows added.
Having a little thought and having looked through google we find the interesting table master..spt_values (we do not exhaust MS SQL as atom).
Rewrite the query.
We will insert not one record at a time, but 2000 at a time.
The solution is not much different from the previous one, but we get a huge increase in performance.
T-sql
It took me 48 seconds to query.
(Assuming that our solution was 2000 times faster, but the previous one would have been executed for more than 26 hours).
Solution 3.
Is it possible to further increase productivity?
Let's look at the script, WHILE .
To get rid of it, we need a table containing as many numbers as possible - ideally equal to 10,000,000.
Swarm google - "CROSS JOIN" and "WITH".
We need a table of prime numbers:
from which we will create a table containing the necessary set of numbers:
and finally add the result
The request completed in less than 34 seconds.
T-sql
Which solution to choose?
Of course we do not use the solution in the forehead. That is, solution 1, you can safely give the story "how not to do."
The difference between solution 2 and solution 3 is not so obvious. Simplicity versus speed. Which one to use depends on the specific situation and what will be easier to maintain and expand in the future.
Source codes
Everyone interested in the topic, welcome under the cat ...
Problem : on MS SQL 2008, add 10,000,000 (ten million) random primes from 1 to 100,000 to the table. I would like to do this as quickly as possible.
In the simplicity of the task, there is a large field for creativity.
')
Solution 1.
The easiest way is to organize a cycle and add a random number to the table at each iteration.
The solution is not original, simple and ... very long, very long indeed ...
T-sql
On my machine (CPU: AMD Phenom II X2 550 / RAM: 8Gb / HDD: WD5000AAKS / MS SQL2008R2 / Win7x64), when the execution time exceeded 25 minutes, the request had to be interrupted.
Solution 2.
The weakest point in the previous solution is the number of iterations equal to the number of rows added.
Having a little thought and having looked through google we find the interesting table master..spt_values (we do not exhaust MS SQL as atom).
Rewrite the query.
We will insert not one record at a time, but 2000 at a time.
The solution is not much different from the previous one, but we get a huge increase in performance.
T-sql
It took me 48 seconds to query.
(Assuming that our solution was 2000 times faster, but the previous one would have been executed for more than 26 hours).
Solution 3.
Is it possible to further increase productivity?
Let's look at the script, WHILE .
To get rid of it, we need a table containing as many numbers as possible - ideally equal to 10,000,000.
Swarm google - "CROSS JOIN" and "WITH".
We need a table of prime numbers:
from which we will create a table containing the necessary set of numbers:
and finally add the result
The request completed in less than 34 seconds.
T-sql
Which solution to choose?
Of course we do not use the solution in the forehead. That is, solution 1, you can safely give the story "how not to do."
The difference between solution 2 and solution 3 is not so obvious. Simplicity versus speed. Which one to use depends on the specific situation and what will be easier to maintain and expand in the future.
Source codes
Source: https://habr.com/ru/post/130567/
All Articles