qvd append taking a long time

[#1]

I have a QVD Load that appends several QVDs into one - the purpose being to append records to the same QVD file each day, so it has all the data historically.  Right now, this takes over an hour, and on some days, several hours.  Does anyone have any suggestions for how I can make this more efficient?  The load script is pasted below.

testRecs:
LOAD
    testClientCode,
    testRecsKey,
    testCode,
    ClientWL,
    testEvent,
    testTask,
    testStatus,
    testInsCode,
    testEntryDt,
    testEntryYrMth,
    testEntryOp,
    testEntryTm,
    testCaseNum,
    testAcctNum,
    testTicketNum,
    CLCase#,
    testStatusDt,
    testStatusTm,
    testStatusOp,
    testAssignee,
    testAssigneeDt,
    testAssigneeTm,
    testDept,
    testRefPhys,
    testAging,
    testStatusAging,
    testSys,
    testRecsAgedToDt,
    testRecsAgedToTm,
    testRecsCondition,
    testAgingCalDays
FROM

(qvd);

//A_testRecs:
Concatenate (testRecs)
LOAD
    testClientCode,
    testRecsKey,
    testCode,
    testClientCode & '_' & testEvent & '_' & testTask & '_' & Today () as ClientWL,
    testEvent,
    testTask,
    testStatus,
    testInsCode,
    testEntryDt,
    testEntryYrMth,
    testEntryOp,
    testEntryTm,
    testCaseNum,
    testAcctNum,
    testTicketNum,
    testClientCode & '_' & testCaseNum  & '_' & Today () as CLCase#,
    testStatusDt,
    testStatusTm,
    testStatusOp,
    testAssignee,
    testAssigneeDt,
    testAssigneeTm,
    testDept,
    testRefPhys,
    testAging,
    testStatusAging,
    testSys,
    testRecsAgedToDt,
    testRecsAgedToTm,
    testRecsCondition,
    testAgingCalDays
FROM

(qvd);

//B_testRecs:
Concatenate (testRecs)
LOAD
    testClientCode,
    testRecsKey,
    testCode,
    testClientCode & '_' & testEvent & '_' & testTask  & '_' & Today () as ClientWL,
    testEvent,
    testTask,
    testStatus,
    testInsCode,
    testEntryDt,
    testEntryYrMth,
    testEntryOp,
    testEntryTm,
    testCaseNum,
    testAcctNum,
    testTicketNum,
    testClientCode & '_' & testCaseNum  & '_' & Today () as CLCase#,
    testStatusDt,
    testStatusTm,
    testStatusOp,
    testAssignee,
    testAssigneeDt,
    testAssigneeTm,
    testDept,
    testRefPhys,
    testAging,
    testStatusAging,
    testSys,
    testRecsAgedToDt,
    testRecsAgedToTm,
    testRecsCondition,
    testAgingCalDays
FROM

(qvd);

//C_testRecs:
Concatenate (testRecs)
LOAD
    testClientCode,
    testRecsKey,
    testCode,
    testClientCode & '_' & testEvent & '_' & testTask  & '_' & Today () as ClientWL,
    testEvent,
    testTask,
    testStatus,
    testInsCode,
    testEntryDt,
    testEntryYrMth,
    testEntryOp,
    testEntryTm,
    testCaseNum,
    testAcctNum,
    testTicketNum,
    testClientCode & '_' & testCaseNum  & '_' & Today () as CLCase#,
    testStatusDt,
    testStatusTm,
    testStatusOp,
    testAssignee,
    testAssigneeDt,
    testAssigneeTm,
    testDept,
    testRefPhys,
    testAging,
    testStatusAging,
    testSys,
    testRecsAgedToDt,
    testRecsAgedToTm,
    testRecsCondition,
    testAgingCalDays
FROM

(qvd);

//D_testRecs:
Concatenate (testRecs)
LOAD
    testClientCode,
    testRecsKey,
    testCode,
    testClientCode & '_' & testEvent & '_' & testTask  & '_' & Today () as ClientWL,
    testEvent,
    testTask,
    testStatus,
    testInsCode,
    testEntryDt,
    testEntryYrMth,
    testEntryOp,
    testEntryTm,
    testCaseNum,
    testAcctNum,
    testTicketNum,
    testClientCode & '_' & testCaseNum  & '_' & Today () as CLCase#,
    testStatusDt,
    testStatusTm,
    testStatusOp,
    testAssignee,
    testAssigneeDt,
    testAssigneeTm,
    testDept,
    testRefPhys,
    testAging,
    testStatusAging,
    testSys,
    testRecsAgedToDt,
    testRecsAgedToTm,
    testRecsCondition,
    testAgingCalDays
FROM

(qvd);

//E_testRecs:
Concatenate (testRecs)
LOAD
    testClientCode,
    testRecsKey,
    testCode,
    testClientCode & '_' & testEvent & '_' & testTask  & '_' & Today () as ClientWL,
    testEvent,
    testTask,
    testStatus,
    testInsCode,
    testEntryDt,
    testEntryYrMth,
    testEntryOp,
    testEntryTm,
    testCaseNum,
    testAcctNum,
    testTicketNum,
    testClientCode & '_' & testCaseNum  & '_' & Today () as CLCase#,
    testStatusDt,
    testStatusTm,
    testStatusOp,
    testAssignee,
    testAssigneeDt,
    testAssigneeTm,
    testDept,
    testRefPhys,
    testAging,
    testStatusAging,
    testSys,
    testRecsAgedToDt,
    testRecsAgedToTm,
    testRecsCondition,
    testAgingCalDays
FROM

(qvd);

//F_testRecs:
Concatenate (testRecs)
LOAD
    testClientCode,
    testRecsKey,
    testCode,
    testClientCode & '_' & testEvent & '_' & testTask  & '_' & Today () as ClientWL,
    testEvent,
    testTask,
    testStatus,
    testInsCode,
    testEntryDt,
    testEntryYrMth,
    testEntryOp,
    testEntryTm,
    testCaseNum,
    testAcctNum,
    testTicketNum,
    testClientCode & '_' & testCaseNum  & '_' & Today () as CLCase#,
    testStatusDt,
    testStatusTm,
    testStatusOp,
    testAssignee,
    testAssigneeDt,
    testAssigneeTm,
    testDept,
    testRefPhys,
    testAging,
    testStatusAging,
    testSys,
    testRecsAgedToDt,
    testRecsAgedToTm,
    testRecsCondition,
    testAgingCalDays
FROM

(qvd);

//G_testRecs:
Concatenate (testRecs)
LOAD
    testClientCode,
    testRecsKey,
    testCode,
    testClientCode & '_' & testEvent & '_' & testTask  & '_' & Today () as ClientWL,
    testEvent,
    testTask,
    testStatus,
    testInsCode,
    testEntryDt,
    testEntryYrMth,
    testEntryOp,
    testEntryTm,
    testCaseNum,
    testAcctNum,
    testTicketNum,
    testClientCode & '_' & testCaseNum  & '_' & Today () as CLCase#,
    testStatusDt,
    testStatusTm,
    testStatusOp,
    testAssignee,
    testAssigneeDt,
    testAssigneeTm,
    testDept,
    testRefPhys,
    testAging,
    testStatusAging,
    testSys,
    testRecsAgedToDt,
    testRecsAgedToTm,
    testRecsCondition,
    testAgingCalDays
FROM

(qvd);

//H_testRecs:
Concatenate (testRecs)
LOAD
    testClientCode,
    testRecsKey,
    testCode,
    testClientCode & '_' & testEvent & '_' & testTask  & '_' & Today () as ClientWL,
    testEvent,
    testTask,
    testStatus,
    testInsCode,
    testEntryDt,
    testEntryYrMth,
    testEntryOp,
    testEntryTm,
    testCaseNum,
    testAcctNum,
    testTicketNum,
    testClientCode & '_' & testCaseNum  & '_' & Today () as CLCase#,
    testStatusDt,
    testStatusTm,
    testStatusOp,
    testAssignee,
    testAssigneeDt,
    testAssigneeTm,
    testDept,
    testRefPhys,
    testAging,
    testStatusAging,
    testSys,
    testRecsAgedToDt,
    testRecsAgedToTm,
    testRecsCondition,
    testAgingCalDays
FROM

(qvd);

//I_testRecs:
Concatenate (testRecs)
LOAD
    testClientCode,
    testRecsKey,
    testCode,
    testClientCode & '_' & testEvent & '_' & testTask  & '_' & Today () as ClientWL,
    testEvent,
    testTask,
    testStatus,
    testInsCode,
    testEntryDt,
    testEntryYrMth,
    testEntryOp,
    testEntryTm,
    testCaseNum,
    testAcctNum,
    testTicketNum,
    testClientCode & '_' & testCaseNum  & '_' & Today () as CLCase#,
    testStatusDt,
    testStatusTm,
    testStatusOp,
    testAssignee,
    testAssigneeDt,
    testAssigneeTm,
    testDept,
    testRefPhys,
    testAging,
    testStatusAging,
    testSys,
    testRecsAgedToDt,
    testRecsAgedToTm,
    testRecsCondition,
    testAgingCalDays
FROM

(qvd);

//J_testRecs:
Concatenate (testRecs)
LOAD
    testClientCode,
    testRecsKey,
    testCode,
    testClientCode & '_' & testEvent & '_' & testTask  & '_' & Today () as ClientWL,
    testEvent,
    testTask,
    testStatus,
    testInsCode,
    testEntryDt,
    testEntryYrMth,
    testEntryOp,
    testEntryTm,
    testCaseNum,
    testAcctNum,
    testTicketNum,
    testClientCode & '_' & testCaseNum  & '_' & Today () as CLCase#,
    testStatusDt,
    testStatusTm,
    testStatusOp,
    testAssignee,
    testAssigneeDt,
    testAssigneeTm,
    testDept,
    testRefPhys,
    testAging,
    testStatusAging,
    testSys,
    testRecsAgedToDt,
    testRecsAgedToTm,
    testRecsCondition,
    testAgingCalDays
FROM

(qvd);

//K_testRecs:
Concatenate (testRecs)
LOAD
    testClientCode,
    testRecsKey,
    testCode,
    testClientCode & '_' & testEvent & '_' & testTask  & '_' & Today () as ClientWL,
    testEvent,
    testTask,
    testStatus,
    testInsCode,
    testEntryDt,
    testEntryYrMth,
    testEntryOp,
    testEntryTm,
    testCaseNum,
    testAcctNum,
    testTicketNum,
    testClientCode & '_' & testCaseNum  & '_' & Today () as CLCase#,
    testStatusDt,
    testStatusTm,
    testStatusOp,
    testAssignee,
    testAssigneeDt,
    testAssigneeTm,
    testDept,
    testRefPhys,
    testAging,
    testStatusAging,
    testSys,
    testRecsAgedToDt,
    testRecsAgedToTm,
    testRecsCondition,
    testAgingCalDays
FROM

(qvd);

if(num(WeekDay(Today())) < 5) then
store testRecs into C:\testRecs_Hist.qvd;

DROP table testRecs;

[Jason_Michaelides · Accepted solution]

Not sure how your QVD files are produced but if you can have your ClientWL and CLCase# already created then this will be a straightforward optimised load and will be orders of magnitude faster.

[rwunderlich]

You are getting uoptimized loads, this would be much faster with an optimized load. To convert this to optimized load:

1. Don't use concatenate.

2. Don't create the ClientWL and CLCase# in this load. Create those fields when you create the A/B/C_testrec qvds.

-Rob

http://robwunderlich.com

[Jason_Michaelides]

Hey Rob - I'm sure that as long as all the QVD files have the same fields then CONCATENATE will still be optimized.