SQL to get the max or min record which one is better?!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peggy Wu - 14 Mar 2006 19:13 GMT

dear all,
I am a programmer ,coding in IBM COBOL and the DB is DB2 V6 on OS/390.
As below, for the performance issue, Does anyone know which one is better
??

To get a record that field A has a maximum value,
either I could declare a cursor to select the qualified records order by
field A descending,
then fetch the 1st record.
(exec-sql
declare cur01
select * from table B
order by field A desc
exec-end
.................
exec-sql
fetch cur01 -------> fetch only 1 time
into :host-vrecord
exec-end)
or get the max(A) from this table and select the record that field
a equal maximum value
(exec-sql
select * from table B
where fieldA = ( select max(field A) from table B)
exec-end)

thank you for your kindly help,

Sincerely yours,
Peggy Wu
mailto: rtomato@ms40.url.com.tw

Reply to this Message

ShengTC@gmail.com - 15 Mar 2006 05:04 GMT

I think if you create a index that "allows reverse scan", then it
makes no difference either way.

Reply to this Message

ShengTC@gmail.com - 15 Mar 2006 05:04 GMT

I think if you create a index that "allows reverse scan", then it
makes no difference either way.

Reply to this Message

Peggy Wu - 15 Mar 2006 16:38 GMT

Thanks for the fast reply!! This helped greatly.
I'll try it !!
<ShengTC@gmail.com>
???????:1142395489.094020.155170@v46g2000cwv.googlegroups.com...
I think if you create a index that "allows reverse scan", then it
makes no difference either way.

Reply to this Message

Phil Sherman - 15 Mar 2006 15:25 GMT

An ascending index allowing reverse scans (see ShengTC's reply) or a
descending index on column A will give the best performance. You should
use the singleton select:
select * from B into :host-vrecord
where col A = (select max(col a) from b)
This will minimize the number of communications between your program and
DB2, decreasing CPU costs. This will work only if column a is unique. If
it isn't, you will have to use a cursor to fetch the rows.

If you have only an ascending index on column A then:
1. "Select * ... order by ... desc" will sort the entire table into
descending sequence then start passing you the data rows.
2. Obtaining the MAX value should scan the index. Using the value for a
single row retrieval will avoid any sorting. The SQL statement for a
descending index (above) should work well.

If there's no index, then you will have to either sort the entire table
or make two scans of the table - one to get the MAX value, one to
retrieve the data. Again, the SQL statement for a descending index
(above) should work well. The optimizer may be smart enough to recognize
that this could be done in one scan and do it that way.

Don't forget to use the EXPALIN facility to determine how the optimizer
will attempt to do each of the retrievals.

Philip Sherman

> dear all,
> I am a programmer ,coding in IBM COBOL and the DB is DB2 V6 on OS/390.
[quoted text clipped - 29 lines]
>
>

Reply to this Message

Peggy Wu - 15 Mar 2006 16:39 GMT

Thanks for the fast reply!! This helped greatly.
I'll try it

"Phil Sherman" <psherman@ameritech.net>
???????:OtVRf.6475$%m4.3047@newssvr33.news.prodigy.com...
An ascending index allowing reverse scans (see ShengTC's reply) or a
descending index on column A will give the best performance. You should
use the singleton select:
select * from B into :host-vrecord
where col A = (select max(col a) from b)
This will minimize the number of communications between your program and
DB2, decreasing CPU costs. This will work only if column a is unique. If
it isn't, you will have to use a cursor to fetch the rows.

If you have only an ascending index on column A then:
1. "Select * ... order by ... desc" will sort the entire table into
descending sequence then start passing you the data rows.
2. Obtaining the MAX value should scan the index. Using the value for a
single row retrieval will avoid any sorting. The SQL statement for a
descending index (above) should work well.

If there's no index, then you will have to either sort the entire table
or make two scans of the table - one to get the MAX value, one to
retrieve the data. Again, the SQL statement for a descending index
(above) should work well. The optimizer may be smart enough to recognize
that this could be done in one scan and do it that way.

Don't forget to use the EXPALIN facility to determine how the optimizer
will attempt to do each of the retrievals.

Philip Sherman

Peggy Wu wrote:

> dear all,
> I am a programmer ,coding in IBM COBOL and the DB is DB2 V6 on OS/390.
[quoted text clipped - 30 lines]
> Peggy Wu
> mailto: rtomato@ms40.url.com.tw

Reply to this Message