Creating our QVD's

[hopkinsc]

Hi,

We are having problems getting our QVD's created as our data has got too big. I have been asked to try and find the best way to load in our data and save to QVD's.

We load in our data everyday at around 1am which previously was taking arond 5 hrs to complete. The problem we are getting now is that it fails everynight as the server runs out of memory.

The process of creating our QVD's is to first load in the days worth of data then save each table as a TMPQVD.

then we combine the TMPQVD with the FULL QVD

I have been thinking of maybe trying the QUALIFY statement to stop the relations between the fields, which i think would speed up the process - Is this right??

But how then would i save my FULL QVD's unqualified?

Can anyone help?

[hopkinsc]

Thanks Rob, I will it give a go.

[hopkinsc]

Hi Rob, I have tried your suggestion but the script keeps failing when it reaches the WHERE statement.

Can you tell me where the WHERE statement should go in the script and what it should replace please?

Should i replace both LOAD DISTINCT * and CONCATENATE LOAD DISTINCT * statements?

Here is my script..

SET cFileName =$(vcRetailDailyAPIQVD)TMPstoreproduct.QVD;
IF QvdCreateTime('$(cFileName)') >= 0 THEN
StoreProduct:
LOAD DISTINCT *
FROM $(cFileName) (qvd);


SET cFileName =$(vcRetailQVD)StoreProduct.QVD;
IF QvdCreateTime('$(cFileName)') >= 0 THEN
StoreProduct:
CONCATENATE LOAD DISTINCT *
FROM $(cFileName) (qvd);
ENDIF

IF NoOfRows('StoreProduct') > 0 THEN
STORE * FROM StoreProduct INTO $(vcRetailQVD)StoreProduct.QVD;
DROP TABLE StoreProduct;
ENDIF
ENDIF

Thanks very much.

[rwunderlich · Accepted solution]

hopkinsc wrote:Should i replace both LOAD DISTINCT * and CONCATENATE LOAD DISTINCT * statements?

If I understand your script correctly, the first LOAD is from the tmp qvd that was created from the csv file. If you can only load the csv once, then you shouldn't need the DISTINCT. However, I'm guessing the tmp qvd is a small volume so the DISTINCT shouldn't make much difference one way or the other.

Now as to updating the master qvd in the second load. Assume a primary key field named "PK" exists or you created one as a composite of several fields. Then your second load should look like this:

SET cFileName =$(vcRetailQVD)StoreProduct.QVD;
IF QvdCreateTime('$(cFileName)') >= 0 THEN
StoreProduct:
CONCATENATE LOAD *
FROM $(cFileName) (qvd)
WHERE NOT EXISTS(PK)
;
ENDIF

The CONCATENATE is only required if you can have differenct field names between the tmp qvd and the master qvd, such as when adding new fields. If the field names will always be the same, leave off the CONCTENATE.

[hopkinsc]

Thanks Rob, That was very helpful.

[rwunderlich]

Ooops. I forgot to remove the DISTINCT from the example. It should be:

StoreProduct:
CONCATENATE LOAD *
FROM $(cFileName) (qvd)
WHERE NOT EXISTS(PK)