|
View previous topic :: View next topic
|
| Author |
Message |
briansheehan
New User
Joined: 02 Jun 2007
Posts: 5
Location: ireland
|
|
|
|
I am creating about 250,000 records, and writing these out to a file.
As my data is extracted from numerous tables at different levels, originally I used three big cursors and looped within them to format my data. Due the the sheer volume of data, the job took over 2 hours to run. Thus, as I figured most of the job is IO, if I combined the three cursors, it could do the IO work up front, then the job would take less time. However it took about twice as long to run this way.
Would you not expect the use of on big cursor in this instance to shorten the run time?
Thanks |
|
| Back to top |
|
 |
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
| briansheehan wrote: |
| Would you not expect the use of on big cursor in this instance to shorten the run time? |
You are talking cursors, so I am thinking DB2...
In the general way things go, I have found that the more complexity in the select, the higher the overhead.
Sometimes, doing simple (keyed) selects on multiple tables to temporary tables and then doing the complex select against these much smaller and simpler tables is much faster and less expensive that "one big cursor"...
But then again, the right stats, and indexes, it could go the other way..... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
If you post the predicates from the selects in the cursors and a bit of info about the tables (how indexed, how many rows, etc), we may be able to offer suggestions.
250,000 is not really a large volume of records to create, but if the db2 system has to look thru millions and millions of rows to get them, it will run unacceptably long.
Look to make sure there are not repeated full-table scans or that none of the joins is generating a cartesian product.
Depending on how the data is being processed, it may be much faster to unload some or all of the tables to sequential datasets and process externally. Some very large batch processes run exponentially faster when taken out of the database environment. |
|
| Back to top |
|
|
munikumar
Currently Banned
New User
Joined: 18 Apr 2007
Posts: 24
Location: India
|
|
|
|
Here are some of the Query tuning tips:
1.Use the appropriate indexed predicates in select statements.
2.If you are using more than one table, better to use JOINs instead of sub-queries.
3.Do not use SELECT *
4.Try to avoid aggregate function on non-numeric columns
Also there are lot of external points that matters your query tuning. |
|
| Back to top |
|
|
|
|
