To read the article online, visit http://www.4GuysFromRolla.com/webtech/092700-1.2.shtml

An Ingenious Banner Rotation System, Part 2

By Bill Graziano


  • Read Part 1

  • In Part 1 we looked at the features for the banner rotation system. In this part we'll examine the administration page!

    Updating an Ad
    My administration page is called BANNER_Admin.asp. The page is a relatively basic Add, Edit, List page that calls a single stored procedure for all it's database work. It has almost no error checking since I'm the only user for it. It only has one quirk that really needs explaining:

    fHTML = Replace(fHTML, "'","''")

    That line of code converts all single quotes in an HTML string to two single quotes. SQL Server uses single quotes to enclose text strings. When you are inserting a record into SQL Server that contains a single quote (or apostrophe) inside a string, you need to replace it with two single quotes. You can read more about that at http://www.sqlteam.com/item.asp?ItemID=293 or at http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=27.

    This ASP page calls a stored procedure called spBANNERPostAd01. A quick word on my naming conventions. All stored procedures start with sp. This is a throwback to the days when SQL Server didn't break objects up into those nice handly folders. I always have a word or abbreviation to identity which subsystem this object belongs to. All these objects use BANNER to identify them. Next is the verb Post to describe what I'm doing and then Ad which describes what I'm posting.

    I attach a two digit version number to each stored procedure. This gets updated whenever the parameters I pass to the stored procedure change. For example, if I add another parameter to a stored procedure I'll create a copy with an 02 at the end. I always leave the 01 in place. Sometimes I can just modify the 01 to call the 02 and pass it the new information. Most times I just modify the 01 to return a nice error condition that my code can handle gracefully if I didn't manage to change it. This also makes it easier to search through code and find what needs to be updated.

    spBANNERPostAd01 takes each column as a parameter. If the AdId = 0, it will do an insert, otherwise it will update the passed in record. At the end it updates the WeightScaled. This is where the cool SQL Server stuff happens. This part of the stored procedure looks like this:

    declare @Counter smallint
    set @Counter = 0
    
    update BANNER_Ads
    SET @Counter = WeightScaled = @Counter + Weight
    
    Update BANNER_Ads
    SET WeightScaled = NULL
    Where Weight <= 0
    

    The second UDPDATE statement clears the WeightScaled field if the ad is not assigned a weight. This will prevent it from being selected. You could probably make that part of the first UPDATE statement if you really wanted to. I chose not to for simplicity sake. It just makes the code easier to read.

    The first UPDATE statement assigns the scaled weight. This is the value that I'm going to use to randomly select an ad. It looks a little odd with two equal signs in it. It goes through BANNER_Ads and updates each record. It sets WeightScaled to the variable @Counter plus the value of the column Weight. Then it assigned this same value back to the variable @Counter. You can find out a little more about this at http://www.sqlteam.com/item.asp?ItemID=765.

    It's probably easier to show you an example. In our example, I'll assume there are three ads. This is what the table will look like after the update has run.

    AdIdWeightWeightScaled
    122
    235
    3611

    When I select my random number from 0 through 11, any value from 0 to 2 will select AdId=1. Any value greater than 2 through 5 will choose the second row and values greater than 5 through 11 will choose the last row. As you can see each row's Weight affects how often it is chosen.

    Any time you update an Ad, this stored procedure will update every record in the table. While that might concern you, it shouldn't be an issue. The Ads table is relatively static. It probably doesn't get updated on a regular basis. It is usually updated by a webmaster who is more tolerant of a slow query knowing what is going on behind the scenes. And finally, updating records like this is just plain fast. Bulk updating records in a single UPDATE statement is 10 to 100 times faster than writing an ASP page that updates each record one at a time. This table is probably cached in memory anyway since it's being queried for every single web page displayed and that will make the UPDATE statement even faster.

    In Part 3, the final part, we'll look at the most important part - actually displaying the advertisement banners!

  • Read Part 3


  • Article Information
    Article Title: An Ingenious Banner Rotation System, Part 2
    Article Author: Bill Graziano
    Published Date: Wednesday, September 27, 2000
    Article URL: http://www.4GuysFromRolla.com/webtech/092700-1.2.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers