| Author |
Message
|
| ibolui |
 Posted: Sun Aug 15, 2010 6:53 pm Post subject: javacompute database query |
 |
|
Novice
Joined: 10 Aug 2010
Posts: 22
|
i am using a javacompute to create a connection to sqlserver 2005, using type4 driver. then i query a table with only 15 rows of data.
i realise that running the message flow 1500 times continuously took about 6.5mins. i do know there are other factors affecting the time as well..such as the complexity of the flow.
hence i replace the db query part with just reading from a file. and running it 1500 times again took only 10secs..
i am wondering is there anything that i can set or do for optimization??
thanks.. |
|
| Back to top |
|
 |
| bruce2359 |
| Posted: Sun Aug 15, 2010 7:14 pm Post subject: |
|
|
Poobah
Joined: 05 Jan 2008
Posts: 9469
Location: US: west coast, almost. Otherwise, enroute.
|
Moved to WMB forum.
_________________
I like deadlines. I like to wave as they pass by.
ב''ה
Lex Orandi, Lex Credendi, Lex Vivendi. As we Worship, So we Believe, So we Live. |
|
| Back to top |
|
|
| AkankshA |
| Posted: Sun Aug 15, 2010 7:57 pm Post subject: Re: javacompute database query |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
| ibolui wrote: |
i am using a javacompute to create a connection to sqlserver 2005, using type4 driver. then i query a table with only 15 rows of data.
i realise that running the message flow 1500 times continuously took about 6.5mins. i do know there are other factors affecting the time as well..such as the complexity of the flow.
hence i replace the db query part with just reading from a file. and running it 1500 times again took only 10secs..
i am wondering is there anything that i can set or do for optimization??
thanks.. |
Database connections have always been resource hungry and take a lot of time...
_________________
Cheers |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Sun Aug 15, 2010 7:58 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
What happens if you have the DB cache the table? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| bruce2359 |
| Posted: Sun Aug 15, 2010 8:12 pm Post subject: |
|
|
Poobah
Joined: 05 Jan 2008
Posts: 9469
Location: US: west coast, almost. Otherwise, enroute.
|
| Quote: |
| then i query a table with only 15 rows of data. |
My calculator says that each of the 1500 java-initiated call to the table took 0.25 seconds.
What kind of data is in the table? Is there an index?
_________________
I like deadlines. I like to wave as they pass by.
ב''ה
Lex Orandi, Lex Credendi, Lex Vivendi. As we Worship, So we Believe, So we Live. |
|
| Back to top |
|
|
| ibolui |
| Posted: Sun Aug 15, 2010 9:17 pm Post subject: |
|
|
Novice
Joined: 10 Aug 2010
Posts: 22
|
the table is a 2 columns string data. the index is the key..
0.25 for each call? that is less than 10sec and faster than reading from file?
i do not know about caching table.. will try it now.. |
|
| Back to top |
|
|
| bruce2359 |
| Posted: Sun Aug 15, 2010 10:18 pm Post subject: |
|
|
Poobah
Joined: 05 Jan 2008
Posts: 9469
Location: US: west coast, almost. Otherwise, enroute.
|
| Quote: |
| hence i replace the db query part with just reading from a file. and running it 1500 times again took only 10secs.. |
Did I misunderstand this? Did all 1500 reads take 10 seconds? Or did one read take 10 seconds?
Alternatively, is my math bad? I am taking a variety or prescription drugs, so it is possible.
You said that 1500 java calls takes 6.5 minutes. 6 minutes = 360 seconds + 30 seconds = 390 seconds. 1500 / 390 = 3.85 calls/second.
1500 reads from a file takes 10 seconds. 1500 / 10 = 150 reads/second.
Reading a file should be faster than Java + SQL call + database search.
| Quote: |
| the index is the key |
I'm confused. Does this index search lead to another table search?
_________________
I like deadlines. I like to wave as they pass by.
ב''ה
Lex Orandi, Lex Credendi, Lex Vivendi. As we Worship, So we Believe, So we Live. |
|
| Back to top |
|
|
| ibolui |
| Posted: Sun Aug 15, 2010 11:17 pm Post subject: |
|
|
Novice
Joined: 10 Aug 2010
Posts: 22
|
sorry for the confusion 
yes.. all 1500 executions of the flow took only about 10secs..
whereas if the flow is doing the db query, all 1500 executions took about 6.5mins
| Quote: |
You said that 1500 java calls takes 6.5 minutes. 6 minutes = 360 seconds + 30 seconds = 390 seconds. 1500 / 390 = 3.85 calls/second.
1500 reads from a file takes 10 seconds. 1500 / 10 = 150 reads/second.
Reading a file should be faster than Java + SQL call + database search. |
sorry.. but what are you trying to tell me??
| Quote: |
I'm confused. Does this index search lead to another table search? |
nope.. it does not leads to another table.
so i am looking at buffer caching and query caching. am i on the right track?
is there anything on mq or wmb that can be tune?
thanks =) |
|
| Back to top |
|
|
| bruce2359 |
| Posted: Mon Aug 16, 2010 6:21 am Post subject: |
|
|
Poobah
Joined: 05 Jan 2008
Posts: 9469
Location: US: west coast, almost. Otherwise, enroute.
|
| Quote: |
| sorry.. but what are you trying to tell me?? |
Reading from a file is faster than Java + SQL call + database search.
Having data reside in a db is a choice.
Does the data need to be in a db? Can the data reside in a file instead of a db?
_________________
I like deadlines. I like to wave as they pass by.
ב''ה
Lex Orandi, Lex Credendi, Lex Vivendi. As we Worship, So we Believe, So we Live. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Aug 16, 2010 6:47 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Do you need to read the data each time through the flow?
How did you read the data from the file? Using Java? |
|
| Back to top |
|
|
| ibolui |
| Posted: Mon Aug 16, 2010 5:15 pm Post subject: |
|
|
Novice
Joined: 10 Aug 2010
Posts: 22
|
yup i know reading a file is definitely faster. as for storing in db... it is what i have been tasked to do. of course i have reflected about this issue and if i cant improve the performance in time, will probably use a file instead.
| Quote: |
Do you need to read the data each time through the flow?
How did you read the data from the file? Using Java?
yes i need to read the data each time. i am also wondering if there is a way for me to store the data in memory or something, so that each flow instance does not need to query the db or read from file?
yes i use java to read from the file.. |
|
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Aug 16, 2010 7:42 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Look at atomic operations and shared variables or singletons if using Java.
Have fun
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| bruce2359 |
| Posted: Mon Aug 16, 2010 8:06 pm Post subject: |
|
|
Poobah
Joined: 05 Jan 2008
Posts: 9469
Location: US: west coast, almost. Otherwise, enroute.
|
Or use FileInput node to avoid Java.
_________________
I like deadlines. I like to wave as they pass by.
ב''ה
Lex Orandi, Lex Credendi, Lex Vivendi. As we Worship, So we Believe, So we Live. |
|
| Back to top |
|
|
| ibolui |
| Posted: Mon Aug 16, 2010 9:56 pm Post subject: |
|
|
Novice
Joined: 10 Aug 2010
Posts: 22
|
hmmmm.. i am not having any problems with the java file reading operation..
but i am having problems with the slow execution of db query using the javacompute node..
| Quote: |
| Look at atomic operations and shared variables or singletons if using Java. |
can we really use shared variables? because from what i understand..each message will create a new instance of the flow.. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Aug 17, 2010 12:38 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
It is a good timesaving trick to use a timer node to periodically read a row from a table that contains static data.
This bit of the flow uses a BEGIN ATOMIC .... END section to update the values.
Then the real flow also uses a BEGIN ATOMIC.. END (the same code) to set the values for its work.
This caters for multiple instances of the flow as well.
The shared variables hold the data but the Atomic bit copies them into a folder like the Environment.
The advantages of this is that it avoids possible DB Read Lock contentions is getting this stuff from a table.
It also avoids file lock issues if reading this stuff from a file when you are running additional instances of the flow.
I've use this method on a flow with 10 additional instances.
Without it we were getting horrible DB locking even with Read Only calls.
Thus performance suffered. Introduce this and the performance problems went away.
Ok, there is a little bit of code where only one thread can go at a time. If you keep this down to a minimum (ie low number of lines of code) then I am sure you will see the performance improve considerably. Also the locking is held within broker itself.
_________________
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 |
|
|
|
|
