Plz test it for execution times

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Plz test it for execution times

Author

Topic

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-05 : 02:41:37

Taken from there >> http://weblogs.sqlteam.com/davidm/posts/412.aspx#Feedback
Searching for Prime Numbers

CREATE TABLE Numbers(Number INT NOT NULL PRIMARY KEY CLUSTERED)
GO
DECLARE @i INT
SET @i = 1
WHILE @i < 10001
BEGIN
INSERT Numbers(Number) values (@i)
SET @i = @i + 1
END
GO

--DavidM solution
SELECT X.Number as Primes
FROM Numbers N CROSS JOIN Numbers X
WHERE X.Number%N.Number != 0 AND X.Number > 1
AND N.Number > 0 AND N.Number < X.Number and X.Number%2 !=0
GROUP BY X.Number
HAVING (X.Number - Count(*)) = 2

--my query
select * from numbers t where not exists(select 0
from numbers tt where t.number%tt.number=0 and
tt.number<t.number and tt.number>1)

On my machine the 2nd query 5 times faster than the 1st one.

The below query 80 times faster than the 1st query:

select * from numbers t where not exists(select 0
from numbers tt where t.number%tt.number=0 and
tt.number<=sqrt(t.number) and tt.number>1)

What's wrong with my sql server?
LOL... Owais!! I know you are here :) What can you say on this?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-05 : 04:39:54

???
Why wouldn't you expect that?
The first two queries are donig a lot of duplicate checks that the last is not.

how about
CREATE TABLE Numbers(Number INT NOT NULL PRIMARY KEY CLUSTERED)
GO
DECLARE @i INT
SET @i = 1
WHILE @i < 10000
BEGIN
SET @i = @i + 1

INSERT Numbers(Number)
select @i
where not exists (select *
from numbers tt where @i%tt.number=0 and
tt.number<=sqrt(@i))

END
GO
select * from Numbers

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-05 : 05:41:48

LOL, Nigel,

that's the case.. I would expect this result, but I was confused
by today's David's response (see link above) to my suggestion.

PS Of course your code is very good but here I am talking about
performance of different retrieving queries.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-05 : 05:43:21

oooooh...i feel woozy now