October 30, 2003 at 12:03 pm
I have the following query:
SELECT TOP 1 site_id
FROM tblSites
WHERE displayLink = 1
ORDER BY NEWID()
tblSites has approximately 140k rows. This is the best way I know of to grab a random record, but the problem lies with the fact that it takes a little over half a second (500 ms) to execute. When I take out the ORDER BY clause it is extremely faster. Is there any other way I can grab a random record from this table that might be a little faster? I know 500ms is not slow to some of you, but this query gets executed extensively and I want it to run as quick as possible. Any suggestions would be appreciated.
Thanks,
Shawn
October 30, 2003 at 1:55 pm
If there isn't one, put a nonclustered index on just Site_Id. I don't think you'll find a faster way to do this than what you're already using, unless you can accept a "less random" result.
--Jonathan
--Jonathan
October 31, 2003 at 12:46 am
This might be of any help
http://www.sqlservercentral.com/columnists/bknight/randomvalues.asp
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 31, 2003 at 12:47 am
The order by forces SQL Server to create a temptable. If your column NEWID is unique and incremental, why don't you generate a random number within it's range,pass it into to your Procedure and then add it to your predicate.
SELECT TOP 1 site_id
FROM tblSites
WHERE displayLink = 1
and NewID=@Random
October 31, 2003 at 7:29 am
Thanks for the link, Frank.
leightonsimsaj - NewID() is a function, not a column name.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply