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.
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 ]
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).
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.
A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Joined: May 24, 2003
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 ]