SQL performance - multiple rows for SELECT (SQL0811)
Jonathan Ball
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.
Drew Dekreon
come up with some surprisingly good analysis.
It sounds like you're using a SELECT..INTO formulation instead of a
DECLARE CURSOR/FETCH, so the fact that multiples match would be a problem.
I've solved this by going to DECLARE/FETCH/CLOSE or by simply using RPG
to access the record (if I'm just trying to confirm existence).
Birgitta Hauser
If you want to return only one record, if there are several available,
use Count(Distinct UniqueValue)
Example:
Suppose you have the following file:
OrderNo Position Article Pieces
100 1 ABC 10
100 2 XYZ 20
100 3 XXX 30
200 1 AAA 50
200 2 XXX 30
To determine if OrderNo 100 is available:
Select Count(Distinct OrderNo) into :MyVar MyInd
from MyFile
or to determine if Article XXX is available:
Set :MyVar MyInd = Select Count(Distinct Article) from MyFile
Birgitta
SamL
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
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" <jon...@whitehouse.not> wrote in message
news:fpsNc.156$9Y6...@newsread1.news.pas.earthlink.net...
Jonathan Ball
I know how to do various techniques to return only one
row, but they're horribly inefficient. If you're just
doing a few in an on-line application, it might not
matter; but in a high-volume batch environment,
techniques like that can make what should be a 10-15
minute job stretch into hours. I should know: I just
took quite a lot of DISTINCT and COUNT instances *out*
of various programs, and replaced them with other queries.
Jonathan Ball
> did you try pasting the sql code into the sql analyzer in OpsNav? It can
> come up with some surprisingly good analysis.
I did that earlier, and it showed me that the optimizer
goes through extra steps to do SELECT COUNT(*) and
FETCH FIRST 1 ROW ONLY.
More tellingly, I serially ran jobs in batch repeating
three test queries 10,000 times each:
SELECT column_name
INTO :host-var
FROM ....
SELECT COUNT(*)
INTO :host-count-var
FROM ....
SELECT column_name
INTO :host-var
FROM ....
FETCH FIRST 1 ROW ONLY
I'd watch the record I/O on each job using WRKJOB
option 14, and the first one - that's the one that
generates the SQL0811 messages - would fly along at
literally thousands of I/O per second; the job would
end in just a few seconds.
The other two were doing maybe THREE reads per second.
> It sounds like you're using a SELECT..INTO formulation instead of a
> DECLARE CURSOR/FETCH, so the fact that multiples match would be a problem.
It's only a "problem" in the sense that it generates
the error message, and the job log winds up at
thousands of pages. It's a nuisance for someone in
operations to have to delete the job log, but
throughput is only relevant criterion in this environment.
> I've solved this by going to DECLARE/FETCH/CLOSE or by simply using RPG
> to access the record (if I'm just trying to confirm existence).
The cursor OPEN/FETCH/CLOSE also is going to generate a
fair amount of overhead, and I suspect will run a good
deal slower than doing the simple SELECT...INTO. If
the throughput were still lousy, I wouldn't hesitate to
switch to 'native' I/O (it's COBOL, not RPG, but no
matter.) I'm trying to get the application to run in a
decent amount of time while touching the original code
as little as possible. Since this particular job runs
in an acceptable time, and its only failing is to
generate the thousands of pages of joblog, I'll
probably leave it as is.
Thanks for your thoughts and for taking the time to reply.
Charles Wilt
Sam already gave you an answer, you need to use the "exists" predicate.
Let me give you another example:
select 1 into :host-var :null-ind
from sysibm/sysdummy1
where exists ( select 1 from myfile
where mykey = 'THEKEY'
)
sysibm/sysdummy1 is a 1 record table that comes in handy.
HTH,
Charles
In article <fpsNc.156$9Y6...@newsread1.news.pas.earthlink.net>,
jon...@whitehouse.not says...
Charles Wilt
possible to get rid of the null indicator, so I played around with it.
Here's one that doesn't need it:
select
case tbl.flag
when 1 then '1'
else '0'
end
into :host-var
from ( select 1 as flag
from sysibm/sysdummy1
where exists (select 1 from myfile
where mykey = 'THEKEY'
)
) as tbl right join sysibm/sysdummy1 on 1=1
Definitely not very pretty.
Charles
In article <MPG.1b714d9de...@news.easynews.com>,
cw...@meaa.mea.com says...
Jonathan Ball
> The null indicator requirement was bugging me. I wondered if it was
> possible to get rid of the null indicator, so I played around with it.
>
> Here's one that doesn't need it:
>
> select
> case tbl.flag
> when 1 then '1'
> else '0'
> end
> into :host-var
> from ( select 1 as flag
> from sysibm/sysdummy1
> where exists (select 1 from myfile
> where mykey = 'THEKEY'
> )
> ) as tbl right join sysibm/sysdummy1 on 1=1
>
>
> Definitely not very pretty.
I don't think we can dress this pig up enough to make
it pretty, but why would you need to do the join? This
pig could at least go into a Waffle House:
select coalesce(flag,0)
into :exists
from ( select 1 as flag
from sysibm/sysdummy1
where exists
(select 1
from myfile
where mykey = 'THEKEY')
) tbl
Host variable EXISTS will be 1 if MYFILE contains a row
with 'THEKEY', 0 if it doesn't.
One thing I don't like about this approach is the use
of the dummy table. It isn't clear from your earlier
post if sysibm/sysdummy1 is a table you created, or if
you're saying it's some kind of always-available dummy.
I didn't see such a table on the only system (V4R5)
to which I can get access through the internet; I'll
have to check on a V5R2 system later today. In any
case, the dummy table is not a part of the business
application, and if it doesn't exist at all, I'd have
to create it at the beginning of the job; not such a
terrible burden, but I'm a contractor on this project,
and I've already gotten some grief from one of the
standards nazis about using a CREATE GLOBAL TEMPORARY
TABLE... that had a legitimate usage.
Doesn't getting the count of all records from a table
(without selection predicate) bypass doing any I/O on
the table, and get it from the external table
statistics? I seem to think I've read that somewhere.
If it's true, then this would work and obviate the
need for the dummy table:
select coalesce(the_count, 0)
into :exists
from ( select count(*) the_count
from mytable
where exists
(select 1
from mytable
where mykey = 'THEKEY')
) tbl
If EXISTS > 0, the key value exists; otherwise (= 0) it
doesn't.
I'll check this out later today and post the results.
Charles Wilt
In article <ufNNc.1096$cK....@newsread2.news.pas.earthlink.net>,
jon...@whitehouse.not says...
>
> I don't think we can dress this pig up enough to make
> it pretty, but why would you need to do the join? This
> pig could at least go into a Waffle House:
>
> select coalesce(flag,0)
> into :exists
> from ( select 1 as flag
> from sysibm/sysdummy1
> where exists
> (select 1
> from myfile
> where mykey = 'THEKEY')
> ) tbl
>
> Host variable EXISTS will be 1 if MYFILE contains a row
> with 'THEKEY', 0 if it doesn't.
Nope, coalesce works if flag is NULL. However, there's a difference
between flag being NULL and no records being returned.
You could use coalesce instead of the case, but you still need the join
in order to have a record returned with flag NULL when the exists fails.
>
> One thing I don't like about this approach is the use
> of the dummy table. It isn't clear from your earlier
> post if sysibm/sysdummy1 is a table you created, or if
> you're saying it's some kind of always-available dummy.
> I didn't see such a table on the only system (V4R5)
> to which I can get access through the internet; I'll
> have to check on a V5R2 system later today. In any
> case, the dummy table is not a part of the business
> application, and if it doesn't exist at all, I'd have
> to create it at the beginning of the job; not such a
> terrible burden, but I'm a contractor on this project,
> and I've already gotten some grief from one of the
> standards nazis about using a CREATE GLOBAL TEMPORARY
> TABLE... that had a legitimate usage.
sysibm/sysdummy1 is a system table. It was added it v5r1 or v5r2 I
think along with a bunch of other "standard" tables and view in order to
make DB2 iSeries match the other DB2 platforms. Oracle and most other
RDBS have equivalents.
>
> Doesn't getting the count of all records from a table
> (without selection predicate) bypass doing any I/O on
> the table, and get it from the external table
> statistics? I seem to think I've read that somewhere.
> If it's true, then this would work and obviate the
> need for the dummy table:
>
> select coalesce(the_count, 0)
> into :exists
> from ( select count(*) the_count
> from mytable
> where exists
> (select 1
> from mytable
> where mykey = 'THEKEY')
> ) tbl
>
>
> If EXISTS > 0, the key value exists; otherwise (= 0) it
> doesn't.
>
> I'll check this out later today and post the results.
>
Hm, sounds familiar and makes sense. But using Run and Explain shows
about a 8x-10x improvement using sysdummy1 vs. count(*). I tried with
an without the right join.
Charles
SamL
returning a row or you aren't. If you return a row, it can't be null, it's
a 1. If you don't return a row, then neither can you return null.
Drop the use of the null. After execution, SQLSTT = '00000' means a
existence, SQLSTT ='02000' means data doesn't exist.
Sam
PS. This is a useful discussion.//
FWIW, I did a little Goggling. Didn't find any iSeries specific stuff, but
here are two articles:
http://www.db2mag.com/story/showArticle.jhtml?articleID=12803232
http://www.dbazine.com/custard1.shtml
"Charles Wilt" <cw...@meaa.mea.com> wrote in message
news:MPG.1b714d9de...@news.easynews.com...
Jonathan Ball
> Comments inline...
>
> In article <ufNNc.1096$cK....@newsread2.news.pas.earthlink.net>,
> jon...@whitehouse.not says...
>
>>I don't think we can dress this pig up enough to make
>>it pretty, but why would you need to do the join? This
>>pig could at least go into a Waffle House:
>>
>> select coalesce(flag,0)
>> into :exists
>> from ( select 1 as flag
>> from sysibm/sysdummy1
>> where exists
>> (select 1
>> from myfile
>> where mykey = 'THEKEY')
>> ) tbl
>>
>>Host variable EXISTS will be 1 if MYFILE contains a row
>>with 'THEKEY', 0 if it doesn't.
>
>
> Nope, coalesce works if flag is NULL. However, there's a difference
> between flag being NULL and no records being returned.
>
> You could use coalesce instead of the case, but you still need the join
> in order to have a record returned with flag NULL when the exists fails.
I figured that out when I got into the office and tried
it out. You're right: in the event the existence
condition isn't met, no row is returned. However, in
this application, that's fine, because I can
initialized the host variable to zero, and then if it's
1 following the query, I know the records exist.
Furthermore, there now is no point in even doing the
COALESCE or the nested table expression. It now reads:
initialize exists.
exec sql
select 1
from sysibm/sysdummy1
where exists
(select 1
from myfile
where mykey = 'THEKEY')
end-exec.
if exists = zero
perform does-not-exist-para
else
perform does-exist-para
end-if.
Getting rid of that nested table expression is a very
good thing, because if I thought I'd have trouble
getting a DECLARE GLOBAL TEMPORARY TABLE past the
standards nazis at this place, using a nested table
expression would have landed my a** in a KZ.
>
>
>>One thing I don't like about this approach is the use
>>of the dummy table. It isn't clear from your earlier
>>post if sysibm/sysdummy1 is a table you created, or if
>>you're saying it's some kind of always-available dummy.
>> I didn't see such a table on the only system (V4R5)
>>to which I can get access through the internet; I'll
>>have to check on a V5R2 system later today. In any
>>case, the dummy table is not a part of the business
>>application, and if it doesn't exist at all, I'd have
>>to create it at the beginning of the job; not such a
>>terrible burden, but I'm a contractor on this project,
>>and I've already gotten some grief from one of the
>>standards nazis about using a CREATE GLOBAL TEMPORARY
>>TABLE... that had a legitimate usage.
>
>
> sysibm/sysdummy1 is a system table. It was added it v5r1 or v5r2 I
> think along with a bunch of other "standard" tables and view in order to
> make DB2 iSeries match the other DB2 platforms. Oracle and most other
> RDBS have equivalents.
Thanks for the tip.
>
>
>>Doesn't getting the count of all records from a table
>>(without selection predicate) bypass doing any I/O on
>>the table, and get it from the external table
>>statistics? I seem to think I've read that somewhere.
>> If it's true, then this would work and obviate the
>>need for the dummy table:
>>
>> select coalesce(the_count, 0)
>> into :exists
>> from ( select count(*) the_count
>> from mytable
>> where exists
>> (select 1
>> from mytable
>> where mykey = 'THEKEY')
>> ) tbl
>>
>>
>>If EXISTS > 0, the key value exists; otherwise (= 0) it
>>doesn't.
>>
>>I'll check this out later today and post the results.
>>
>
>
> Hm, sounds familiar and makes sense.
It also turned out to be absolutely horrible, in terms
of performance, so I shudder to think what a COUNT(*)
for a subset of the table - i.e., using a WHERE
predicate - would be. I wrote a quick-n-dirty to test
out various approaches, and the one getting the full
record count from the "real" database table was orders
of magnitude worse than the others. The one using the
dummy table was the best: 10,000 iterations in about 8
seconds. The full record count version was doing about
ONE iteration per second, so I abandoned that in a second.
Jonathan Ball
> 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.
Jonathan Ball
It wouldn't be one of my posts if I didn't forget some
key element. My programs never compile the first time,
either ;-)
>
> initialize exists.
>
> exec sql
> select 1
==> into :exists <==
Charles Wilt
You're correct. I realized that after the work required to get it to
return a 1 if it exists or a 0 if it doesn't.
I like the idea about just checking the SQLSTT. You wouldn't even need
the host variable in that case. Unfortunately, the pre-compiler won't
let you get rid of it.
Charles
In article <Q5CdncmCJp7...@comcast.com>, none@no_such_isp.com
says...
SamL
though I read something today about them being a new feature in V5R3.
Sam
"Jonathan Ball" <jon...@whitehouse.not> wrote in message
news:WzYNc.1561$cK....@newsread2.news.pas.earthlink.net...
Jonathan Ball
> What OS release are you on? I'm not familar with global temporary tables,
> though I read something today about them being a new feature in V5R3.
I'm at V5R2...I think. I haven't been at this outfit
very long.)
The following are equivalent:
declare global temporary table mystuff (mycol dec(5,0))
create table qtemp.mystuff (mycol dec(5,0))
The table built with DECLARE GLOBAL TEMPORARY TABLE is
always created in library QTEMP.
Here's a *very* strange phenomenon for you that I think
must be related to server jobs running under the QUSER
profile. When I create a table in QTEMP by either of
the above methods using the navigator SQL script
processor, the creation is successful, but I can't do a
thing with it: no INSERT, no DROP, no nothing. But if
I run the same table creation command in a "green
screen" STRSQL session, the table is fully usable. I'd
call the sysadmin about it, but I have no idea (yet)
how to reach him/her.