Jim, Yes those are all issues. 10,000 records doesn't seem like too much (depending on your hardware and how managed your code is) however. My structure of symbols grows from 0 to around 18,000 routinely throughout the day. For you, the first time it is built it will be slow, but after that not bad (I'm assuming a dedicated server and sufficient ram). Also, I was suggesting that you go ahead and run an update against the database rather than update the value into the structure. If the ap crashes or CF is restarted, the only thing you would need to do to get back on track is rebuild the structure from your database table. The structure basically functions as a place holder for key names - the values are irrelavant (in my scheme). I'm was just trying to get you down from 2 queries to 1. As far as locking goes, I simply use a read-only cflock around my check of the structurekeys, and an exclusive lock when adding a new key (in the case of a page not found). It works very well for us (can't speak to others).
One thing that occurs to me (now) is to examine how "structkeyexists" works. does it start at the first key created? If that is the case and you are creating keys from a query, then sorting the query to put your most commonly accessed pages at the top may give you an added boost. Perhaps Ray could inform us as to how that function works. Mark -----Original Message----- From: Jim McAtee [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 02, 2002 11:17 AM To: CF-Talk Subject: Re: Efficient Logging Application Mark, That's a possibility. I'd have to evaluate how much memory that might require. There could easily be 10,000 distinct records. I think I'd either need to just keep only a day's worth of data within this application structure, or else write it out periodically to disk, just in case the CF server crashes or needs to be restarted. Also, I wonder about the issue of needing to do an exclusive application scope lock on this structure for each page view and how it might affect a very busy site. Jim ----- Original Message ----- From: "Mark A. Kruger - CFG" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, January 02, 2002 1:39 PM Subject: RE: Efficient Logging Application > 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 > _________________________ _________________________ ____________________ 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 ______________________________________________________________________ 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
