topic Re: select single in Application Development and Automation Discussions

select single

Former Member — Wed, 28 Jan 2009 16:25:01 GMT

is there a way to use select single * without using into?

i just want to check the sy-subrc of the selection

thanks

Thread moved to Performance Tuning Forum

Edited by: Vijay Babu Dudla on Jan 28, 2009 11:02 PM

Re: select single

JozsefSzikszai — Wed, 28 Jan 2009 16:26:05 GMT

than you should use SELECT COUNT( * )

Re: select single

SuhaSaha — Wed, 28 Jan 2009 16:27:00 GMT

Hello,

No you have to use INTO with SELECT SINGLE.

i just want to check the sy-subrc of the selection

You should use SELECT COUNT * for this purpose.

BR,

Suhas

PS: Eric shall i say duplicate post for this or the Mods will understand from the timings

Edited by: Suhas Saha on Jan 28, 2009 9:57 PM

Re: select single

MarcinPciak — Wed, 28 Jan 2009 16:30:43 GMT

Yes, there is a way. Declare corresponding structure with tables. It will implicitly place an entry in that work area.


TABLES: tab_name.

SELECT SINGLE * FROM tab_name WHERE ....

Marcin

Re: select single

Former Member — Wed, 28 Jan 2009 16:31:34 GMT

Since COUNT is an aggregate, it will likely have poorer performance. I think SELECT SINGLE ... INTO...is the best way.

Rob

Re: select single

SuhaSaha — Wed, 28 Jan 2009 16:33:14 GMT

Hello Rob,

Point taken, but you cannot use SELECT SINGLE w/o INTO.

Suhas

Re: select single

ThomasZloch — Wed, 28 Jan 2009 16:39:52 GMT

What's so bad about INTO?

Reminds me of the question "How can I sort a table without using SORT?"...

Thomas

Re: select single

Former Member — Wed, 28 Jan 2009 16:40:23 GMT

Hi,

The only option to use without INTO statement.

Tables: VBAK.

SELECT SINGLE * FROM VBAK WHERE <CONDT>.
IF SY-SUBRC EQ 0.
ENDIF.

Re: select single

ThomasZloch — Wed, 28 Jan 2009 16:42:08 GMT

Still doing an INTO internally, you just don't see it...that's why TABLES is obsolete.

Thomas

Re: select single

Former Member — Wed, 28 Jan 2009 16:42:48 GMT


> Hello Rob,
> 
> Point taken, but you cannot use SELECT SINGLE w/o INTO.
> 
> Suhas

Yeah - I know it's not what the OP wanted. But it's the standard way to do it. Without any reason not to do it this way, that's what I would do.

Rob

Re: select single

Former Member — Wed, 28 Jan 2009 16:42:49 GMT

Yes you can, like this

TABLES: VBAK.

SELECT SINGLE * FROM VBAK WHERE VBELN = '00000100'.

Note that I have used TABLES statement, which automatically creates a work area under the dictionary table name, so VBAK itself will stand for the table as well as work area. So no separate work area is required for INTO.

Re: select single

Former Member — Wed, 28 Jan 2009 16:43:55 GMT

Yes you can, like this

TABLES: VBAK.

SELECT SINGLE * FROM VBAK WHERE VBELN = '00000100'.

Re: select single

JozsefSzikszai — Wed, 28 Jan 2009 16:44:21 GMT


> Since COUNT is an aggregate, it will likely have poorer performance. I think SELECT SINGLE ... INTO...is the best way.
> 
> Rob

I did a fast test on table BKPF (SE38 => Performance ...):

SELECT SINGLE * is clearly slower, than SELECT COUNT( * )

SELECT SINGLE <specific_field> takes more or less the same time, than SELECT COUNT( * )

(this is a development system, number of entries in BKPF: about 120K)

Re: select single

Former Member — Wed, 28 Jan 2009 16:50:08 GMT


> I did a fast test on table BKPF (SE38 => Performance ...):
> SELECT SINGLE * is clearly slower, than SELECT COUNT( * )
> SELECT SINGLE <specific_field> takes more or less the same time, than SELECT COUNT( * )
> 
> (this is a development system, number of entries in BKPF: about 120K)

Did you do each more than once and take the lowest?

Rob

Re: select single

JozsefSzikszai — Wed, 28 Jan 2009 16:51:46 GMT

I was pressing the 'Measure time' button more than ten times (in both cases).

Re: select single

Former Member — Wed, 28 Jan 2009 16:54:08 GMT

Well, it doesn't seem right. I'll try to look into it.

Rob

Re: select single

SuhaSaha — Wed, 28 Jan 2009 16:59:31 GMT

Hello Eric,

Keeping my fingers crossed !!! Share your findings with us Rob !!!

Suhas

Re: select single

Former Member — Wed, 28 Jan 2009 20:16:56 GMT

OK - not the results I expected. I ran this:

REPORT ztest LINE-SIZE 80 MESSAGE-ID 00.

TABLES: ekko.

PARAMETERS p_ebeln LIKE ekko-ebeln OBLIGATORY MEMORY ID bes.

DATA: start    TYPE i,
      end      TYPE i,
      dif      TYPE i,
      count    TYPE i.

SELECT COUNT( * ) FROM ekko
  INTO count
  WHERE ebeln = p_ebeln.

DO 5 TIMES.
  GET RUN TIME FIELD start.
  SELECT COUNT( * ) FROM ekko
    INTO count
    WHERE ebeln = p_ebeln.
  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for SELECT COUNT( * )', ':', dif, 'microseconds'.
ENDDO.

SELECT SINGLE ebeln FROM ekko
  INTO ekko-ebeln
  WHERE ebeln = p_ebeln.

DO 5 TIMES.
  GET RUN TIME FIELD start.
  SELECT SINGLE ebeln FROM ekko
    INTO ekko-ebeln
    WHERE ebeln = p_ebeln.
  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for SELECT SINGLE *  ', ':', dif, 'microseconds'.
ENDDO.

and got pretty much the same results as Eric. I also did an explain from ST05 and they were almost identical.

Mea culpa.

I ran this in a DB2 ECC6 environment. I'd be interested to learn of results from other releases (particulary older ones).

Rob

Edited by: Vijay Babu Dudla on Jan 28, 2009 11:01 PM

Re: select single

Former Member — Thu, 29 Jan 2009 07:54:25 GMT

@Rob,

what you expect is not completely wrong, there are recommendation out, which say, that count(*) will

first build up the aggregate over all entries. But in newer systems this is definitely not true. I can not say whether all systems behave that way, that they don't check all records.

The actual recommendations are however still different:

+ either


   SELECT COUNT(*)
                INTO numb
                FROM db
                UP TO 1 ROWS
                WHERE ...

or if it is a fully specified key, i.e. a SELECT wqith primary key


   SELECT SINGLE key1
                INTO ls_db_tab
                FROM db_tab
                WHERE key1 = ...  key2 = ...

The INTO is required in newer systems.

Siegfried

Re: select single

ThomasZloch — Thu, 29 Jan 2009 08:31:01 GMT


> I ran this in a DB2 ECC6 environment. I'd be interested to learn of results from other releases (particulary older ones).

No difference on ECC6 with Oracle 10. Sorry, can't help with older releases.

Thomas