Speeding up a stored proc.... - SQL Server Forums

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)

Speeding up a stored proc....

Author

Topic

interclubs
Yak Posting Veteran

63 Posts

Posted - 2002-08-04 : 11:17:08

I am trying to randomly select 10,000 emails from a table, but the stored proc is taking a long time to process (there are about 3.5 million rows). Anyone have any ideas on speeding it up:

set nocount on

Create Table #RandomRows (EmailID int IDENTITY (1,1),ID int,email varchar(200) )

declare @intCounter int
set @intCounter = 0

WHILE @intCounter < 10000
BEGIN
INSERT #RandomRows
SELECT id, email FROM emails WHERE ID = (round((rand() * 264), 0) + 1)
set @intCounter = @intCounter + @@rowcount
End
select * from #RandomRows
DROP TABLE #RandomRows
set nocount off

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-04 : 11:47:31

Have you looked at this:

http://www.sqlteam.com/item.asp?ItemID=8747

You could easily do this as a single SELECT statement using either TOP 10000 or SET ROWCOUNT 10000 to limit the number of rows returned.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-04 : 11:56:52

Here's four approaches in one thread - hopefully one will be of use

[url]http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&safe=off&threadm=84qim3%24dc7%241%40nnrp1.deja.com&rnum=1&prev=/groups%3Fnum%3D20%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26safe%3Doff%26q%3Dselect%2Brandom%2Brows%2Bgroup%253Amicrosoft.public.sqlserver.*[/url]

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources