|
View previous topic :: View next topic
|
| Author |
Message |
prasanthiv
New User
Joined: 07 Dec 2008
Posts: 6
Location: INDIA
|
|
|
|
Hi, we have DB2 SP being called from Batch Program for 400000 times. SP has nearly 40 SQL Queries to execute for each call from Batch Program. Nearly 30 SQL Queries are using SUM function.
Currently this process is taking 24 Min CPU time and 4-5Hrs of Elapsed Time.
Now we are in the process of fine tune this process.
I would like to know in general how many maximum SQL queries can be coded inside SP?
How many max input calls allowed for SP for better performance?
Thanks |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
why are you looking at the SP to fine tune,
when it sounds as if the whole process needs to be revamped?
is this a daily, weekly, monthly, quarterly run?
you mention SUM; have you thought about MQT - Materialized Query Tables or simply building and storing aggregate values.
Tuning any SQL means eventually you reach a point where the time involved means
data has to be crunched and it takes time.
you need to reduce the 400000 CALLs and the 40 queries.
that is 16 million sql invokations (CALLs to db2)
which in itself (just forget about what the query has to do) takes time.
and these questions:
| Quote: |
I would like to know in general how many maximum SQL queries can be coded inside SP?
How many max input calls allowed for SP for better performance? |
sounds as if you are one of Mac's mechanics -- making the horn blow louder since the breaks can not be fixed. |
|
| Back to top |
|
|
prasanthiv
New User
Joined: 07 Dec 2008
Posts: 6
Location: INDIA
|
|
|
|
| Thanks for your reply. This is a weekly Job. Yes Exactly we need to change the whole process. I am thinking of shifting to Parallel processing to handle 400000 CALLs. Is there any better way apart from running SP parallel y ? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
you are not thinking about changing the process,
you consistanly want the existing process to run faster.
without knowing something of the application, tables, etc...
and knowing the goal of this batch run and how is works within the complete system,
what you are asking for is free consultant work.
suggest you look into other methods to accomplish the goal of this process,
(i.e. have the aggregation of information done with/on a different way)
just to answer your question about parallel SP tasks,
anytime you can split up the work into multiple jobs and
they do not conflict
you have the potential of speeding-up your processing,
but you would have to test in your environment to determine is there is any gain to be found. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| Hi, we have DB2 SP being called from Batch Program for 400000 times. SP has nearly 40 SQL Queries to execute for each call from Batch Program. |
Make sure you know who decided was a proper approach and make sure thry are no longer to provide this kind of specification.
Had anyone done anything approaching volume testing of this process, it wold have been seen that the approach was self-defeating. What happens here quite often is someone has this "great idea" implements the design/code and runs a few iterations and decide this is good. Then when the normal volume is run, it all comes crashing done.
If you discard this now and implement a better solutiin, you will save considerable lost time & effort. |
|
| Back to top |
|
|
|
|
