Anybody knows a faster/optimized way to do this? (Autogenerate stuff)

[marcel_olmo]

Hey guys, I have to do a demo with millions of records of a "selling table".

My code works perfectly. But it does autogenerates, and they are very slow if you have to generate a big bunch of data.

I have to do this script for 10 million records, and if I trust my computer, it takes 3 days of generating data. 😞

Here is what I have, if anybody knows a way to do it faster, I'm gonna thank you a lot :

set nClientes = '1000000';

set nProductos = '5000000';

set nCabeceras = '10000000';

set nLineas = '10';

FOR k = 0 TO ($(nCabeceras) -1)

FOR i = 0 to ($(nLineas)-1)

LineasVentas:

load

mod(2000 + ( $(k) ),$(nCabeceras)) + 2000 as [Nº documento],
10000 + ($(i)) as [Nº línea],
(mod(2000 + ( $(k) ),$(nCabeceras)) + 2000) &'-'&( 10000 + (($(i))) ) as [Nº documento-Nº línea],
mod(10000 + ( $(k) ),$(nClientes)) + 10000 as [Venta a-Nº cliente],
date( today() - mod($(k),500)) as [Fecha registro],
mod(1000+ (rowno()-1),$(nProductos))+ 1000 as [Nº producto]

autogenerate 1;

NEXT i

NEXT k

store LineasVentas into LineasVentas.qvd;

[#2 · Accepted solution]

Hi Marcel,

You can create a table temporary to generate the values of variables "i", "k". After use this table to generate your data.

set nClientes = '1000000';
set nProductos = '5000000';
tabTemp:
LOAD (100 - RowNo()) AS k
AUTOGENERATE 10000000;
Lineas:
JOIN(tabTemp)
LOAD (10 - RowNo()) AS i
AUTOGENERATE 10;
LineasVentas:
LOAD
 mod(2000 + (k),$(nCabeceras)) + 2000 as [Nº documento],
 10000 + (i) as [Nº línea],
 (mod(2000 + (k),$(nCabeceras)) + 2000) &'-'&( 10000 + (($(i))) ) as [Nº documento-Nº línea],
 mod(10000 + (k),$(nClientes)) + 10000 as [Venta a-Nº cliente],
 date( today() - mod(k,500)) as [Fecha registro],
 mod(1000+ (rowno()-1),$(nProductos))+ 1000 as [Nº producto]
RESIDENT tabTemp;
DROP TABLE tabTemp;
store LineasVentas into LineasVentas.qvd;

[marcel_olmo]

Thanks Eduardo, this is what I was looking for. Now it works nice and fast.

Thank you everyone who tried to help me.

In addition, there is a little mistake I've fixed. And this is the correct "tested" solution :

Set nClientes = '1000000';
Set nProductos = '5000000';
Set nCabeceras = '1000000';
Set nLineas = '10';

tabTemp:
LOAD (100 - RowNo()) AS k
AUTOGENERATE $(nCabeceras);
Lineas:
JOIN(tabTemp)
LOAD (10 - RowNo()) AS i
AUTOGENERATE $(nLineas);

LineasVentas:
LOAD
mod(2000 + (k),$(nCabeceras)) + 2000 as [Nº documento],
10000 + (i) as [Nº línea],
(mod(2000 + (k),$(nCabeceras)) + 2000) &'-'&( 10000 + (i)) as [Nº documento-Nº línea],
mod(10000 + (k),$(nClientes)) + 10000 as [Venta a-Nº cliente],
date( today() - mod(k,500)) as [Fecha registro],
mod(1000+ (rowno()-1),$(nProductos))+ 1000 as [Nº producto]
RESIDENT tabTemp;

drop table tabTemp;

store LineasVentas into LineasVentas.qvd;