Random Quote- ASP.NET&SQLDB

[Eulibrius7]

I would like to implement a Random Quote that will appear at the bottom of every page of my site using ASP.NET and SQLDB. Any ideas (sample code) on how to begin?

[PeOfEo]

   Private Function getRandomNumber _
      (ByVal low, ByVal high) As Integer
      getRandomNumber = New System.Random(). _
         Next(low, high)
   End Function

I just grabbed this. Just create a data set and for the high put in
ds.Tables("yourtable").Rows.Count
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.

[Eulibrius7]

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.

[PeOfEo]

well
dim yourvar as integer = rand.Next(0, ds.Tables("yourtable").Rows.Count)

should give you the number you want.

variable = ds.Tables("yourtable").Rows(yourvar).Item("quote")

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.

[CardboardHammer]

If you can use a stored procedure, that's the way to go. At any rate, you shouldn't use a dataset; you only need a single value, not a set of values.

CREATE PROCEDURE GET_RANDOM_QUOTE 
@quote AS varchar(8000) OUTPUT
AS
SET @quote = (SELECT TOP 1 quote 
  FROM quotes 
  WHERE pk >= RAND() * (SELECT MAX(pk) FROM quotes)
  ORDER BY pk
GO

Assumption: pk is a numeric primary key and is always >= 0

If there are no gaps in the sequence of values for pk and pk is integral, there are optimizations that can be made to get rid of TOP and the ORDER BY.

EDIT: corrected an "autonum" to "pk" in the code above

[PeOfEo]

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.

[CardboardHammer]

Look again

As written, it will work given the assumption that the minimum pk is 0. I only mentioned there were possible optimizations if the pk sequence were gapless; I didn't include them.

[PeOfEo]

woops... neglected to see what your where statement was doing.

[CardboardHammer]

Yet another way to do it, as inspired by http://www.microsoft.com/sql/techinf...eryresults.asp

CREATE PROCEDURE GET_RANDOM_QUOTE 
@quote AS varchar(8000) OUTPUT
AS
SET @quote = (SELECT TOP 1 quote FROM quotes ORDER BY NEWID())
GO

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.