Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generating Random Results - SQL School Video Expand / Collapse
Author
Message
Posted Wednesday, November 26, 2008 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 10:52 AM
Points: 6,771, Visits: 1,830
Comments posted to this topic are about the item Generating Random Results - SQL School Video

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #608874
Posted Thursday, January 15, 2009 5:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 3, 2013 1:10 AM
Points: 1, Visits: 40
oooh, great tip, thanks.:)
Post #636996
Posted Thursday, January 15, 2009 8:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 1, 2012 1:18 PM
Points: 110, Visits: 261
Ordering by newid() is pretty lackluster in the performance department, I assumed this article would cover some great new ways to randomize data

I've gotten by in a variety of cases by using row_number() over some indexed column(s), and predicating to some random offsets. This ends up being significantly better than the newid() approach, but the perf is still not that great if you have huge tables and some of your random offsets are deep into the table.

The other main approach I've seen is using tablesample (i.e. http://www.mssqltips.com/tip.asp?tip=1308), this may work better on very large tables.

For the common variety of requirement of fetching some random images in a website image gallery, I ended up creating a table to contain a randomized list of keys to the data rows. So if each image has an identity column as the PK, the randomized table contains a list of these ids in random order. You pay the perf to order by newid() one time with a nightly job, then each request simply chooses a random entry point into the randomized table and queries out the next N ids. Since the randomized table has contiguous ids it is cheap to seek into - where id is rand between 1 and max(id).
Post #637256
Posted Saturday, January 17, 2009 3:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:43 AM
Points: 5,291, Visits: 1,377
Nice one ...:)


Post #638671
Posted Thursday, January 22, 2009 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 9:49 AM
Points: 5, Visits: 91
In the video example, wouldn't the same 20 people be selected, just returned in a different order? If you had a table with all the names, you would need to first have a sub query select all the records and add a uniqueidentifier (newguid()), then from that set select the top 20 and order by the uniqueidentifier.

Post #641767
Posted Thursday, January 22, 2009 9:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 1, 2012 1:18 PM
Points: 110, Visits: 261
dray (1/22/2009)
In the video example, wouldn't the same 20 people be selected, just returned in a different order? If you had a table with all the names, you would need to first have a sub query select all the records and add a uniqueidentifier (newguid()), then from that set select the top 20 and order by the uniqueidentifier.


When you have TOP and ORDER BY in the same statement, the ORDER BY is logically applied before the TOP.
From the entry on TOP (http://msdn.microsoft.com/en-us/library/ms189463.aspx)
If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.
Post #641818
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse