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 <lester@...> 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]