Really depends on the queries running over the 5 million record table. IE if you were doing select * on a regular basis, I'd freak as well :)
I generally archive my logfiles as plain text, and gzip them. I keep the daily analysis reports in a database. For the situation, I'd be inclined to either archive data to free diskspace, dump the sql db, archive it on backup tapes or something simmilar, and then just query trim it so that any records more then X days/weeks/months old were tossed and restore from the backups if you needed older data for some reason. Beyond just guessing if I knew exactly why he freaked we'd have a better starting point. Load intensive query? poor query structure? disk space concerns? Doesn't realise that he's using a real database, not Access? Alternately of course... if you are using access database with 5 million records and a dba, I'll loan him the pickhandle to beat whoever developed it with... hell I'll probably even hold them while he does the bludgeoning. ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, March 19, 2003 3:46 PM Subject: [cfaussie] Re: OT: DB Archiving - performance > On Wed, Mar 19, 2003 at 04:12:32PM +1100, Hickman, Matt wrote: > > > We recently archived 5million records in this table to csv file as the DBA > > was freaking out about such a large table. > > Since you've got a DBA I'd assume you're running a serious database, not > something like Access. If that's the case, I don't understand the concern. > > > We noticed no performance issues on the user end and not sure how the server > > was coping but we had no outages for ages either. > > In that situation I'd recommend making an archive file with the same > specs as the original. Then ensure that queries to it hit appropriate > indexes. Then periodically move the contents of the live log file to > the archive. Your DBA should be able to setup import routines that > drop the indexes for the bulk move, which would speed things up. > > Without knowing other factors, this sounds straight out of the > MSCE school of database management. > > Wait a second, here's a thought. What's the record size? > He might be running into system file size limitations. > > 100 characters * 5M records = .5G database > 1000 characters * 5M records = 5G database > > All that's needed here is to switch to a database that can > spread tables over multiple files, or move the server to > a 64 bit platform :) > > Or, more practically, split the data up into yearly/quarterly > archives. > > Paul Haddon > Technical Services Manager > Formstar Print Technologies > > > --- > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > To unsubscribe send a blank email to [EMAIL PROTECTED] > > MX Downunder AsiaPac DevCon - http://mxdu.com/ > --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
