| Author |
Message
|
| pratik_oze |
 Posted: Sun Jun 17, 2012 9:53 am Post subject: Database inserts frequency for high volumes - RT or batch? |
 |
|
Apprentice
Joined: 22 Jun 2004
Posts: 34
|
We receive high volumes of messages in a second. One of the flow is currently designed to make external oracle database inserts. This is taking a long time than expected and hence piling s observed on the MQ queue.
This database inserts are done real time(RT). Is this the right approach in WMB for such high volumes or will a batch insert approach must be followed?
Any pointers please. |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Sun Jun 17, 2012 11:39 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
It might help if we had an idea of how the insert into the DB was coded... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sun Jun 17, 2012 12:14 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
As a general rule of thumb, using a Stored Procedure to do the inserts will be slightly faster than not using one.
Another point to think about is if the insert also requires some reads from associated tables, these may very well issue DB locks against the table. you can speed up this part if it is for reference only to use a READONLY select.
But as has already been said, we need to know how the DB insert is coded.
_________________
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 |
|
|
| pratik_oze |
| Posted: Sun Jun 17, 2012 8:37 pm Post subject: Database inserts frequency for high volumes - RT or batch? |
|
|
Apprentice
Joined: 22 Jun 2004
Posts: 34
|
| Passthru has been used in the esql codes to make the inserts. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Jun 18, 2012 4:38 am Post subject: Re: Database inserts frequency for high volumes - RT or batc |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| pratik_oze wrote: |
| Passthru has been used in the esql codes to make the inserts. |
Yes, but how is the insert coded? I agree that within the strictest interpretation the question "how has the insert been coded?" can be correctly be answered with "it's been coded with a passthru" but that's as accurate & as much use as a Microsoft Technote!
What insert code are you passing through?
Better information, better advice.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Jun 18, 2012 5:08 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
WMB does not handle database operation well, especially when ramping up volumes. You'll get better performance, throughput, and connection utilization if you follow one of two options: [1] front-end your database calls with a Web Service facade and use the SOAPRequest node to process your database operations -or- [2] use solidDb as your database backed by your DB2 instance. WMB does not manage database connections well and you should avoid accessing a database directly from WMB.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Jun 18, 2012 5:10 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| WMB does not manage database connections well and you should avoid accessing a database directly from WMB. |
You are extrapolating a general rule from your particular experience, and advocating a design goal based on your own architectural principles, rather than on a sound design based on the implementer's situation. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Jun 18, 2012 5:12 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| mqjeff wrote: |
| lancelotlinc wrote: |
| WMB does not manage database connections well and you should avoid accessing a database directly from WMB. |
You are extrapolating a general rule from your particular experience, and advocating a design goal based on your own architectural principles, rather than on a sound design based on the implementer's situation. |
Working off of the information provided in the question, I answered to the best of my ability and experience. Some people may have differing opinions. The OP asked for pointers. I advocate two pointers: Web Service facade or solidDb. Both of these pointers are IBM products.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Jun 18, 2012 5:18 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| I advocate two pointers: Web Service facade or solidDb. Both of these pointers are IBM products. |
Neither of these make sense if the original poster is an iSeries shop.
Wrapping all of your functional endpoints in webservice facades is not the solution to all problems. It works well in certain kinds of situations.
Implementing solidDB to frontend db2 on zOS to increase performance, when you could instead just tune the db2 connect client does not make sense.   |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Jun 18, 2012 5:18 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| lancelotlinc wrote: |
| WMB does not handle database operation well, especially when ramping up volumes. You'll get better performance, throughput, and connection utilization if you follow one of two options: [1] front-end your database calls with a Web Service facade and use the SOAPRequest node to process your database operations -or- [2] use solidDb as your database backed by your DB2 instance. WMB does not manage database connections well and you should avoid accessing a database directly from WMB. |
You seem to have skipped/ignored the bit on the original post where Oracle is mentioned at the back end DB and not DB2
_________________
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 |
|
|
| lancelotlinc |
| Posted: Mon Jun 18, 2012 5:21 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| smdavies99 wrote: |
| lancelotlinc wrote: |
| WMB does not handle database operation well, especially when ramping up volumes. You'll get better performance, throughput, and connection utilization if you follow one of two options: [1] front-end your database calls with a Web Service facade and use the SOAPRequest node to process your database operations -or- [2] use solidDb as your database backed by your DB2 instance. WMB does not manage database connections well and you should avoid accessing a database directly from WMB. |
You seem to have skipped/ignored the bit on the original post where Oracle is mentioned at the back end DB and not DB2 |
I was presenting options as a general guide. You could easily substitute solidDb for Oracle's equivalent, which is TimesTen.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Jun 18, 2012 5:30 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| mqjeff wrote: |
| lancelotlinc wrote: |
| I advocate two pointers: Web Service facade or solidDb. Both of these pointers are IBM products. |
Neither of these make sense if the original poster is an iSeries shop.
Wrapping all of your functional endpoints in webservice facades is not the solution to all problems. It works well in certain kinds of situations.
Implementing solidDB to frontend db2 on zOS to increase performance, when you could instead just tune the db2 connect client does not make sense. |
Lets say for example a message flow makes three database connections. Then, for throughput sake, you ramp up the additional instances of that message flow to nine additional instances, for a total of ten. How many database connections will result? Ten times three is thirty. Lets say that is MessageFlow called MessageFlow A. Lets say you have more message flows that need to interact with the database. MessageFlows B, C, D, E, F, G, H, I, J. Lets say each of those message flows has three connections and nine additional instances. Now how many database connections do you have? Thirty times ten flows is 300 connections.
Now lets say you facade your database connections with a Web Service. If the facade needs only one connection (highly likely), how many database connections do you need from your ten flows with ten instances? One. 300 vs. one.
For simplicity sake, I would like WMB to handle my database operations. But due to the poor way the database connections are managed in WMB version 7, it is not possible.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Jun 18, 2012 6:52 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
And let's not forget that best practice tells us to not use passthru to call a stored procedure but to declare it as external type database....
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
