| Author |
Message
|
| kotagiriaashish |
 Posted: Sun Jan 15, 2012 2:51 am Post subject: ESQL Question? |
 |
|
Disciple
Joined: 06 Aug 2011
Posts: 165
|
If i want to select some values from Input Xml..... like I would like to select
all Authors who published books which cost more than 30$ from the xml
| Code: |
<books>
<book>
<author>Hightower, Kim</author>
<title>The First Book</title>
<genre>Fiction</genre>
<price>44.95</price>
<pub_date>2000-10-01</pub_date>
<review>An amazing story of nothing.</review>
</book>
<book>
<author>Nagata, Suanne</author>
<title>Becoming Somebody</title>
<genre>Biography</genre>
<review>A masterpiece of the fine art of gossiping.</review>
</book>
<book>
<author>Nagata, Suanne</author>
<title>Becoming Somebody</title>
<genre>Biography</genre>
<price>44.95</price>
<review>A masterpiece of the fine art of gossiping.</review>
</book>
<book>
<author>Nagata, Suanne</author>
<title>Becoming Somebody</title>
<genre>Biography</genre>
<review>A masterpiece of the fine art of gossiping.</review>
</book>
<book>
<author>Oberg, Bruce</author>
<title>The Poet's First Poem</title>
<genre>Poem</genre>
<price>24.95</price>
<review>The least poetic poems of the decade.</review>
</book>
</books>
|
I always find my fellow developers looping the entire xml through each sub node and checking its price and then its author.....
I suggested that we could use Select on the Input XML like
| Code: |
SET Environment.Variables.Output.Data[] =
(SELECT b.title AS Book.Title, b.author AS Book.Author
FROM InputRoot.XMLNSC.books.book[] AS b
WHERE b.price > 30
);
|
Most of them kept sticking with the looping method.... I mean Which one is better??
I personally believe Select is much better... what do you guys say? |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Sun Jan 15, 2012 3:46 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
For a small dataset then the loop might be faster.
I'd expect that for large datasets then the select will be better.
Why not try it out for yourself with a number of variable sized datasets?
There is nothing like seeing for yourself with things like this.
Why?
Any recommendations you might get will be based upon simiar but not the same experiences.
As far as best practices go, the select is by far the best method. So why do coders use a loop when a select can do it for you?
It could be because in some of the other popular dev languages they don't have a SELECT function?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Sun Jan 15, 2012 4:19 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| smdavies99 wrote: |
As far as best practices go, the select is by far the best method. So why do coders use a loop when a select can do it for you?
It could be because in some of the other popular dev languages they don't have a SELECT function? |
What is your recommendation for the Select vs a loop over a reference @ book level (move next sibling repeat type name)? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| kash3338 |
| Posted: Mon Jan 16, 2012 12:01 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
The main difference between a SELECT and LOOP is that, in case of SELECT there is loop running internally but stops as soon as the Where clause is met from your SELECT query. In case of a LOOP, we loop through the entire dataset even if the required record is found in the first set.
In case you break out of the loop as soon as your Where clause is met while using a LOOP, I guess its same as using a SELECT.
Experts, correct me if I am wrong. |
|
| Back to top |
|
|
| cociu_2012 |
| Posted: Mon Jan 16, 2012 12:12 am Post subject: |
|
|
Acolyte
Joined: 06 Jan 2012
Posts: 72
|
| kash3338 wrote: |
The main difference between a SELECT and LOOP is that, in case of SELECT there is loop running internally but stops as soon as the Where clause is met from your SELECT query. In case of a LOOP, we loop through the entire dataset even if the required record is found in the first set.
In case you break out of the loop as soon as your Where clause is met while using a LOOP, I guess its same as using a SELECT.
Experts, correct me if I am wrong. |
You're wrong. Your explained the exact opposite way.
If you're correct, the above example will stop at the first found book and will not get all the books that apply.
So, select always loops till the end, and that's the only problem.
In your case using select is a lot better, you anyway want to select all records that meet your requirement. But If for example you want to select the one with price = 44.95. Here If you're constantly having big XML's, classic loop with break fits better(more code, but performs better). |
|
| Back to top |
|
|
| kash3338 |
| Posted: Mon Jan 16, 2012 12:31 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| cociu_2012 wrote: |
You're wrong. Your explained the exact opposite way.
If you're correct, the above example will stop at the first found book and will not get all the books that apply.
|
I am sorry if i got it wrong, it works the way i explained in case of THE SELECT. Here the difference is seen as explained above.
In the above case any ways the loop is done completely and it has to be done in both cases, since the condition says for values greater than 30 . For these conditions both LOOP and SELECT does not make much of a difference since it has to be looped throughout your dataset. |
|
| Back to top |
|
|
| cociu_2012 |
| Posted: Mon Jan 16, 2012 12:42 am Post subject: |
|
|
Acolyte
Joined: 06 Jan 2012
Posts: 72
|
| kash3338 wrote: |
I am sorry if i got it wrong, it works the way i explained in case of THE SELECT. Here the difference is seen as explained above. |
Well, THE( SELECT ..), does the same thing - loop though all tree. It just selects firs value from result.
| kash3338 wrote: |
both LOOP and SELECT does not make much of a difference since it has to be looped throughout your dataset. |
It does(in this case): Select is more easy to code.
Less code, less to understand/review. |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Mon Jan 16, 2012 5:16 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| kash3338 wrote: |
both LOOP and SELECT does not make much of a difference since it has to be looped throughout your dataset. |
If your (experienced) fellow colleagues do something a certain way, which you can't explain why (and they also are too lazy to dispute with you)... probably they are still right (because of their experience). With reference moving and looping you just have the full control (how far to loop and what is done) and you can change the flow later and adjust some this and that more easily.
| cociu_2012 wrote: |
It does(in this case): Select is more easy to code.
Less code, less to understand/review. |
With select you have a black box doing (easy and limited) stuff for you. With changed requirements (which require more than what select can do) you quickly find yourself re-code the select into a reference-loop (Using a select and then appending a reference-loop (after the select) just makes roughly double the time for the flow to run, so you remove one of them).
_________________
Just use REFERENCEs
Last edited by mqsiuser on Mon Jan 16, 2012 6:58 am; edited 1 time in total |
|
| Back to top |
|
|
| kotagiriaashish |
| Posted: Mon Jan 16, 2012 6:47 am Post subject: |
|
|
Disciple
Joined: 06 Aug 2011
Posts: 165
|
| mqsiuser wrote: |
With select you have a black box doing (easy and limited) stuff for you. With changed requirements (which require more than what select can do) you quickly find yourself re-code the select into a reference-loop (Using a select and then appending a reference-loop (after the select) just makes roughly double the time for the flow to run, so you remove one of them).
|
The black box concept sounds more reasonable, In fact a simple select will be too complex to write in many cases and we will have lesser control..... unlike a custom loop...
I fact, the Select Statement should Loop through all the dataset, to see if any records match the criteria, the last records are probed too...
but i doubt if the select statement has some "Super Optimized" code which makes it run faster.... what say?? |
|
| Back to top |
|
|
| kotagiriaashish |
| Posted: Mon Jan 16, 2012 6:51 am Post subject: |
|
|
Disciple
Joined: 06 Aug 2011
Posts: 165
|
| smdavies99 wrote: |
For a small dataset then the loop might be faster.
I'd expect that for large datasets then the select will be better.
Why not try it out for yourself with a number of variable sized datasets?
There is nothing like seeing for yourself with things like this.
Why?
Any recommendations you might get will be based upon simiar but not the same experiences.
As far as best practices go, the select is by far the best method. So why do coders use a loop when a select can do it for you?
It could be because in some of the other popular dev languages they don't have a SELECT function? |
the results are pretty much the same... with 10 records and with 100000 records... it just took less than half a sec.. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Jan 16, 2012 6:52 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Writing a loop in ESQL would achieve nearly the same performance metrics as a SELECT, with a slight advantage being given to the SELECT method.
The reason this is so, is due to the fact that ESQL is interpreted, then executed, and SELECT is executed entirely in lower-level code ("C");
mgk, kimbert, mqjeff may be able to offer more insight into the performance; however, it is my belief that it would be hard to measure a significant difference. Both methods would perform very well.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Mon Jan 16, 2012 6:54 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| kotagiriaashish wrote: |
| but i doubt if the select statement has some "Super Optimized" code which makes it run faster.... what say?? |
Good point, I know of people who claim that an ESQL-Select it super-fast. Does anybody confirm with a number (twice as fast, 10% faster than proper ESQL-Reference-moving-Code)? There are some tricks like subselects with it, too. Probably it is just super-fast compared to "improper" (can also happen  ) ESQL code?
_________________
Just use REFERENCEs
Last edited by mqsiuser on Mon Jan 16, 2012 6:57 am; edited 2 times in total |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Jan 16, 2012 6:55 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Jan 16, 2012 7:09 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
My 2 cents:
If all you're doing is trying to retrieve an XML stanza (or stanzas) then it's probably faster and more maintainable to use a SELECT statement. Remember that you can easily store multiple results in a ROW variable.
If you're trying to identify an given stanza then transform it / output it / use it then the speed & maintenance advantage of using SELECT rapidly evaporates & it's easier to code a loop of some kind.
IMHO there's no "right" answer for SELECT v loop. I think there's a right answer for the requirements in a given situation. There are a lot of decision factors here, not least of which the size of the XML document you're working on.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Jan 16, 2012 7:16 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| Vitor wrote: |
IMHO there's no "right" answer for SELECT v loop. I think there's a right answer for the requirements in a given situation. There are a lot of decision factors here, not least of which the size of the XML document you're working on. |
And let us not forget the correct use of a reference variable.
If you go "walking the tree" for each loop, the performance of the select (or a loop with reference) can well be over twice as fast...
Have fun :innocent;
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
