Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
PROBLEM: I have a very slow running loop that peeks() at a bit mask field in each row in order to extract the values into a separate fact table
DETAILS:
I have a data model in which each possible status is encoded as a different bit.
Here would be the 'hard coded' table representing the possible bit values:
_CarTypeBitMask:
LOAD * INLINE [
_CarTypeMaskVal, _CarTypeMaskText
1,Truck
2,Car
4,Mini Van
8,Cross-Over
16,2-wheel
32,4-wheel
64,AWD
];
And the list of cars read in from a data source: (example made up, I'm not really dealing with cars)
[Cars]:
LOAD * INLINE [
%CarID,CarTypeMask
1,18 // Car + 2-wheel
2,33 // Truck + 4-wheel
3,24 // Cross-Over + 2-wheel
];
The idea is to generate a new table pulling out the car type combinations:
CarTypes:
%CarID,CarType
1,Car
1,2-Wheel
2,Truck
2,4-Wheel
3,Cross-Over
3,2-Wheel
Here's the script I run, but with thousands of rows it is way too slow:
// CREATE CarTypes 1-N table
//
vNumRows = NoOfRows('Cars')
FOR i=0 to $(vNumRows)-1; // for each Car
LET vCarID=peek('%CarID',$(i),'Cars'); // read unique car ID
LET vMask=peek('CarTypeMask',$(i),'Cars'); // read the mask
FOR j = 1 toNoOfRows('_CarTypeBitMask') // for each possible bit mask
LET vMask_Num = peek('_CarTypeMaskVal',$(j),'_CarTypeBitMask');
IF( $(vMask) bitand $(vMask_Num) = $(vMask_Num) ) THEN // IF this bit is ON
LET vCMF_Text = peek('_CarTypeMaskText',$(j),'_CarTypeBitMask');
[CarTypes]:
LOAD * INLINE[
%CarID,CarType
$(vCarID),$(vCMF_Text)];
ENDIF
NEXT
NEXT
(please note I modified the code here to be based on car and car types from my original code. My original code works. It's just slow)
So any ideas on how to improve the performance?
Yes, Loops are really slow for what you are trying to do.
I would chose another approch, creating a load of your cars table with a integrated WHILE loop in the LOAD statement to generate a record per bit of the mask. Then MAP the bits:
CarTypeBitMask:
MAPPING
LOAD * INLINE [
_CarTypeMaskVal, _CarTypeMaskText
1,Truck
2,Car
4,Mini Van
8,Cross-Over
16,2-wheel
32,4-wheel
64,AWD
];
//And the list of cars read in from a data source: (example made up, I'm not really dealing with cars)
[Cars]:
LOAD * INLINE [
%CarID,CarTypeMask
1,18
2,33
3,24
];
//
//The idea is to generate a new table pulling out the car type combinations:
//
//CarTypes:
//%CarID,CarType
//1,Car
//1,2-Wheel
//2,Truck
//2,4-Wheel
//3,Cross-Over
//3,2-Wheel
//
//
//Here's the script I run, but with thousands of rows it is way too slow:
//
// CREATE CarTypes 1-N table
//
RESULT:
LOAD *
,APPLYMAP('_CarTypeBitMask', Split, 'Oops, missing CarTypeBitMap entry') as CarType
WHERE Split;
LOAD %CarID,
CarTypeMask bitand pow(2,iterno()-1) as Split
Resident Cars
WHILE iterno() < 7;
If this runs better, could you post the improvement?
Yes, Loops are really slow for what you are trying to do.
I would chose another approch, creating a load of your cars table with a integrated WHILE loop in the LOAD statement to generate a record per bit of the mask. Then MAP the bits:
CarTypeBitMask:
MAPPING
LOAD * INLINE [
_CarTypeMaskVal, _CarTypeMaskText
1,Truck
2,Car
4,Mini Van
8,Cross-Over
16,2-wheel
32,4-wheel
64,AWD
];
//And the list of cars read in from a data source: (example made up, I'm not really dealing with cars)
[Cars]:
LOAD * INLINE [
%CarID,CarTypeMask
1,18
2,33
3,24
];
//
//The idea is to generate a new table pulling out the car type combinations:
//
//CarTypes:
//%CarID,CarType
//1,Car
//1,2-Wheel
//2,Truck
//2,4-Wheel
//3,Cross-Over
//3,2-Wheel
//
//
//Here's the script I run, but with thousands of rows it is way too slow:
//
// CREATE CarTypes 1-N table
//
RESULT:
LOAD *
,APPLYMAP('_CarTypeBitMask', Split, 'Oops, missing CarTypeBitMap entry') as CarType
WHERE Split;
LOAD %CarID,
CarTypeMask bitand pow(2,iterno()-1) as Split
Resident Cars
WHILE iterno() < 7;
If this runs better, could you post the improvement?
Thank you very much!
This is a very slick piece of code - I continue to forget that you can iterate during the load operation.
This is SIGNIFICANTLY FASTER!
I had a table of 30,000 entries and 16 possible bit masks.
The original script took 2 hours to run. With your solution it was down to 30 seconds !!
I had a table of 30,000 entries and 16 possible bit masks.
The original script took 2 hours to run. With your solution it was down to 30 seconds !!
Yes, that's what I call significant. There is a BIG overhead in For / Next loop and table creation.
Like it!
Hi Stefan,
I am also facing same type of problem. I am using Loops and Peek functions to get the Last team data in Ticket history table.Logic is working fine but it runs too slow and takes 10 hrs to complete the load.
Requirement is needs to pick most recent team details only per each ticket.
For more details please open attached excel where you can find sample Input and Output format.
Code i written in my application is below.
Ticket_History:
Load
TCKT_HIS_KEY_AS_NUM,
Ticket_His_TeamName,
START_DATE_TIME,
END_DATE_TIME,
Ticket_His_OpenStatus_Desc,
1 as Jink
// TCKT_HIS_KEY_AS_NUM&Ticket_His_TeamName as TTnKey,
// TCKT_HIS_KEY_AS_NUM&Ticket_His_TeamName&START_DATE_TIME&END_DATE_TIME as TempKey
Resident Ticket_History1
Order by TCKT_HIS_KEY_AS_NUM,START_DATE_TIME desc , END_DATE_TIME desc;
DROP Table Ticket_History1;
Let vNoOfRows = NoOfRows('Ticket_History');
set vFlag=1;
FOR i=0 to $(vNoOfRows)-1
Let i1= $(i)+1;
Let vTicketKey2=Peek('TCKT_HIS_KEY_AS_NUM',$(i),'Ticket_History');
Let vTicketKey3=Peek('TCKT_HIS_KEY_AS_NUM',$(i1),'Ticket_History');
SET vTicketKey=Peek('TCKT_HIS_KEY_AS_NUM',$(i),'Ticket_History');
SET vTicketKey1=Peek('TCKT_HIS_KEY_AS_NUM',$(i1),'Ticket_History');
SET vTeam=Peek('Ticket_His_TeamName',$(i),'Ticket_History');
SET vST=Peek('START_DATE_TIME',$(i),'Ticket_History');
SET vET=Peek('END_DATE_TIME',$(i),'Ticket_History');
SET vStatus=Peek('Ticket_His_OpenStatus_Desc',$(i),'Ticket_History');
SET vKey=Peek('TCKT_HIS_KEY_AS_NUM',$(i),'Ticket_History')& Peek('Ticket_His_TeamName',$(i),'Ticket_History');
SET vKey1=Peek('TCKT_HIS_KEY_AS_NUM',$(i1),'Ticket_History')& Peek('Ticket_His_TeamName',$(i1),'Ticket_History');
Let vKey2=AutoNumber(Peek('TCKT_HIS_KEY_AS_NUM',$(i),'Ticket_History')& Peek('Ticket_His_TeamName',$(i),'Ticket_History'));
lET vKey3=AutoNumber(Peek('TCKT_HIS_KEY_AS_NUM',$(i1),'Ticket_History')& Peek('Ticket_His_TeamName',$(i1),'Ticket_History'));
TestReplicate:
LOAD $(vTicketKey) as Ticket,
$(vTeam) as Team,
$(vST) as ST,
$(vET) as ET,
$(vStatus) as Status,
$(vKey) AS Key,
$(vKey1) AS Key1,
$(vFlag) as Flag
AutoGenerate(1);
// TRACE $(vKey2) = $(vKey3);
If '$(vKey2)'<>'$(vKey3)' then
set vFlag=0;
ENDIF
//TRACE $(vTicketKey2) = $(vTicketKey3);
If '$(vTicketKey2)'<>'$(vTicketKey3)' then
set vFlag=1;
ENDIF
//Where $(vKey)=$(vKey1) ;
NEXT i;
@Stefan wuhl
Thanks in advance.
"Requirement is needs to pick most recent team details only per each ticket."
You should be able to fulfill your requirement by simply aggregating your table, grouped by ticket.
Use FirstSortedValue() to retrieve the latest information by a sort order value (latest timestamp).
Something like
LOAD
Ticket,
Max(Timestamp) as LatestTimestamp,
FirstSortedValue(AnotherAttribute, -Timestamp) as LatestAnotherAttribute
RESIDENT YourTable
GROUP BY Ticket;
Hi,
Thanks for your quick reply on this.
Sorry for confusion, I don't want to pick only last record..I need to pick last team related all records.
If we use Aggregation we will get one record per Ticket, But i want to load last team all records to find Turntime of that last team
Please see below output.
Input | ||||
TCKT_HIS_KEY_AS_NUM | START_DATE_TIME | END_DATE_TIME | Ticket_His_OpenStatus_Desc | Ticket_His_TeamName |
149879 | 15-06-17 10:38 | 18-09-17 14:13 | Open | CS Team 8 Birm |
149879 | 18-09-17 14:13 | 18-09-17 14:16 | In Process | Data Management 2 |
149879 | 18-09-17 15:04 | 18-09-17 15:09 | In Process | CS Team 2 Birm |
149879 | 22-09-17 8:24 | 27-09-17 8:44 | Customer Action | CS Team 2 Birm |
149879 | 27-09-17 8:44 | 27-09-17 8:47 | 3rd Level Support | CS Team 2 Birm |
149879 | 27-09-17 8:47 | 28-09-17 12:34 | 3rd Level Support | CS Team 8 Birm |
149879 | 28-09-17 12:34 | 28-09-17 12:36 | In Process | CS Team 8 Birm |
149879 | 05-10-17 12:02 | 31-12-99 0:00 | Complete | CS Team 8 Birm |
17 | 31-05-17 12:45 | 01-06-17 11:44 | Open | Post Live - Plus / Elite / Video |
17 | 01-06-17 11:44 | 30-06-17 10:23 | Closed (in use) | Post Live - Plus / Elite / Video |
17 | 30-06-17 10:23 | 30-06-17 10:24 | In Process | Post Live - Plus / Elite / Video |
17 | 30-06-17 10:24 | 30-06-17 10:53 | In Process | Elite Support |
17 | 30-06-17 10:53 | 31-12-99 0:00 | Complete | Elite Support |
Output | ||||
TCKT_HIS_KEY_AS_NUM | START_DATE_TIME | END_DATE_TIME | Ticket_His_OpenStatus_Desc | Ticket_His_TeamName |
149879 | 27-09-17 8:47 | 28-09-17 12:34 | 3rd Level Support | CS Team 8 Birm |
149879 | 28-09-17 12:34 | 28-09-17 12:36 | In Process | CS Team 8 Birm |
149879 | 05-10-17 12:02 | 31-12-99 0:00 | Complete | CS Team 8 Birm |
17 | 30-06-17 10:24 | 30-06-17 10:53 | In Process | Elite Support |
17 | 30-06-17 10:53 | 31-12-99 0:00 | Complete | Elite Support |
Please let me know required more clarification on this.
Thanks,
Ashok.
Ok, but similar approach:
Find the last team per ticket, using my previous script. Then use these two fields, ticket and team, as key to join all records related to these ticket team combinations from your input table.