| Author |
Message
|
| longng |
 Posted: Mon Mar 11, 2013 6:55 pm Post subject: SELECT FROM DynRefVar |
 |
|
Apprentice
Joined: 22 Feb 2013
Posts: 42
|
I am a bit green on the nuance of SELECT statement (non-database) here and just wonder if anyone would comment.
I (may be incorrectly) assume that a dynamic reference variable can be used anywhere but then....
The code populates Environment.Variables.CustomerRequest.CorrelationId with array of Id's (for the sake of simplicity, assuming that the array has 5 elements Environment.Variables.CustomerRequest.CorrelationId.Id[5] and all fully populated, and
then:
A classic way that works
| Code: |
SET rowId = THE(SELECT A.Id
FROM Environment.Variables.CustomerRequest.CorrelationId[] AS A
WHERE A.Id = CorrId);
|
Now I replace the above classic code as to use dynamic reference variables (supposed to be more efficient)
| Code: |
DECLARE xxx REFERENCE TO Environment.Variables.CustomerRequest.CorrelationId;
SET rowId = THE(SELECT A.Id
FROM xxx AS A
WHERE A.Id = CorrId);
|
The smart alex's code above appears to work but deeper analysis reveals a different story. The xxx variable always points to the same first element under CorrelationId and inside the SELECT statement, it seems to be aware of only that first element despite there are many more after that and the trace does confirm that's the case. i.e. something to the effect of "Finding one and only SELECT result. "
Can dynamic reference variable be used as FROM inside a SELECT statement? If yes, what's wrong with the above? |
|
| Back to top |
|
 |
| marko.pitkanen |
| Posted: Mon Mar 11, 2013 11:22 pm Post subject: |
|
|
Chevalier
Joined: 23 Jul 2008
Posts: 440
Location: Jamsa, Finland
|
Hi,
You should consider what is difference between following quotes and what is their implication for your SELECT statements.
| Quote: |
| An index clause can also consist of an empty pair of brackets ( [] ). This selects all fields with matching names. |
| Quote: |
| The REFERENCE data type holds the location of a field in a message. |
--
Marko |
|
| Back to top |
|
|
| Esa |
| Posted: Tue Mar 12, 2013 12:43 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
In other words:
Make the reference xxx point to Environment.Variables.CustomerRequest and then select from xxx.CorrelationId[].
But in fact changing an ESQL select like yours to use a reference variable instead of a direct reference does not make it more efficient - it was already as efficient it can be. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Mar 12, 2013 3:00 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
You want a result set instead of a single value. Have you looked up the significance of the "THE" statement in your ESQL? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Esa |
| Posted: Tue Mar 12, 2013 3:09 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| fjb_saper wrote: |
| You want a result set instead of a single value. Have you looked up the significance of the "THE" statement in your ESQL? |
No, he is assigining a singular value. The problem is that he gets a result only when the first id happens to match.... |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Mar 12, 2013 3:56 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Esa wrote: |
| fjb_saper wrote: |
| You want a result set instead of a single value. Have you looked up the significance of the "THE" statement in your ESQL? |
No, he is assigining a singular value. The problem is that he gets a result only when the first id happens to match.... |
The difference here is that in one case, the value being selected from is the entire set of children Environment.Variables.CustomerRequest.CorrelationId[] and in the second case it is the first child Environment.Variables.CustomerRequest.CorrelationId.
This is what marko.pitkanen has pointed out. |
|
| Back to top |
|
|
| longng |
| Posted: Tue Mar 12, 2013 7:24 am Post subject: |
|
|
Apprentice
Joined: 22 Feb 2013
Posts: 42
|
| Esa wrote: |
| fjb_saper wrote: |
| You want a result set instead of a single value. Have you looked up the significance of the "THE" statement in your ESQL? |
No, he is assigining a singular value. The problem is that he gets a result only when the first id happens to match.... |
@Esa: You're spot on! 
My intention here is to find out if a correlation ID has already been inserted into the array, hence I purposely use the THE.
To explain further, I would like to explore a way to optimise the search for a particular correlation ID within the array and if it's not already there then it will be inserted into it. The array can potentially have hundreds of element and I find out that the performance of the searches and inserts is not that great since there are going to be as many searches as there are the number of elements within the array. That's why I try to wring out every last bit of performance by using the tripple X's dynamic variable 
The general consensus on this board so far seems to indicate that the only correct way is to use a variable[] as a parameter for FROM and not the dynamic reference variable directly.
Is there a better algorithm? |
|
| Back to top |
|
|
| Esa |
| Posted: Tue Mar 12, 2013 7:50 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| longng wrote: |
Is there a better algorithm? |
OK, if you are going to issue the search for hundreds of times within one flow instance, then you may win some nanoseconds or even milliseconds by using the reference variable. Just do not select from the reference itself, but from the array beneath it, as we all have been trying to explain...
What would you say about something like this:
| Code: |
DECLARE xxx REFERENCE TO Environment.Variables.CustomerRequest.CorrelationId;
....
IF NOT EXISTS(xxx.{CorrId}[]) THEN
SET xxx,{CorrId} = CorrId;
ELSE
-- you have a match!
END IF; |
Didn't test this, but it might even work and give better performance. Depending on what you are trying to do with the list of correlation id's and the matching id's... |
|
| Back to top |
|
|
| Esa |
| Posted: Tue Mar 12, 2013 8:02 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| By the way, you are obviously looping with MQGet. Unless you know how to do it properly - which I doubt - the cause of your performance problems is probably not the way you search but the way you loop... |
|
| Back to top |
|
|
| kimbert |
| Posted: Tue Mar 12, 2013 8:04 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| if you are going to issue the search for hundreds of times within one flow instance, then |
...choose your data structure carefully!
If you have not already done so, I would consider one of the following:
- maintain a sorted array, and use a binary search
- store the values in a map
Those are probably best done using Java, which has excellent support for maps and arrays. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Tue Mar 12, 2013 8:17 am Post subject: Re: SELECT FROM DynRefVar |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| longng wrote: |
| Now I replace the above classic code as to use dynamic reference variables (supposed to be more efficient) |
Reference variables are only more efficient if they reduce the amount of tree navigation that a message flow needs to do to resolve references, and it's only significant if the message flow spends a lot of time navigating through trees. Using a reference variable only once per declaration is not more efficient, but using it to loop through the elements of an array can be. |
|
| Back to top |
|
|
| longng |
| Posted: Tue Mar 12, 2013 8:51 am Post subject: Re: SELECT FROM DynRefVar |
|
|
Apprentice
Joined: 22 Feb 2013
Posts: 42
|
| rekarm01 wrote: |
| longng wrote: |
| Now I replace the above classic code as to use dynamic reference variables (supposed to be more efficient) |
Reference variables are only more efficient if they reduce the amount of tree navigation that a message flow needs to do to resolve references, and it's only significant if the message flow spends a lot of time navigating through trees. Using a reference variable only once per declaration is not more efficient, but using it to loop through the elements of an array can be. |
We don't have any disagreements there as that has been my intention to reduce tree traversals. In trying to do so, I/we now realize that it's not appropriate to use a dynamic reference variable directly for the FROM option in a SELECT statement.
| Quote: |
What would you say about something like this:
| Code: |
DECLARE xxx REFERENCE TO Environment.Variables.CustomerRequest.CorrelationId;
....
IF NOT EXISTS(xxx.{CorrId}[]) THEN
SET xxx,{CorrId} = CorrId;
ELSE
-- you have a match!
END IF;
|
Didn't test this, but it might even work and give better performance. Depending on what you are trying to do with the list of correlation id's and the matching id's... |
Thanks Esa, that's a very interesting technique, I'll give it a try and share with you the results later. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Mar 12, 2013 9:32 am Post subject: Re: SELECT FROM DynRefVar |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| longng wrote: |
| rekarm01 wrote: |
| longng wrote: |
| Now I replace the above classic code as to use dynamic reference variables (supposed to be more efficient) |
Reference variables are only more efficient if they reduce the amount of tree navigation that a message flow needs to do to resolve references, and it's only significant if the message flow spends a lot of time navigating through trees. Using a reference variable only once per declaration is not more efficient, but using it to loop through the elements of an array can be. |
We don't have any disagreements there as that has been my intention to reduce tree traversals. In trying to do so, I/we now realize that it's not appropriate to use a dynamic reference variable directly for the FROM option in a SELECT statement. |
It's perfectly valid, and it's a somewhat reasonable performance enhancement. It reduces the tree navigation that the SELECT itself needs to process, potentially.
Again, the big difference between your two original statements is what you are selecting FROM. You provide a LIST of CorrelIDs in the first, working case, by using []. You provide a SINGLE CorrelID in the second, by pointing your reference at the FIRST child named CorrelID.
if you
| Code: |
DECLARE xxx REFERENCE TO Environment.Variables.CustomerRequest;
SET rowId = THE(SELECT A.Id
FROM xxx.CorrelationId[] AS A
WHERE A.Id = CorrId); |
Then you should be good. |
|
| Back to top |
|
|
| longng |
| Posted: Tue Mar 12, 2013 11:27 am Post subject: Re: SELECT FROM DynRefVar |
|
|
Apprentice
Joined: 22 Feb 2013
Posts: 42
|
| mqjeff wrote: |
| longng wrote: |
| rekarm01 wrote: |
| longng wrote: |
| Now I replace the above classic code as to use dynamic reference variables (supposed to be more efficient) |
Reference variables are only more efficient if they reduce the amount of tree navigation that a message flow needs to do to resolve references, and it's only significant if the message flow spends a lot of time navigating through trees. Using a reference variable only once per declaration is not more efficient, but using it to loop through the elements of an array can be. |
We don't have any disagreements there as that has been my intention to reduce tree traversals. In trying to do so, I/we now realize that it's not appropriate to use a dynamic reference variable directly for the FROM option in a SELECT statement. |
It's perfectly valid, and it's a somewhat reasonable performance enhancement. It reduces the tree navigation that the SELECT itself needs to process, potentially.
Again, the big difference between your two original statements is what you are selecting FROM. You provide a LIST of CorrelIDs in the first, working case, by using []. You provide a SINGLE CorrelID in the second, by pointing your reference at the FIRST child named CorrelID.
if you
| Code: |
DECLARE xxx REFERENCE TO Environment.Variables.CustomerRequest;
SET rowId = THE(SELECT A.Id
FROM xxx.CorrelationId[] AS A
WHERE A.Id = CorrId); |
Then you should be good. |
Indeed! I do get a bit better performance with using a reference variable to refer to the nearest branch as noted by you.
I also try the approach posted by Esa
| Code: |
DECLARE xxx REFERENCE TO Environment.Variables.CustomerRequest.CorrelationId;
....
IF NOT EXISTS(xxx.{CorrId}[]) THEN
SET xxx,{CorrId} = CorrId;
ELSE
-- you have a match!
END IF; |
but it does not seem to work for me as the test always returns negative (NOT EXISTS)... |
|
| Back to top |
|
|
| marko.pitkanen |
| Posted: Tue Mar 12, 2013 12:44 pm Post subject: |
|
|
Chevalier
Joined: 23 Jul 2008
Posts: 440
Location: Jamsa, Finland
|
Hi,
If I got it right you need to create fields named by the values you need to search to get Esa's code snippet working?
You could also try to build your own search / add routine with named loop over reference variable, move and leave statements. Then you perhaps can stop loop if you find the value from your list.
--
Marko |
|
| Back to top |
|
|
|
|
