Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello:
I have a set of data (32m records) that has a start and end years each record.
Using this for any type of calculation is very slow and very complicated. We need to know if a particular record feels in a particular year.
For this, we are trying to build a table that has a row that contain the key fields and a year. That year would be all the years between start and end. so if a particular records goes from 2003 to 2012, we will have a table that has
Key | Year |
---|---|
key | 2003 |
key | 2004 |
... | ... |
key | 2012 |
We alredy try two ways to do this, and all of them works. But they take more than 6 hours to run (a lot more).
One of them goes all the table all the time adding one year at a time for each key.
the other has all the diference between start and end, builds a table that has all the years, and then concatenate it to the original table. This works better than the other one, but takes around a minute for each start-end row (and they are about 3500 in a select disticnt)...
So any one has a better idea on how to build this table?
Thanks.
Typically it's done with something like this:
LOAD Key, StartYear + iterno() -1 as Year
FROM MyTableWith32MRecords.qvd (qvd)
while StartYear + iterno() -1 <= EndYear;
Typically it's done with something like this:
LOAD Key, StartYear + iterno() -1 as Year
FROM MyTableWith32MRecords.qvd (qvd)
while StartYear + iterno() -1 <= EndYear;
Id doesn't seems so heavy, I just tried with this script (only 4M, not 32) and it only took me 90 seconds, including rows generation and qvd store, the log is attached
or I didn't understand?
directory;
Characters:
Load Chr(RecNo()+Ord('A')-1) as Alpha, RecNo() as Num autogenerate 26;
ASCII:
Load
if(RecNo()>=65 and RecNo()<=90,RecNo()-64) as Num,
Chr(RecNo()) as AsciiAlpha,
RecNo() as AsciiNum
autogenerate 255
Where (RecNo()>=32 and RecNo()<=126) or RecNo()>=160 ;
Table:
Load
autonumber(TransID & TransLineID) as Id,
year(floor(today() + rand()*1000)) as YearFrom,
year(floor(today() + 1000 + rand()*4000)) as YearTo,
TransLineID,
TransID,
mod(TransID,26)+1 as Num,
Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,
Round(1000*Rand()*Rand()*Rand1) as Expression1
;
Load
Rand() as Rand1,
IterNo() as TransLineID,
RecNo() as TransID
Autogenerate 2000000
While Rand()<=0.5 or IterNo()=1;
STORE Table into Table.qvd (qvd);
YearTable:
NoConcatenate load
Id,
YearFrom + IterNo() -1 as Year
From Table.qvd (qvd)
While IterNo()-1 + YearFrom <= YearTo;
Characters << AUTOGENERATE(26) 26 lines fetched
ASCII << AUTOGENERATE(255) 191 lines fetched
Table << AUTOGENERATE(2000000) 4.003.506 lines fetched
YearTable << Table 31.376.589 lines fetched
My tries were heavy. Gysbert Idea is running and looks a lot faster than mine...