Re: SQL performance - multiple rows for SELECT (SQL0811)

SamL wrote:
This is one of the uglier issues that I haven't satisfactorily solved in a
straightforward with SQL.

You probably want to use a WHERE EXISTS construct.

It sounds as if you have code like this:

clear var
SELECT aaa into :var from filea
WHERE bbb = :value
If var <> ' '
UPDATE fileb set xxx = 123
WHERE x = y

Actually, it isn't quite like that, which is why I didn't fully appreciate the power of your suggestion at first; Charles helped me along by expanding on your suggestion.

I've known how to do an update in the manner you suggest for a long time. However, the application isn't doint that. Instead, based on whether or not one or more qualifying rows exist, the program (COBOL) does some report processing logic.

See the reply I made to Charles's most recent. Translating the HLL part of it from COBOL to RPG, it winds up like this

[do SQL existence statement to load variable]

if exists > 0
exsr ExistsSR
else
exsr NoExistSR
endif


Thanks for your answer and time.



Try rewriting it to be something like this:
UPDATE fileb set xxx = 123
WHERE x= y and EXISTS
(select 1 from filea
where bbb = :value)

As I understand it, the EXISTS simply needs to deterime that the results set
contains 1 row. Therefore it doesn't need to read all the records of the
result set. Unfortuatley, I can't test this right now, so treat the code
with a degree of skepticism.

Sam

"Jonathan Ball" <jonball@xxxxxxxxxxxxxx> wrote in message
news:fpsNc.156$9Y6.72@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

I'm working on a project to enhance the performance of
some high-volume batch jobs that have lots of embedded
SQLin COBOL. Several of the jobs generate thousands of
pages of job log for message SQL0811 - 'Result of
SELECT more than one row.' Most of the queries that
return multiple rows are only trying to establish
existence of qualifying records, rather than return
data values that will be used in subsequent processing.

I've experimented with various reformulations of the
SQL statements in question to try to eliminate the
multi-row queries, but they all perform really badly -
orders of magnitude worse, in fact. The two I've tried
most recently are SELECT COUNT(*), and adding FETCH
FIRST 1 ROW ONLY to the statement. Both of those are
just *terrible*: a comparison with the multi-row
SELECT revealed that the latter was literally thousands
of times faster than the other two.

I've wondered about calling an API to remove the
occurrences of SQL0811, but that intuitively strikes me
as incurring a relatively high overhead compared to
letting the messages accumulate in the log. I also
don't know enough about these jobs to know if there are
any important logged messages that would be lost if I
were to adjust the jobs' logging level not to capture
the SQL0811.

Any comments or suggestions appreciated.






.

Relevant Pages