I'm planning to do historical data archive too, and still searching what the best strategies to use. For me, it is best to separate the historical table into it's own table, because the historical table should not have any index. If i put the historical table into separate database, that will make it harder for me to handle two database update that almost happen on every transaction. That what i think of course
On Wed, Jul 31, 2013 at 3:43 PM, Lester Caine <[email protected]> wrote: > ** > > > Aldo Caruso wrote: > > My question is the following: should both tables be merged into a > > single table, with an additional field marking historic records ? Will > > the queries on this table be slower than having them separated ? > > I have a number of systems running which record 'footfall' so all the new > records are purely today and views on the database using today's date > provide > the 'write' access. We have approaching a million records on some of the > sites, > and only yesterday a customer commented on just how fast they can run > reports > even covering a year at a time. Your archive process may be a little more > complex, needing to identify records from different days as historic? But > I'd > still include a date with the historic flag to make that index a little > more > selective. That said - nowadays I could probably do a UNION on two tables > and > still be able to provide the historic activity view for a caller with > today's > activity - didn't have UNION when we started gathering data :) > > -- > Lester Caine - G8HFL > ----------------------------- > Contact - http://lsces.co.uk/wiki/?page=contact > L.S.Caine Electronic Services - http://lsces.co.uk > EnquirySolve - http://enquirysolve.com/ > Model Engineers Digital Workshop - http://medw.co.uk > Rainbow Digital Media - http://rainbowdigitalmedia.co.uk > > > -- Iwan Cahyadi Sugeng Interaktif Cipta Lestari [Non-text portions of this message have been removed] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
