| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
| SELECT FROM WHERE |
« View previous topic :: View next topic » |
| Author |
Message
|
| Esa |
 Posted: Sun Mar 17, 2013 12:38 am Post subject: |
 |
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
By the way, it would be interesting to know if
| Code: |
| EXISTS(THE(SELECT <element> FROM <array> WHERE <condition>)) |
performs better than
| Code: |
| EXISTS(SELECT <element> FROM <array> WHERE <condition>) |
We know Message Broker makes use of several internal optimizations. Are there any optimizations when it comes to calling functions SELECT, THE and EXISTS in combinaison?
I mean that SELECT function could - when called by functions THE or EXISTS - return immediately after locating the first matching element.
Instead of building and returning the whole array?
If it does not, the lower level approach suggested by marko.pitkanen in logng's other recent thread would probably give the best performance when the input array is large. Traversing the input array with a reference variable and returning immeadiately after finding a positive match...
Of course the easiest and best performing way would be just to traverse the array and put all values in a java Set instead of the tree structure Environment.Variables.Layer1.Layer2.Layer3.id[]. Java Set would ensure that the values are unique, so you would not need to code the test at all. And if I'm not mistaken, logng, the array (or Set) of unique values is the one you would be using for matching values in the next step described in your other thread SELECT FROM DynRefVar... |
|
| Back to top |
|
 |
| longng |
| Posted: Mon Mar 18, 2013 8:32 pm Post subject: |
|
|
Apprentice
Joined: 22 Feb 2013
Posts: 42
|
| Esa wrote: |
By the way, it would be interesting to know if
| Code: |
| EXISTS(THE(SELECT <element> FROM <array> WHERE <condition>)) |
performs better than
| Code: |
| EXISTS(SELECT <element> FROM <array> WHERE <condition>) |
We know Message Broker makes use of several internal optimizations. Are there any optimizations when it comes to calling functions SELECT, THE and EXISTS in combinaison?
I mean that SELECT function could - when called by functions THE or EXISTS - return immediately after locating the first matching element.
Instead of building and returning the whole array?
If it does not, the lower level approach suggested by marko.pitkanen in logng's other recent thread would probably give the best performance when the input array is large. Traversing the input array with a reference variable and returning immeadiately after finding a positive match...
Of course the easiest and best performing way would be just to traverse the array and put all values in a java Set instead of the tree structure Environment.Variables.Layer1.Layer2.Layer3.id[]. Java Set would ensure that the values are unique, so you would not need to code the test at all. And if I'm not mistaken, logng, the array (or Set) of unique values is the one you would be using for matching values in the next step described in your other thread SELECT FROM DynRefVar... |
From syntax perspective, it's not correct to use EXISTS function with THE SELECT statement as its parameter since THE is implicitly singular whereas EXISTS expect a list. As a matter of fact, error BIP2493E (Illegal type for parameter '1' of the function EXIST) would be issued.
Since quite a few of us are interested in the performance topic. I'll try to post some of my findings later
Last edited by longng on Mon Mar 18, 2013 9:01 pm; edited 1 time in total |
|
| Back to top |
|
|
| longng |
| Posted: Mon Mar 18, 2013 8:41 pm Post subject: |
|
|
Apprentice
Joined: 22 Feb 2013
Posts: 42
|
Many thanks for sharing your expertise and making this thread interesting. As a returned favour and my earlier promise, I would like to share the results so far (unconfirmed) of my performance analysis related to what has been discusing so far in this thread and the other (www.mqseries.net/phpBB2/viewtopic.php?t=6372).
From the results (showed below), I am most surprised about the THE SELECT test cases as comparing to the cases of EXISTS SELECT... Originally, I assumed THE would be most efficient and performing best! But.... Please feel free to share your comments and let me know if you would like to have the code to test out for yourself before making comments.
My objective: To find out the most efficient approach (in response time) in performing searches and create an output (in Environment Variables) containing unique values.
For controlled tests, I generate an input consists of a list/array containing 4000 elements that contains random numbers between 0-499. Not surprisingly, the output would have a maximum of 500 elements representing 500 possible unique values.
The code used is mostly based upon the various techniques discussed in this thread and the broker runs in a RHELv6.4 VM with 4GB of memory 2 CPU (in VMware ESXi v5 with 64GB of memory and 16 CPU) and here are the results listed from worse to best:
1. For the case of having a separate search function (FindID()) and the THE SELECT, it takes approx. 2,790,000 microseconds (2.79 seconds)
2. For the case of having a separate search function (FindID()) and the RETURN EXISTS SELECT, it takes approx. 2,520,000 microseconds (2.52 seconds)
The remaining test cases do not involve separate search function, i.e. the searches and inserts are in the same procedure.
3. The use of FOR with predicate: 1,640,000 microseconds.
4. The use of THE SELECT: 1,250,000 microseconds
5. The use of EXISTS SELECT: 460,000 microseconds
6. The use of FOR loop and loop termination upon first found: untested. |
|
| Back to top |
|
|
| Esa |
| Posted: Tue Mar 19, 2013 12:08 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| longng wrote: |
4. The use of THE SELECT: 1,250,000 microseconds
5. The use of EXISTS SELECT: 460,000 microseconds
|
Thank you for sharing these interesting results! |
|
| Back to top |
|
|
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
