MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Select without using a cursor and performance hit

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
jim haire
Beginner


Joined: 30 Dec 2002
Posts: 140
Topics: 40

PostPosted: Thu Mar 24, 2016 11:57 am    Post subject: Select without using a cursor and performance hit Reply with quote

I am doing some performance tuning on a COBOL program. There is a SELECT statement that is doing some existence checking.

I know that there can be more than 1 row returned from the SELECT statement. In fact, the developer actually checks for -811. I am going to add a "FETCH FIRST 1 ROWS ONLY" statement so once it finds the first row the query would stop. I am also not returning data from the table...just doing a "SELECT 1". Both of these are for performance reasons.

My question is this:
As the program was originally written, did the query return a maximum of two rows and then realize that the COBOL program was not properly equipped to handle the query (no cursor provided) or would the query determine the complete result set before returning the -811 SQLCODE?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Thu Mar 24, 2016 1:27 pm    Post subject: Re: Select without using a cursor and performance hit Reply with quote

jim haire wrote:
My question is this:
As the program was originally written, did the query return a maximum of two rows and then realize that the COBOL program was not properly equipped to handle the query (no cursor provided) or would the query determine the complete result set before returning the -811 SQLCODE?


Jim Haire,

The result set is NOT materialized before the query is run. FETCH FIRST 1 ROW is a better alternative and if you are merely checking the existence, why not use SYSIBM.SYSDUMMY1 which always have just 1 row.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
misi01
Advanced


Joined: 02 Dec 2002
Posts: 616
Topics: 171
Location: Stockholm, Sweden

PostPosted: Mon Mar 28, 2016 1:53 am    Post subject: Reply with quote

Kolusu. I'm going to assume that Jim needs to know whether, say, column_d in mytable has any rows with a value of X. On that basis, using sysdummy1 won't help him (or am I missing something)
_________________
Michael
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Mon Mar 28, 2016 5:29 pm    Post subject: Reply with quote

misi01 wrote:
Kolusu. I'm going to assume that Jim needs to know whether, say, column_d in mytable has any rows with a value of X. On that basis, using sysdummy1 won't help him (or am I missing something)


Here is the SQL I am talking about

Code:

 SELECT IBMREQD
   FROM SYSIBM.SYSDUMMY1                   
   WHERE EXISTS (SELECT *                   
                   FROM SYSIBM.SYSTABLES   
                  WHERE CREATOR = 'SYSIBM')   
 ;             


If you have any records that match the criteria, then you will have 1 record with "Y" or if your table does not contain any rows that do not match the criteria , you will get an sql code of +100.


You can use SELECT 1 instead of SELECT * in the outer query. So it does not matter how many records your outer query brings up, but the final from Sysibm.sysdummy will either be 1 or no records at all.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
jim haire
Beginner


Joined: 30 Dec 2002
Posts: 140
Topics: 40

PostPosted: Mon Apr 04, 2016 12:56 pm    Post subject: Reply with quote

Does the inner SELECT in this statement only return 1 row before the EXISTS is performed and then stops since a record was found or does the inner SELECT return all rows where the CREATOR = 'SYSIBM'?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Mon Apr 04, 2016 1:22 pm    Post subject: Reply with quote

jim haire,

Technically EXISTS should stop right after finding 1 row, but it may also depend on your WHERE clause in the outer query. You should run an EXPLAIN on both your original query and the EXIST query and pick the most optimal query.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group