Private Function getRandomNumber _
(ByVal low, ByVal high) As Integer
getRandomNumber = New System.Random(). _
I just grabbed this. Just create a data set and for the high put in
for low put in 1. It will pick out a random number, then get that entry out from the set. I sort of forgot if you need to do the low at 0 and the high at count -1. I forgot if it acts like an array in that respect or not. But play with it.
I have the structure setup but I'm stuck at actually outputting that random numbered record. Does it need to be bound with any controls if so what's the syntax for binding just that particular record? With ADO you could .Move to the record but I don't know how that's implemented now on ADO.NET.
Last edited by Eulibrius7; 05-15-2004 at 08:56 PM.
that should select that row from the data set. Now in your select statement you are going to be selecting several feilds, then putting them into the data set, quote is just a feild name I made up, change that to whatever feild your quotes are.
0 will be the first row in your data grid, so start the random statement at 0.
But, if that numberic feild is incimenting and quotes from the middle might have been deleted you would have gaps, that is why I suggested select them all then grab from the data set. If you do it with a select statement and he removes some dusty old quotes and puts in new ones, it would cause some angry little error messages randomly when one of the missing quotes happens to be picked and is not there. But if you do not use an incimenting feild and just add the quotes manually and an control it yes, your method would be better.
CREATE PROCEDURE GET_RANDOM_QUOTE
@quote AS varchar(8000) OUTPUT
SET @quote = (SELECT TOP 1 quote FROM quotes ORDER BY NEWID())
On well indexed tables, the performance is generally MUCH worse than my previous code. On the plus side, there's no requirement of a numeric primary key, or even one at all.
Another benefit is that you can use it to grab a different number of random results than 1 without significant additional performance penalties. You could morph the previous code to use a temporary table and some additional logic to build your way up to X number of distinct rows, but there will be a price to pay in terms of performance, which WILL make it the worse choice under certain circumstances (the smaller the table row count and the larger the number of rows pulled, the more likely it is to be suckier... however, it'll still win handily when pulling few enough rows from a table with a large enough row count).
On properly indexed tables, my previous code has equivalent performance for roughly a hundred rows and roughly 20,000 rows. The code above goes from about 50 times slower to over 150 times slower, respectively. (Individual results may vary.)
The reason it sucks increasingly harder as the row count of the table goes up is that the ORDER BY takes much more effort (barring certain special cases, IIRC, sorting performance is at best O(n lg n), depending on the sort used) and MUST be performed, as the number is generated on the fly and is not strictly increasing nor strictly decreasing. The previous code can take advantage of the index on the table to avoid having to sort at all, which is why the performance is equivalent over an order of magnitude increase in the row count. (All it has to do is look up the max autonum using the index, calculate a value, find the first autonum in the index >= to that value, look up the row from the index and return the requested column.)
On a non-indexed table, it might be possible for the code above to beat my previous code, as the previous code would have to search all the rows for the max autonum, grab all the rows with autonum >= to the calculated value, then ORDER BY their autonum, and then return the requested value from the first row.
Last edited by CardboardHammer; 05-25-2004 at 10:44 AM.