Under idea #2: Can you make an adjustment to the content production side of the fence, so when a content producer does a new page insert a corresponding insert is also done to your log db? That would keep the two tables synch'd and you'd be down to one query doing UPDATEs.
--Matt Robertson-- MSB Designs, Inc. http://mysecretbase.com > -----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 > ______________________________________________________________________ Why Share? Dedicated Win 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=coldfusionc 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
