Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Using One big Cursor vs Multiple Cursors

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
briansheehan

New User


Joined: 02 Jun 2007
Posts: 5
Location: ireland

PostPosted: Sun Jun 03, 2007 3:09 am    Post subject: Using One big Cursor vs Multiple Cursors
Reply with quote

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
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Sun Jun 03, 2007 3:24 am    Post subject: Re: Using One big Cursor vs Multiple Cursors
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Jun 03, 2007 4:19 am    Post subject:
Reply with quote

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
View user's profile Send private message
munikumar
Currently Banned

New User


Joined: 18 Apr 2007
Posts: 24
Location: India

PostPosted: Thu Jun 07, 2007 10:49 am    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Converting multiple VB files to FB fi... Viswanath Reddy JCL & VSAM 6 Mon Aug 08, 2016 11:49 pm
No new posts Execute multiple DB2 Load commands in... faizm DB2 4 Wed Aug 03, 2016 12:53 pm
This topic is locked: you cannot edit posts or make replies. JCL MULTIPLE STEP EXECUTION QUERY Susanta JCL & VSAM 18 Sat Jul 30, 2016 1:17 pm
No new posts Variable for cursor names ravikumar15 DB2 5 Sun Jul 17, 2016 7:08 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us