| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| stored proc or sql |
« View previous topic :: View next topic » |
| Author |
Message
|
| ajit1981 |
 Posted: Thu Oct 07, 2010 4:27 am Post subject: stored proc or sql |
 |
|
Apprentice
Joined: 09 Apr 2010
Posts: 41
Location: Bangalore, Inida
|
Not sure weahter MqSeries forum is correct place for my this question or not, but this is realted to WMB so i am posting my query, please let me know your views.
Which will return faster response from database, stored procedure or normal sql query which we passing from MB flow esql ?
We have to developed a interface, which is webservice and destination system is oracle database(which is remote database), where the data volume of requst/response data is very high(it can go upto 10000 also).And this is real time interface, where user are wating for response once he/she click the button,so by keeping performance in mind we have save each second whereever its possibel. So just thinking if we used the stored procedure, will it reduce some response time ?
I know webservice is not correct option for this kind of operation, but this is requiremnt so we have to build this is in webservice. |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Thu Oct 07, 2010 6:58 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Have you thought about the flow not accessing the DB but writing to a Queue where an MDB running in a J2EE appserver would do the DB part? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Oct 07, 2010 8:38 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| fjb_saper wrote: |
| Have you thought about the flow not accessing the DB but writing to a Queue where an MDB running in a J2EE appserver would do the DB part? |
The performance of a stored procedure vs. doing the same logic in ESQL is going to be very dependent.
The stored procedure is likely going to execute somewhat faster, because it can be precompiled by the DB and stored and etc. etc. etc.
But the logic required to produce a result set that matches the output requirements may not run as fast as the same logic in ESQL would.
The performance of doing a round trip to a third application environment is likely to be more expensive than either of those, requiring as it will at a minimum an additional message serialization and another message parsing.
ajit1981 should prototype both solutions and do performance testing to evaluate. |
|
| Back to top |
|
|
| ajit1981 |
| Posted: Thu Oct 07, 2010 9:08 am Post subject: |
|
|
Apprentice
Joined: 09 Apr 2010
Posts: 41
Location: Bangalore, Inida
|
thanks mqjeff for your suggestion.
| Quote: |
| ajit1981 should prototype both solutions and do performance testing to evaluate. |
Yes. I have also thought the test both solutions, once I will get the access to database I will test both scenario. I will post my testing results here. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Oct 07, 2010 12:11 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| mqjeff wrote: |
| The performance of doing a round trip to a third application environment is likely to be more expensive than either of those, requiring as it will at a minimum an additional message serialization and another message parsing |
Sure but it decouples the flow from the DB and allows for different scaling.
You may have more instances of the MDB than instances of the flow.
If your limiting factor is the time needed for the DB operation, allowing for more MDB instances than flow instances will result in more throughput if you have an asynchronous model.
On a synchronous model you are limited at the speed of your weakest link.
As well you have better control on your time out/expiration.
If your DB timeout is 2 mins but your flow timeout is 500 ms, good luck on getting control back when the DB has a problem when the flow accesses the DB...
Have fun
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Oct 07, 2010 12:23 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| fjb_saper wrote: |
| Sure but it decouples the flow from the DB and allows for different scaling. |
Or it adds an unnecessary network round trip, unnecessary extra development tools and skills, unnecessary production support, unnecessary license costs....
There's nothing in this requirement that strongly suggests using anything other than Message Broker. Receive an HTTP request, make a database call, transform the result into an http response? Right in Broker's sweet spot. Potentially no code required. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
