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
 

 

CURSOR vs Singleton SELECT

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

New User


Joined: 20 Sep 2007
Posts: 17
Location: cheenai

PostPosted: Wed May 06, 2009 3:19 pm    Post subject: CURSOR vs Singleton SELECT
Reply with quote

Hi,

In a COBONLDB program there are ten singleton SELECTS on a same table for different criteria.Like,
Select COL
FROM TABLE A
Where
COL1 = 'constant criteria'
AND COL2 = 'val1'.

Select COL
FROM TABLE A
Where
COL1 = 'constant criteria'
AND COL2 = 'val2'.
And it goes on for ten different COL2 criterias.
Will there be any improvement If I change the above Selects into a Cursor. Like,
Select COL
FROM TABLE A
Where COL1='constant criteria'
AND COL2 IN ('val1','val2',...'val10').
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed May 06, 2009 3:24 pm    Post subject:
Reply with quote

you may want to investigate what is being done with the results of the select.
(i.e. what routines PERFORM the paragraphs containing the SELECTs)
Back to top
View user's profile Send private message
yogalakshmi

New User


Joined: 20 Sep 2007
Posts: 17
Location: cheenai

PostPosted: Wed May 06, 2009 3:28 pm    Post subject: Reply to: CURSOR vs Singleton SELECT
Reply with quote

If the row is found (when the SQLCODE of the SELECT is ZERO) , a cobol variable is set to true. Basically it acts like an existence check.
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: Wed May 06, 2009 11:54 pm    Post subject:
Reply with quote

Hello,

Performance impact could vary depending on how many rows contain the "constant value" and if col2 is defined in an index that is made up of col1/col2.
Back to top
View user's profile Send private message
yogalakshmi

New User


Joined: 20 Sep 2007
Posts: 17
Location: cheenai

PostPosted: Thu May 07, 2009 9:53 am    Post subject:
Reply with quote

The rows which contain the constant value are at the maximum of 15. There is no index for col2. But there is an index for col1 which is made up of four other columns.col1 is the first column in this index.Could you explain me how this would impact the performance.
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: Thu May 07, 2009 10:15 am    Post subject:
Reply with quote

Hello,

Is it possible that there would be multiple rows that contain the same value in col1 and col2? If yes, this is a consideration for successful execution.

The way the code is now, multiple rows will need to be read to confirm or reject if the col2 value is hit. If a new index could be added to the table that consists of only col1 and col2, the performance of the existance checking could be improved (again, the caution about duplicate col2 values - not a problem, just would need to be dealt with).

If a cursor is used and keyed by only col1, more rows will need to be processed.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu May 07, 2009 2:13 pm    Post subject:
Reply with quote

Lakshmi,

If you query is for existence alone, you dont even need to retreive the a column value from the table. You could try like the following.
Code:
Select 1
FROM TABLE A
Where
COL1 = 'constant criteria'
AND COL2 = 'val1'
FETCH FIRST 1 ROWS ONLY
FOR READ ONLY


Thank You,
Sushanth
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: Thu May 07, 2009 7:36 pm    Post subject:
Reply with quote

Hello,

Sorry, but if col2 is not part of an index with col1, multiple rows must be read until a hit is found for that value or all of the rows matching the col1 value have been read with no hit.

Keep in mind that internally many rows will be read that are never passed back to the application. That can be (usually is) tremendous overhead. And when the performance problem is looked at, some programmer or dba answers "we're only reading 1 row". Ooops. . .
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 Getting -504 Cursor Name GTT-ARTS-CUR... Robin Sulsona DB2 2 Fri Mar 17, 2017 1:43 am
No new posts Release of Adabas Cursor mohitsethi All Other Mainframe Topics 1 Mon Feb 06, 2017 8:36 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 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