| Author |
Message
|
| hari.g |
 Posted: Mon Mar 22, 2010 11:26 pm Post subject: Arrange in alphabetical order |
 |
|
Newbie
Joined: 22 Mar 2010
Posts: 5
|
Hi guys,
Please let me know about any functions or statements present to present the data in alphabetical order. |
|
| Back to top |
|
 |
| harish_td |
| Posted: Tue Mar 23, 2010 12:15 am Post subject: |
|
|
Master
Joined: 13 Feb 2006
Posts: 236
|
Please elaborate on what you are trying to achieve.
If your data is from the DB, then there are already functions called ASC or DESC that will sort the data for you. |
|
| Back to top |
|
|
| hari.g |
| Posted: Tue Mar 23, 2010 11:03 pm Post subject: |
|
|
Newbie
Joined: 22 Mar 2010
Posts: 5
|
| I want to send the data alphabetically to the destination received from a file or MQ message |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Mar 24, 2010 1:46 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| I want to send the data alphabetically |
What are you sorting? Lines of text? Subtrees in OutputRoot? Something else? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Mar 24, 2010 2:23 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Which Transformation method are you using? Compute? Mapping? JavaCompute? PHPCompute?
Oddly enough, they offer *different* functions or statement.
What does the documentation say? Why haven't you looked? Clearly you haven't looked, or you would have asked a different question. |
|
| Back to top |
|
|
| hari.g |
| Posted: Thu Mar 25, 2010 12:42 am Post subject: |
|
|
Newbie
Joined: 22 Mar 2010
Posts: 5
|
| Thanks for your replies I am using the compute node and want to arrange the data taken from the input or source [from q] by taking the data ,do some program logic on that and and send the updated data which is in alphabetical order to the target through Q |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Mar 25, 2010 1:20 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| You have not answered my question. Please describe the data that you are sorting. |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Thu Mar 25, 2010 1:42 am Post subject: Sorting |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
Hello,
I have written a quicksort implementation in ESQL. It has about 30 lines of code, so is quite small. If you see fit the example I provide on https://github.com/mqsiuser/Generic-ESQL-Utilities/wiki/sort feel free to contact me for support.
kind regards,
mqsiuser
Last edited by mqsiuser on Tue Jul 12, 2011 11:43 pm; edited 1 time in total |
|
| Back to top |
|
|
| hari.g |
| Posted: Mon Mar 29, 2010 2:15 am Post subject: |
|
|
Newbie
Joined: 22 Mar 2010
Posts: 5
|
| Chked your reply it will suit my requirement can u pls share the program logic with me..... |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Mon Mar 29, 2010 2:37 am Post subject: quickSort(refList, sibling, element, typeCast, ascending) |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
Hello,
Here is a sample. If you want to sort the following input-structure:
| Code: |
<message>
<header date="2010-03-17" time="16:02.123"/>
<list>
<element value="BCD"/>
<element value="ABC"/>
<element value="BC"/>
<element value="A"/>
</list>
</message> |
Then you'd have the following code to call quickSort(...):
| Code: |
SET OutputRoot = InputRoot;
CALL quickSort( OutputRoot.XMLNSC.message.list, 'element', 'value', '', TRUE );
RETURN TRUE; |
and the quickSort()-function:
| Code: |
CREATE PROCEDURE quickSort( IN refList REFERENCE, IN sibling CHAR, IN element CHAR, IN typeCast CHAR, IN ascending BOOLEAN)
-- refList: A Reference to the structure, which currently embeds the unordered siblings
-- siblings: The name of the siblings (to be sorted): e.g. 'element'
-- element: The Field which will be compared: e.g. 'child.value'
-- typeCast: Type-Cast to proper Type: '' (No Cast), 'INT' (Integer), else 'yyyy-MM-dd HH:mm:ss' (TIMESTAMP)
-- ascending: Sort in ascending order if TRUE, else descending order
BEGIN
DECLARE rTemp REFERENCE TO refList;
MOVE rTemp FIRSTCHILD NAME sibling;
IF LASTMOVE(rTemp) THEN -- at least one element/sibling
MOVE rTemp NEXTSIBLING REPEAT NAME;
IF LASTMOVE(rTemp) THEN -- at least two elements/siblings --> start sorting
DECLARE rIn REFERENCE TO refList;DECLARE recDepth INT 0;
DECLARE finished BOOLEAN FALSE;DECLARE backOut BOOLEAN FALSE;
WHILE NOT finished DO
DECLARE rPivot REFERENCE TO rIn;
MOVE rPivot FIRSTCHILD NAME sibling;
IF NOT LASTMOVE(rPivot) THEN
SET backOut=TRUE;
ELSE
MOVE rPivot NEXTSIBLING REPEAT NAME;
IF NOT LASTMOVE(rPivot) THEN
DETACH rPivot; ATTACH rPivot TO refList AS LASTCHILD;
SET backOut=TRUE;
ELSE
IF recDepth > 20 THEN -- Recursion-Tree probably/likely degenerated
DECLARE siblingCount INT CARDINALITY( rIn.{sibling}[] ); -- Note that CARDINALITY can be bad for performance
MOVE rPivot TO rIn.{sibling}[ (siblingCount+1)/2 ]; -- Select pivot-element from the middle
END IF;
DECLARE rPiv REFERENCE TO rPivot; MOVE rPiv FIRSTCHILD NAME element;
DECLARE rUpper REFERENCE TO rIn;CREATE LASTCHILD OF rUpper NAME 'upper';MOVE rUpper LASTCHILD;
DECLARE rEqual REFERENCE TO rIn;CREATE LASTCHILD OF rEqual NAME 'equal';MOVE rEqual LASTCHILD;
DECLARE rLower REFERENCE TO rIn;CREATE LASTCHILD OF rLower NAME 'lower';MOVE rLower LASTCHILD;
DECLARE isSmaller BOOLEAN; DECLARE isBigger BOOLEAN; MOVE rIn FIRSTCHILD NAME sibling;
WHILE LASTMOVE( rIn ) DO
DECLARE rElem REFERENCE TO rIn; MOVE rElem FIRSTCHILD NAME element;
IF typeCast = '' THEN
SET isSmaller = rElem < rPiv;
SET isBigger = rElem > rPiv;
ELSEIF typeCast = 'INT' THEN
SET isSmaller = CAST( rElem AS INT ) < CAST ( rPiv AS INT );
SET isBigger = CAST( rElem AS INT ) > CAST ( rPiv AS INT );
ELSE
SET isSmaller = CAST( rElem AS TIMESTAMP FORMAT typeCast ) < CAST ( rPiv AS TIMESTAMP FORMAT typeCast );
SET isBigger = CAST( rElem AS TIMESTAMP FORMAT typeCast ) > CAST ( rPiv AS TIMESTAMP FORMAT typeCast );
END IF;
DECLARE rCur REFERENCE TO rIn;
MOVE rIn NEXTSIBLING REPEAT NAME;
IF ascending AND isSmaller OR NOT ascending AND isBigger THEN
DETACH rCur; ATTACH rCur TO rUpper AS LASTCHILD;
ELSEIF ascending AND isBigger OR NOT ascending AND isSmaller THEN
DETACH rCur;ATTACH rCur TO rLower AS LASTCHILD;
ELSE -- is Equal
DETACH rCur;ATTACH rCur TO rEqual AS LASTCHILD;
END IF;
END WHILE;
MOVE rIn TO rUpper; SET recDepth = recDepth + 1;
END IF;
END IF;
IF backOut THEN -- Backs Out from recursion
DECLARE doLoop BOOLEAN TRUE; SET backOut = FALSE;
WHILE doLoop DO
DECLARE rTmp REFERENCE TO rIn;MOVE rIn PARENT;SET rTmp = NULL;
IF FIELDNAME(rTmp)='upper' THEN
MOVE rIn FIRSTCHILD NAME 'equal';
MOVE rIn FIRSTCHILD NAME sibling;
DECLARE doWhile BOOLEAN LASTMOVE( rIn );
WHILE doWhile DO
DECLARE rTmp REFERENCE TO rIn;
MOVE rIn NEXTSIBLING REPEAT NAME;
IF NOT LASTMOVE(rIn) THEN
MOVE rIn PARENT;
SET doWhile=FALSE;
END IF;
DETACH rTmp;ATTACH rTmp TO refList AS LASTCHILD;
END WHILE;
DECLARE rTmp REFERENCE TO rIn;MOVE rIn PARENT;SET rTmp = NULL;
MOVE rIn FIRSTCHILD NAME 'lower';SET doLoop = FALSE;
ELSEIF rIn=refList THEN
SET finished = TRUE;SET doLoop = FALSE;
ELSEIF FIELDNAME(rTmp)='lower' THEN
SET recDepth = recDepth - 1;
END IF;
END WHILE;
END IF;
END WHILE;
END IF;
END IF;
END; |
will have the result:
| Code: |
<message>
<header date="2010-03-17" time="16:02.123"/>
<list>
<element value="A"/>
<element value="ABC"/>
<element value="BC"/>
<element value="BCD"/>
</list>
</message> |
quickSort() is stable.
Last edited by mqsiuser on Tue Feb 28, 2012 2:54 am; edited 9 times in total |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Thu Apr 01, 2010 4:08 am Post subject: Any experience, Bugs ? |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
Hello,
I thought its easy, but I found several improvements over the last few days. Fortunately it is possible to edit posts in this forum as many times as you like. I will have a look and try to adjust the code above to any bug which gets reported, so pls don't hesitate and tell me.
Up to now I have tested quickSort() with Broker 6.0 and 6.1, with small and large messages. I was able to sort 25 tsd siblings (3MB Message) within some fractions of seconds. |
|
| Back to top |
|
|
| pyrus |
| Posted: Tue Aug 17, 2010 7:30 am Post subject: |
|
|
Novice
Joined: 20 Jul 2007
Posts: 16
Location: Belgium
|
Sorry for posting on an old topic but I couldn't PM, apparently it's disabled...
Have you used this function on anything which contains tens of thousands of elements which need to be sorted? It seems like this function can't handle big loads because it seems to be using huge amounts of memory.
I have used this function on a file containing between 150 and 200 thousand records and the memory usage goes up to 2 GB and then the execution group restarts.
My own function doesn't have the memory problem but is much much slower than yours, so I would like to keep using this one, unfortunately I can't seem to figure out where all the memory is going, if I could only delete one field every now and then...
_________________
Connectivity Cowboy |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Aug 17, 2010 7:43 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| pyrus wrote: |
| Sorry for posting on an old topic but I couldn't PM, apparently it's disabled... |
PM is still on anti-spammer lockdown 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Wed Aug 18, 2010 2:37 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
sorry, did I disable PM ? Can I change this ?
My tests were with a structure which was very simple (just one element... the one which holds the sorting criteria). Also I have only tested 25 tsd siblings (which still is quite big and ends up in 3MB messages). Surely memory gets more heavily used, when the structures (of each sibling) are more complex. There is a limit on message size (~100MB) in MQ, so 100 tsd records is a lot.
I am happy if we can make improvements to generic functions (which are of interest to the community  ) and have it on the web. If you (anyone) can improve the performance (of quickSort) and send me the code, then I will adjust/update my original posting (to just have one leading implementation) !
Nevertheless quickSort is recursive and has increasing memory requirements (nlogn?!). This is little, but it is not n (stable). Still there might be improvement potential... and I will have a look. I am happy for anyone else who has a look and makes suggestions !
If you have strict memory requirements you might need to use non-recursive sorting (e.g. bubble sort, would that be doable in ESQL?). This is likely much slower in time (n²?!) but probably better (stable) in memory.
QuickSort is not bad (very quick in time and o.k. in memory) but it requires some additional memory and if you are at something between 50% to 80% of mem-usage (after just reading your data from the queue), then quickSort adds some more and then your flow crashes. I guess your flow will also crash if you just try to read a bit more than what you are currently processing (try 200-300tsd siblings). You seem to approach the maximum boundary of resources that your infrastructure (hardware (2GB of memory), broker and mq) provides with your requirements (150-200tsd siblings).
P.S.: Can anyone please enable PM again. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Aug 18, 2010 4:09 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqsiuser wrote: |
| sorry, did I disable PM ? Can I change this ? |
No, it's a global setting (or unsetting if you prefer).
| mqsiuser wrote: |
| P.S.: Can anyone please enable PM again. |
Only the administrators, and they have determined it will remain disabled until it's safe.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
