Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Getting random records

 
Hussein Baghdadi
clojure forum advocate
Bartender
Posts: 3479
Clojure Mac Objective C
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi.
Suppose my database has 1000 recored.
How can I get ten random records from a database -to be displayed , say in the index page- ?
 
Purushoth Thambu
Ranch Hand
Posts: 425
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Which database are you using?. If you happen to use Oracle then it's simple


sometimes you may not get random rows if SQL Plan happens to use Index. You can try this
 
Scott Selikoff
author
Saloon Keeper
Posts: 4015
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could always use the random api in java to select an id from x to y and then use that id to fetch the record from the database.
 
Mahendra Shelke
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But i suppose using the SQL function which exists in almost every DBMS/RDBMS would be a far more appropriate option rather than using any java api for that purpose.
 
Hendy Setyo Mulyo
Ranch Hand
Posts: 219
Firefox Browser Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
use the Java API random class to generate the random ids then query the record from database based on them.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34672
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Mahendra Shelke:
But i suppose using the SQL function which exists in almost every DBMS/RDBMS would be a far more appropriate option rather than using any java api for that purpose.

Which function does this refer to?
 
Purushoth Thambu
Ranch Hand
Posts: 425
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't understand how selecting random records through java is going to be effective. With java you need to

- Generate the Random numbers (What numbers?, How is it going to be used? You need to find out the domain boundary (max row or max value of numeric column)
- Bind these values to the SQL Query using IN operator. (Define SQL with 10 parameters and bind the values)
- execute query.

Isn't this approach bit expensive compared to doing it through DB?
[ October 21, 2006: Message edited by: Purushothaman Thambu ]
 
Scott Selikoff
author
Saloon Keeper
Posts: 4015
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes/No, if you have well structured data we some predefined information, the database call that actually retrieves the results will be a lot faster. Database calls are almost always more expensive than java calls so if you can reduce the database calls easily, do it. If the range is difficult to define and the data is scattered that's another story (although I imagine there's probably a clever way to do this in java still).
 
Hussein Baghdadi
clojure forum advocate
Bartender
Posts: 3479
Clojure Mac Objective C
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm using MySQL 4.1
Actually, I was thinking about getting a random generated number, but is it the best way ?
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd prefer a sql solution, too, but would have to rely on db experts to hand me one.

Java approaches ... as mentioned above generating random keys in Java will only work if you know the database key distribution or generation algorithm. If you know the db has keys 1 through 1000 it's not too hard. If the db key is SSN, it will take a while for a RNG to get even one hit.

Check the performance of select the key from all rows, build an array, shuffle the array, query on the first n keys. This won't scale forever.
 
Purushoth Thambu
Ranch Hand
Posts: 425
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am not mySQL guy, however when I looked at 4.1 doc's I see note on bug fix to make

work. Even if this doesn't work then you can use LIMIT functionality of MySQL to randomly pick the record.



You need to bind ? with random number generated from Java (if you cannot find some other way to get this done on DB side).

PS: I am not sure what will be the cost of ORDER BY RAND() SQL will be. Since you need just 10 records if the SQL does a full scan then you need to compare between fetching one record at a time and RAND() method. It works differently in Oracle when you use ROWNUM < ? sort of queries.
[ October 23, 2006: Message edited by: Purushothaman Thambu ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic