MVSFORUMS.com

jim haire · Beginner Joined: 30 Dec 2002 Posts: 140 Topics: 40

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?

kolusu

misi01 · Posted: Mon Mar 28, 2016 1:53 am Post subject:

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

kolusu · Posted: Mon Mar 28, 2016 5:29 pm Post subject:

jim haire · Beginner Joined: 30 Dec 2002 Posts: 140 Topics: 40

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'?

kolusu · Posted: Mon Apr 04, 2016 1:22 pm Post subject:

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
www.linkedin.com/in/kolusu