| Author |
Message
|
| kash3338 |
 Posted: Wed Dec 08, 2010 10:56 pm Post subject: Use of Shared variables |
 |
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
Hi, We have a scenario in our project where we have to fetch few values from Database.
We came across a suggestion to use Shared Row to load the values from the Database initially when the flow runs and later fetch the values from the Shared Row. One of the drawback that we found in this usage is that, we have to reload the execution group every time we insert/delete/update he database.
Can anyone suggest the better approach in terms of effenciency, can we use a select query directly or can we have a shared row to fetch the values from DB at once?
The number of rows to be fetched from the database is at max 25. |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Wed Dec 08, 2010 11:56 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Think about the following
1) Add a timenode to do periodic DB reads & Shared Row refreshes.
2) An an MQ Input Node to do the above. Send any message to the flow to get the DB vaues refreshed. Thus using WMQ as a trigger.
We use 1) above on a 24 hour clock.
The refresh cycle really depends upon how often the vaues in the DB will change.
On another project, we used a 30sec refresh time because that was the agreed SLA
_________________
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 |
|
|
| Herbert |
| Posted: Thu Dec 09, 2010 12:51 am Post subject: Re: Use of Shared variables |
|
|
Centurion
Joined: 05 Dec 2006
Posts: 146
Location: Leersum, The Netherlands
|
| kash3338 wrote: |
We came across a suggestion to use Shared Row to load the values from the Database initially when the flow runs and later fetch the values from the Shared Row. One of the drawback that we found in this usage is that, we have to reload the execution group every time we insert/delete/update he database.
Can anyone suggest the better approach in terms of effenciency, can we use a select query directly or can we have a shared row to fetch the values from DB at once? |
A option is to add a second input node to the flow to reset the shared row(s), I use the HTTPInput node for this. That way after updating the database you can execute a URL in your browser and the shared row is reloaded from the database.
hgj |
|
| Back to top |
|
|
| zpat |
| Posted: Thu Dec 09, 2010 12:55 am Post subject: |
|
|
Jedi Council
Joined: 19 May 2001
Posts: 5866
Location: UK
|
Why not store the date/time of the data load in a shared variable as well?
Each time you access the shared variable data, check this value and if it beyond a certain age limit, reload the data from the db.
This way it is automatic and does not require any intervention.
This age limit could be stored as an external property of the flow. |
|
| Back to top |
|
|
| kash3338 |
| Posted: Thu Dec 09, 2010 1:16 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
Thanks for the options. I will try applying one of this.
My doubt is, is using the Shared Row a better choice or can we directly query the Database everytme? The amount of data that we have is not so large though. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Dec 09, 2010 2:44 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
There are lots of factors that will influence your decision.
Most of them relate to performance. A few of them are:-
1) How many instances of the flow are there?
2) What is the message rate?
3) What is the probability of DB row locking causing prodlems
4) Is the DB being accessed local or remote?
5) Is your DB read coded to be the READ ONLY
etc etc etc
Only you will know the answers to these.
_________________
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 Dec 09, 2010 3:17 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The more important factor is "how often will the data change"? If the data changes frequently, then it's better to use a direct lookup.
if the data only changes once a day or once a week or etc. then use some kind of caching - shared row for example. |
|
| Back to top |
|
|
|
|
