select single

[Former Member]

hi

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

[JozsefSzikszai]

than you should use SELECT COUNT( * )

[SuhaSaha]

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

[MarcinPciak]

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

[Former Member]

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

Rob

[SuhaSaha]

Hello Rob,

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

Suhas

[ThomasZloch]

What's so bad about INTO?

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

Thomas

[Former Member]

>

> 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

[JozsefSzikszai]

>

> 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)

[Former Member]

>

> 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

[JozsefSzikszai]

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

[Former Member]

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

Rob

[SuhaSaha]

Hello Eric,

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

Suhas

[Former Member]

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

[ThomasZloch]

>

> 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

[Former Member]

Hi,

The only option to use without INTO statement.

Tables: VBAK.

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

[ThomasZloch]

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

Thomas

[Former Member]

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.

[Former Member]

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.

[Former Member]

@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