www.webdeveloper.com
Results 1 to 9 of 9

Thread: Random Quote- ASP.NET&SQLDB

  1. #1
    Join Date
    Apr 2004
    Posts
    27

    Lightbulb Random Quote- ASP.NET&SQLDB

    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?
    Last edited by Eulibrius7; 05-15-2004 at 02:36 PM.

  2. #2
    Join Date
    Nov 2002
    Location
    Auburn, AL
    Posts
    9,222
    Code:
       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.
    Last edited by PeOfEo; 05-15-2004 at 04:15 PM.

  3. #3
    Join Date
    Apr 2004
    Posts
    27

    Output

    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Auburn, AL
    Posts
    9,222
    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.

  5. #5
    Join Date
    Nov 2003
    Posts
    655
    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.

    Code:
    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
    Last edited by CardboardHammer; 05-25-2004 at 10:42 AM.

  6. #6
    Join Date
    Nov 2002
    Location
    Auburn, AL
    Posts
    9,222
    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.
    Last edited by PeOfEo; 05-19-2004 at 03:06 PM.

  7. #7
    Join Date
    Nov 2003
    Posts
    655
    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.

  8. #8
    Join Date
    Nov 2002
    Location
    Auburn, AL
    Posts
    9,222
    woops... neglected to see what your where statement was doing.

  9. #9
    Join Date
    Nov 2003
    Posts
    655
    Yet another way to do it, as inspired by http://www.microsoft.com/sql/techinf...eryresults.asp

    Code:
    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.
    Last edited by CardboardHammer; 05-25-2004 at 10:44 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles