View previous topic :: View next topic
|
Author |
Message |
TS70363
New User
Joined: 07 Apr 2010 Posts: 94 Location: Bangalore, India
|
|
|
|
Hi,
Requirement - to be accomplished via COBOL
TABLE A : TABLE B = 1 : many
1. Select 1 row from TABLE A - about 600 bytes of data per row.
2. Fetch upto 100 records from TABLE B based on COL1 from TABLE A - about 20 bytes of data per row.
3. Perform 1 and 2 until a list of values - COL1, gets exhausted
Can any one suggest the better option in terms of efficiency, performance and cost from the below 2 - both the tables have millions of rows?
A. Use 1 cursor, use JOIN to combine TABLE A & TABLE B and retrieve the required rows.
B. Use 2 cursors, open cursor 1, fetch first row, open cursor 2, fetch all rows, close cursor 2, fetch second row in cursor 1, open cursor 2, fetch all rows, close cursor 2 and so on.
Thanks
TS70363 |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Try for multi row fetch for cursor 2 |
|
Back to top |
|
|
TS70363
New User
Joined: 07 Apr 2010 Posts: 94 Location: Bangalore, India
|
|
|
|
Hi Rohit,
Yes, cursor 2 is a multi-row fetch cursor..
But question over here is, which one of the 2 options is better |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hi TS70363,
Use Joins and give good filtering conditions in the WHERE clause(not flag indicators having low cardinality).
Thanks,
Sushanth |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Hi,
join should be the good option over here and have a multi row fetch , share your explain results. 1st option should give less MIPS than 2.so try it and share your results. |
|
Back to top |
|
|
|