| Author |
Message
|
| raghav_138 |
 Posted: Thu Oct 08, 2009 6:57 pm Post subject: Dataase ESQL Insert versus DataInsert Node |
 |
|
Newbie
Joined: 04 Aug 2009
Posts: 9
|
Hello,
Which way of inserting data is faster?
I have a table containing 25 columns and will be inserting thousands of records into it.I am running the flow as transaction so that everything is commited or every thing is rollbacked.
I want to know which way would be fater to insert data into database.2
1.using ESQL insert statement
2.using DataInsert node
Also are there any articles on optimizing performance of message flows?
All you ideas and inputs would be a great help to me |
|
| Back to top |
|
 |
| Gaya3 |
| Posted: Thu Oct 08, 2009 10:35 pm Post subject: Re: Dataase ESQL Insert versus DataInsert Node |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
| raghav_138 wrote: |
Hello,
Which way of inserting data is faster?
I have a table containing 25 columns and will be inserting thousands of records into it.I am running the flow as transaction so that everything is commited or every thing is rollbacked.
I want to know which way would be fater to insert data into database.2
1.using ESQL insert statement
2.using DataInsert node
Also are there any articles on optimizing performance of message flows?
All you ideas and inputs would be a great help to me |
both are more over same.
i suggest you to use ESQL [ Compute Node] so you can finish it off everything by using One Node.
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Oct 08, 2009 10:41 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
There are a large number of things which CAN affect the performance of your flow.
From my experience the top 2 (I know others may well have a different set) are as follows:-
- Is the DB I'm inserting into Local (on the same machine) or Remove(over a network)
- Use Stored Procedures on the DB wherever possible.
The first can be a real can of worms. On one project I developed we had to use a remote SQLServer DB. In Dev/Test this was running on a totally inadequate Single Core PC. This really made our inserts/second look bad.
Moving the SQLServer DB to a more representative system AND Sorting out network issues (like TCP/IP doing a DNS Lookup for every connect/insert) we improved the throughtput by 300%
My suggestion is that you do a benchmark and determine the various CPU & Network loadings and use that as a starting point. But before that you need to know what the business expects to be the traffic rates in a worse case scenario. That is your target.
If you can't meet that right away, then I'm afraid there is a lot of work ahead. But, as I indicated above, you have to make sure that your benchmarking Hardware is pretty well identical to that which is going to be used in production otherwise you are just putting up a finger into the air and guessing which way the wind is blowing.
_________________
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 |
|
|
| mqjeff |
| Posted: Thu Oct 08, 2009 11:34 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
A really great way to do a bench mark is to deploy the Sample flows that cover areas you are interested in benchmarking.
Then you can compare your numbers against *the exact same flows* that were used in the Performance Report Support Pacs. |
|
| Back to top |
|
|
| Herbert |
| Posted: Thu Oct 08, 2009 11:54 pm Post subject: |
|
|
Centurion
Joined: 05 Dec 2006
Posts: 146
Location: Leersum, The Netherlands
|
| smdavies99 wrote: |
| - Use Stored Procedures on the DB wherever possible. |
Indeed, this can speed up things a lot, in particular if the DB is on a remote host and you can combine SQL statements in one stored procedure. |
|
| Back to top |
|
|
| raghav_138 |
| Posted: Mon Oct 12, 2009 8:30 pm Post subject: |
|
|
Newbie
Joined: 04 Aug 2009
Posts: 9
|
Thanks all for your inputs.
Due to some restrictions we can't use the stored procedures in DB.
I have made a comparision and seen that ESQL insert statement is offering better performance compared to DataInsert node. |
|
| Back to top |
|
|
|
|
