| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Multiple Database Inserts in the same Compute Node |
« View previous topic :: View next topic » |
| Author |
Message
|
| Sam99 |
 Posted: Thu May 16, 2013 9:26 am Post subject: Multiple Database Inserts in the same Compute Node |
 |
|
Apprentice
Joined: 19 Jul 2012
Posts: 43
|
Hello All,
I've a repeating element in my XML, each repeating element must be inserted as a new row in the database.
Volume : 1000 messages
We are using Stored Procedure to insert the data in SQL server
Is it a good idea to do this in a sinlge compute node ?
Is there any better way to achieve this in a performant way ?
Many Thanks |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Thu May 16, 2013 9:57 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
>> Is it a good idea to do this in a sinlge compute node ?
Yes.
>> Is there any better way to achieve this in a performant way ?
Compute/Database node is the most efficient.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| new2z |
| Posted: Fri May 17, 2013 12:54 pm Post subject: |
|
|
Novice
Joined: 16 May 2013
Posts: 15
|
@Sam99
You should do it in one Compute Node
For better performance, what you may want to do is
1. Make the stored procedure call only once. Let the stored procedure loop through your data and insert it in to new rows. use the transaction container of your RDBMS for these inserts.
Do not make connection to the database everytime you want to insert a new row, esp when you are speaking about 1000 rows at one go within matter of seconds. That means, 1 thread will end up making 1000 connections within secs.
Better to have only 1 touchpoint with the DB, and pas all that is required by the DB to a stored proc and let the stored proc do the looping. It will be faster that way. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue May 21, 2013 5:47 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| new2z wrote: |
| Do not make connection to the database everytime you want to insert a new row, esp when you are speaking about 1000 rows at one go within matter of seconds. That means, 1 thread will end up making 1000 connections within secs. |
That's not the way Broker ODBC works. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Wed May 22, 2013 1:45 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| mqjeff wrote: |
| new2z wrote: |
| Do not make connection to the database everytime you want to insert a new row, esp when you are speaking about 1000 rows at one go within matter of seconds. That means, 1 thread will end up making 1000 connections within secs. |
That's not the way Broker ODBC works. |
This is what the Infocenter says about ODBC Connections,
| Quote: |
The number of connections to a user database that a broker requires depends on the actions of the message flows that access the database. For each message flow thread, a broker that accesses a user database makes one connection for each data source name (DSN). If a different node on the same thread uses the same DSN, the same connection is used, unless a different transaction mode is used, in which case another connection is 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
|
|
|
|
