Jim, How about an application variable - a structure with all the "current" pages that is built from your table. Check the structure (structkeyexists( )) to see if the your ap is aware of the page - if it's not, do an insert, otherwise proceed to the update. I have used this method before for tracking stock quote history and caching daily data.
Mark -----Original Message----- From: Jim McAtee [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 10:34 AM To: CF-Talk Subject: Efficient Logging Application I need to log views of dynamically generated pages in a MySQL database. We're just going to be logging on a monthly basis, so this means that each page will have one log record per month. A couple of ideas we're kicking around: 1. Every time a page is accessed on the site, we'll call a CF tag that will: Look to see if a log record exists for the page If it does increment the 'views' column else create a new record, with views=1 Since the record creation is only done on a monthly basis, this means that the vast majority of tag calls will simply do an update, yet must still do two queries, the first to see if the record exists. 2. Create all of the log records before-hand, on the first of each month. Biggest problem with that is that new items are constantly being added to the database, so new pages are possible. Since items are pulled from a couple dozen tables, with a couple dozen distinct maintenance applications, we'd need to modify those applications to add log records as new records are created. That's a lot of work. 3. Instead of updating a monthly log record directly, we thought we might just do an insert into a table of a new record for every single page view. At the end of the day, all the daily records would then be 'rolled' into the monthly logs. This way, no SELECT query is necessary. Not too sure of the relative efficiency, however, of an INSERT every time versus a SELECT, then an UPDATE. Any ideas or insights would be appreciated. Jim _________________________ _________________________ ____________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ______________________________________________________________________ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
