Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
DEAR TEAM,
Requirement is -> Dump those model devices from Devices master table that is not contributing X % of call.
X% IS VARIABLE USER DEFINED:
LET SAY X=0.01
Existing code AS BELOW that we have TOOK almost 2 hours to dump few thouannds model from Million models.
Can u please advise what is the fastest/optimized way of achieving the same results?
tHIS IS HOW WE ARE HANDLING AT PRESENT WHICH IS VERY TIME CONSUMING
/12/2016 3:37:54 AM: 0087 SumAllCallsTmp:
7/12/2016 3:37:54 AM: 0088 LOAD
7/12/2016 3:37:54 AM: 0089 sum(COUNT) as SumOfAllCalls
7/12/2016 3:37:54 AM: 0090 Resident DEVICEMASTER
7/12/2016 3:42:09 AM: 1 fields found: SumOfAllCalls, 1 lines fetched
7/12/2016 3:42:09 AM: 0093 LET vSumAllCalls=Peek('SumOfAllCalls',0)
7/12/2016 3:42:09 AM: 0094 DROP Table SumAllCallsTmp
7/12/2016 3:42:09 AM: 0097 ModelDumpCalc:
7/12/2016 3:42:09 AM: 0098 LOAD
7/12/2016 3:42:09 AM: 0099 ModelDevices,
7/12/2016 3:42:09 AM: 0100 sum(COUNT)/vSumAllCalls as SumCallPerModel
7/12/2016 3:42:09 AM: 0101 Resident DEVICEMASTER
7/12/2016 3:42:09 AM: 0102 group by ModelDevices
7/12/2016 3:47:37 AM: 2 fields found: ModelDevices, SumCallPerModel, 29,314 lines fetched
7/12/2016 3:47:37 AM: 0103 DROP Table DEVICEMASTER
7/12/2016 3:47:46 AM: 0106 NoConcatenate
7/12/2016 3:47:46 AM: 0107 ModelDumpOrderBy:
7/12/2016 3:47:46 AM: 0108 LOAD *
7/12/2016 3:47:46 AM: 0109 Resident ModelDumpCalc
7/12/2016 3:47:46 AM: 0110 order by SumCallPerModel asc
7/12/2016 3:47:46 AM: 2 fields found: ModelDevices, SumCallPerModel, 29,314 lines fetched
7/12/2016 3:47:46 AM: 0111 DROP Table ModelDumpCalc
7/12/2016 3:47:46 AM: 0114 NoConcatenate
7/12/2016 3:47:46 AM: 0115 AccumulatedTable:
7/12/2016 3:47:46 AM: 0116 LOAD *, if(rowno()=1,SumCallPerModel,Peek(AccumulatedSum)+SumCallPerModel) AS AccumulatedSum
7/12/2016 3:47:46 AM: 0117 Resident ModelDumpOrderBy
7/12/2016 3:47:46 AM: 3 fields found: ModelDevices, SumCallPerModel, AccumulatedSum, 29,314 lines fetched
7/12/2016 3:47:46 AM: 0118 DROP Table ModelDumpOrderBy
7/12/2016 3:47:46 AM: 0121 NoConcatenate
7/12/2016 3:47:46 AM: 0122 ModelDump:
7/12/2016 3:47:46 AM: 0123 LOAD *
7/12/2016 3:47:46 AM: 0124 Resident AccumulatedTable
7/12/2016 3:47:46 AM: 0125 where AccumulatedSum>0.01
7/12/2016 3:47:46 AM: 3 fields found: ModelDevices, SumCallPerModel, AccumulatedSum, 2,541 lines fetched
7/12/2016 3:47:46 AM: 0126 DROP Table AccumulatedTable
7/12/2016 3:47:46 AM: 0130 left keep(ModelDump)
7/12/2016 3:47:46 AM: 0131 DEVICEMASTER:
7/12/2016 3:47:46 AM: 0132 LOAD Distinct
7/12/2016 3:47:46 AM: 0133 *
7/12/2016 3:47:46 AM: 0134 FROM F:\QV\ScModel\QVD\Devices\DEVICEMASTER.Qvd(qvd)
7/12/2016 3:47:46 AM: 24 fields found:461,875,007 lines fetched
7/12/2016 4:58:52 AM: Joining/Keeping
7/12/2016 5:04:47 AM: 0136 DROP Table ModelDump
May be use Where Exists() instead of Left Keep because you will be able to do an optimized load while loading the qvd using Where Exists() whereas Left Keep() might not keep it optimized (I am not sure if Left keep is optimized or not)