Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Server Puzzle:Prime Numbers 9

Status
Not open for further replies.
SQLDenis

SQLDenis

Programmer
Oct 1, 2005
5,575
0
0
US
Why wait until Friday
Okay here we go
This is what needs to be accomplished
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 20th (Next Friday)
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


template below

Code: 
declare @d datetime,@d2 datetime
select @d = getdate()
declare @intPrimeNumberCount int
select @intPrimeNumberCount =0

/*
All other code goes here
*/

select @d2 = getdate()
select (@d2 -@d)as TimeElapsed,@intPrimeNumberCount as PrimeNumberCount

So this is a real SQL puzzle and no admin stuff here
Let me know what you think and good luck


Denis The SQL Menace
SQL blog:
Personal Blog:
 
Sort by date Sort by votes
Table @variables are allowed or not?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Let's make an emphasis on "put prime numbers into a table". Answers using "pass-through" counting (without table) are not valid. OK?

Regarding benchmarking, maybe we should post average from 5 (five) attempts or so - with 1/100s accuracy.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Here, count [smile].

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
1...2....3... [red]78498[/red] whew. I ran out of fingers and toes.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Fastest time wins? Man, I hope my big Unisys 32-way monster gets here in time ;-).
 
Upvote 0 Downvote
Hey this isn't fair. I ran out of fingers at toes at 10. If George didn't run out until 78498 he's got a huge advantage. :)
 
Upvote 0 Downvote
That's why I type faster than most. [bigsmile]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Code: 
create table primes
(
mynums int
)

insert into primes values (2)
insert into primes values (3)
insert into primes values (5)
insert into primes values (7)


...my code could take some time to type :p ...haha thats if i also remember all the primes :)

selecy count(*) from primes
 
Upvote 0 Downvote
Damn I get 78499 Rows in 55:22 minutes on crappy 'Corporate Standard' IBM centrino 1GB Ram Laptop
Now where do I have a extra number??
I see the link starts with 2 i start with 1
Oh well time to speed the stuff up, just wanted to make sre the count was correct

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
I just got 78498 records in 3 minutes 34 seconds. I have a couple ideas that may make this faster, too.

I have a P4 2.8 GHz machine with 1 GB ram.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Oh man, that shaved a third of the time off. hehehe this is kinda fun. I'm now down to 2 minutes 9 seconds. Oh wait... clickity click click.... tap tap tappity tap tap. Ah... that's better 1 minute 25 seconds. Hold on a minute! This should make it better... Yes... That is better! 45 Seconds. Just another idea or 2.....

OK, I'm probably done. I got it down to 34 seconds. It takes 16 seconds just to generate my number table.

By the way, here's the basic structure for my Stored Procedure. It may be helpful if others follow a similar structure.

Code: 
Alter Procedure GetPrimeNumbers_[red]gmmastros[/red]
As
SET NOCOUNT ON
Declare @StartTime DateTime
Set @StartTime = GetDate()

Create Table #T ...

[green]-- code here[/green]

Select Number From #T Order By Number

Drop Table #T

Select Convert(Decimal(10,5), DateDiff(Millisecond, @StartTime, GetDate())) / 1000

go
GetPrimeNumbers_gmmastros

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
What are you running? Toaster at 110 volts? [wink].

I got 35 seconds with vanilla Eratosthenes. And table generation takes 4.5 seconds.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Ok, ok. I hear ya.

Me and my toaster just got it down to 15 seconds.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Very fun problem. Making me think

I have ~34 seconds with ~6 seconds of table generation.
 
Upvote 0 Downvote
I'd like to propose one more rule - specifically for this puzzle.

By definition prime number is positive integer divisible without remainder only with 1 and itself. The only two numbers that automatically pass that rule are:

- 1 - because 1 and itself is the same
- 2 - because there are no other integers between 1 and 2.

3 5 7 11... don't. So if someone hardcodes these values without calculating 'em previously, is that considered illegal?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
312
Volvere
V
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
275
pjw001
pjw001
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
195
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
T-SQL Dialog Box
Replies
8
Views
178
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
215
SaltyTheFrog
SaltyTheFrog

Part and Inventory Search

Sponsor

Back
Top