Challenge 3

[dbomrrsm]

Write SQL code that will put all Prime numbers between 1 and 1000000 ( 1 million) into a table and then select a count from that table

Rules
1 You have to write everything from scratch, meaning we should be able to execute the script on any computer and get the same result
2 Don't show any code until the 31st January (next Tuesday)
3 We should only show the time and recordcount so that people will have a general idea if their code is fast enough
example PIV 3.0 Ghz 2GB RAM runtime 21 seconds recordcount 567
People should post this every time they improve their code so that we can see what's going on
4 The first five people who sign up will run all the scripts and list the in execution time order or we can designate 5 people it doesn't matter
5 remember the FASTEST time wins, it doesn't have to look pretty

Good luck

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[SantaMufasa]

I'd be happy to "sign up". What constitutes "sign up"?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbomrrsm]

what you have just done - i thought you might be one of the first to reply so your place in the 5 was reserved

BTW the best time achieved on this in sql server was:

On a PIV with 2.8 Hyperthreaded 2GB Ram workstation

5.93 seconds

Thats what you are up against

good luck and dont damage your brains too much

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dbalearner]

It is difficult to see how "any" optimiser features will be relevant to this test.

Generating primes depends on the efficiency of your generator algorithm, and pretty much all of them (from factoring to sieving) are CPU-bound. This task will not be hitting disk, network or even memory, and in terms of the database access everything will be point queries via an index.

... so I'm not really sure how this will prove anything? Whoever has the best algorithm will "win" this contest, and it will have nothing to do with which version or even which database product is used.

 

[dk87]

What's to prove? Nothing. Except that some people like to challenge themselves. If you can't compete, don't.

 

[djbjr]

I want to play, can I sign up?

 

[djbjr]

Acutally, quick question

You say SQL

what about PL/SQL or a store function, are those not allowed?

 

[dbalearner]

djbjr,

I am no expert in computational algorithmics but not counting elliptical curve methods (which only show "probable primeness"), we're pretty much limited to either factoring or sieving. Both of these are number crunching processes that only ever make point queries against the data. With simple indexes present it isn't at all clear to me how *any* amount of database tuning or optimisation can do anything to help and IMO it will have nothing to do with which version or even which database product is used.

I think what we are possibly testing is internal functions not an aspect of query processing. So PL/SQL and any store function use will play an important part. Since I believe the Questionnaire is coming from MSSQL background, hence the use of term "SQL".

 

[dbomrrsm]

Functions Procedures Packages all allowed - as I said its not how you do it its the spped that counts.

BTW dk87 like you say "if you dont want to compete - dont"

We have two to mark the results so far santa and djbjr - are you both ok with marking the results of others code ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[djbjr]

fine with me....YOU'RE GOING DOWN DAVE!

actually I doubt that since you always answer my questions lol.

 

[djbjr]

Well I can do 10,000 in 5 seconds but am taking 7 minutes to do a million

ho hum

 

[carp]

My algorithm is running anywhere from 15.87 to 19.04 seconds, with most executions around 17.8 seconds. It's running on a Dell Latitude D600 with the following configuration:

Intel Pentium
1.4 GHz
1GB RAM
Windows 2000 SP5
Oracle 9.2.0.4.0

Are we supposed to post our code now, or wait until some specific time?

 

[SantaMufasa]

I haven't had a chance to write any code yet, but if Dave (Carp), djbjr, DBomrrsm, and any other contributors wish to e-mail me their code, I am happy to run the code all on the same machine for a level playing field.

Since DBomrrsm didn't place a specific time for a deadline, I propose that the deadline is e-mail timestamp of "11:59:59 Tek-Tips time" (i.e., 11:59:59 Eastern Standard Time).

Send contributions to "Dave at Dasages dot com".

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbomrrsm]

Post code and santa and djbjr will run and time

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[carp]

OK, so I'm technically late and out of the running, but I note there is no other code posted. So here's my entry.

-- setups
set serveroutput on size 1000000
set timing on
drop table primes;
create table primes(p number) STORAGE (INITIAL 3M);
-- start the test here
DECLARE
   TYPE t_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   l_tab   t_tab;
   l_indx  NUMBER;
   l_count NUMBER := 0;
   l_max   NUMBER := 1000000; -- SET THE UPPER BOUNDARY HERE
BEGIN
--
-- populate table
   FOR i IN 1..l_max LOOP
      l_tab(i) := 1;
   END LOOP;
--
-- mark off non-primes
   FOR i IN 2..FLOOR(SQRT(l_max)) LOOP
      IF (l_tab(i) = 1) THEN
         l_indx := 2 * i;
         WHILE (l_indx <= l_max) LOOP
            l_tab(l_indx) := 0;
            l_indx := l_indx + i;
         END LOOP;
      END IF;
   END LOOP;
-- load the primes
   FOR i IN 1..l_tab.count LOOP
      IF (l_tab(i) = 1) THEN
         INSERT INTO primes VALUES (l_tab(i));
      END IF;
   END LOOP;
-- count the primes
   SELECT count(*) INTO l_count FROM primes WHERE p = 1;
   dbms_output.put_line(l_count);
END;
/

 

[djbjr]

Results from Carp's code

78499

PL/SQL procedure successfully completed.

Elapsed: 00:00:50.34

 

[carp]

djbr -
What environment are you running on.
Also, are we just running the code once, or several times and taking the average? I have found there can be a wide difference (up to 25%) between runs.

 

[SantaMufasa]

I ran Carp's code and it resulted in:
[tt]
78499
Elapsed: 00:00:14.08
[/tt]
That's smokin' !!!

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

BTW, Dave, I don't want to futz with your code, but it would be nice if the table that holds the 78499 rows contained the primes instead of all "1s". Just a thought. (Is that a mod you would be interested in making to your code?)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[carp]

egads!
Of course, you are (as usual) correct, Dave! That is an artifact from when I was trying a different approach.

Please replace
INSERT INTO primes VALUES (l_tab(i));
with
INSERT INTO primes VALUES (i);
and all will be well!