Combine records of Current and History

[alisaif]

Hi,

I have a stock file which contains at least 1 million records from June, 2009. It is very bulky and taking too much time for any inquiry about the products. I want to send all these records in history file and want to keep only current year (2014) records in order for fast processing.
Suppose current file is in k:\newfas\stock.dbf {01/01/2014 to 17/10/2014} and want to copy the same name file in history like k:\newfas\history\stock.dbf {01/06/2009} to {31/12/2013}.

Now, I want to fetch records from {01/08/2013} to {31/05/2014} from both the files using SQL Select. How can I do so?
Structure of Stock.dbf is as follows:

1. Reference c(8)
2. cDate d
3. Icode c(10)
4. Qntymaj N(10,3)
5. Qntymin N(10,3)
6. Warehouse c(5)

dt1 = {01/08/2013}
dt2 = (31/05/2014}

Please guide..

Thanks

Saif

 

[Olaf Doschke]

select * from k:\newfas\stock.dbf ;
union ;
select * from k:\newfas\history\stock.dbf ;
where cdate between dt1 and dt2

You'll not be faster, rather slower with this splitting of data. You'd only profit from a smaller table with just current data, if you mainly only work on current data.

So partition the data in a way all data you need for most of your queries is in one dbf. What goes in the archive is what you really only need sparsely. So you could split at 2012 or even 2011 instead of 2013.

Bye, Olaf.

 

[alisaif]

Thanks for the pointing towards the realities and suggestions.

Saif

 

[JRB-Bldr]

I might add a comment about...

I want to send all these records in history file and want to keep only current year (2014) records in order for fast processing.

Everyone's needs differ, but generally that idea works when you are already most of the way through a current year (like we are now) in which case most (if not all) of the 'recent' records (depending on how you define 'recent') would be contained within the same calendar year.

But it does not work so well when you are near the beginning of a current year where a number of the 'recent' records might be in the previous calendar year.

You might instead want to consider using a 'sliding time window' that goes back a certain number of months or days instead - perhaps something like the last 12 months regardless of calendar year.

If you did that, then when you are near the beginning of a current year you could still quickly access records which were relatively recent, but just happened to be in the previous year.

Good Luck,
JRB-Bldr

 

[alisaif]

Thanks

Saif