Performance: What is the best way to check if a record exist on a table ?

[Former Member]

Hello,

normaly you can use:

select single * from tab where x = y.

But, concerning the performance, I think there's some other better commands ...

any idea ?

tks.

[Former Member]

Hi,

If you have the full key then use select single .. else use select upto 1 rows ...

Regards,

Srini.

[Former Member]

if you just want to know if the record is there (or not there) as part of another query, see the subquery usage outlined in tips and tricks (SE30). This method is exceptionally fast, compared to SELECT, FAEI, etc.

[volker_borowski2]

select single * from tab where x = y.

Hi,

if your DB stores table data in B-trees you can do it this way and have the benefit,

that you have already selected the data you might need.

if you have i.e. ORACLE without index-organized tables and do NOT need the data but

just the confirmation, that a record exists,

select only_key_fields from tab where x=y

will be faster, because you save the datablock fetch.

If your PK has a height of 2 or 3, this means 33% or 25% percent gain for the "gets",

which might be even more, if the possible required tableblock fetch might involve a physical read.

Volker

[former_member182566]

>

> if you have i.e. ORACLE without index-organized tables and do NOT need the data but
> just the confirmation, that a record exists,
> 
> 

> select only_key_fields from tab where x=y
> Volker

When you say only_key_fields I suppose you mean only_fields_from_the_index_used_if_any (which might or might not be the key). I suppose the safer way is to use

select x from tab where x = y

By using x itself you are making sure you won't need to read anything else.

[volker_borowski2]

Yes Rui, exactly, thanks for clearification.

Beside, if the case "if there IS any fitting index" does NOT fit, you can select any fields you like,

because oracle will do an FTS in that case

May be it would be even better to select a constant.

select '1' from table where x=y

Volker

[former_member192616]

Hi,

>

> May be it would be even better to select a constant.
> 
> select '1' from table where x=y
> 
> Volker

which isn't possible in OPEN SQL, is it? (Only in native or standard SQL i think... .

(Haven't tried to do so... and too tired to try now...)

Kind regards,

Hermann

[franois_henrotte]

it is now possible from ABAP 7.4

[kerem_kayacan]

I do it like

select count( * ) from dbtab up to 1 rows where field = value.

Seems reasonable to me..

[former_member192616]

>

> I do it like
> 

> select count( * ) from dbtab up to 1 rows where field = value.
> 
> 
> Seems reasonable to me..

well, that is something we should NOT do... we should count only if we want to know the number of existing rows...

[kerem_kayacan]

That statement is counting only 1 row. What's bad about that?

[former_member192616]

>

> That statement is counting only 1 row. What's bad about that?

are you sure about that? On ORACLE this might be true but on DB2 for example (i just ran it)

we do 1182 "get pages" for e.g. this statement:

select count(*) into sy-dbcnt from t100 up to 1 rows where sprsl = 'DE'.

"get pages" are the database pages that are touched... 1182 clearly indicates that we touch more than one row.

So on some database plattforms we clearly count more than one row (if there are more rows for the where predicates) even if we add "up to 1 rows" which is applied after counting here...(and in this example the result set is 1 rows in any case...)

Further more in my opinion a count is a count (is a count) which should be used if we want to count (which is not necessary if we want to know if one row exists or not)

Kind regards,

Hermann

[kerem_kayacan]

Thanks for the information you shared. I actually don't know what 'database pages' means but is there any chance that 1182 may be the records read until finding the right record?

[former_member192616]

Hi Kerem,

> Thanks for the information you shared. I actually don't know what 'database pages' means

the database stores the rows in pages (also called blocks). The I/O is done on a page basis not on

a row basis that is the smallest unit per i/O is a page (block) containing many rows... . "Get Pages" tells

us now how many pages have been touched that is how much i/O was done for a statement.

> but is there any chance that 1182 may be the records read until finding the right record?

no, these 2 statements

select sprsl into sp from t100 up to 1 rows where sprsl = 'DE'. ENDSELECT.

select single sprsl into sp from t100 where sprsl = 'DE'.

do only 1,5 "get pages"....

Kind regards,

Hermann

[former_member182566]

Hi Hermann,

Just out of curiosity, on DB2, what is in sy-dbcnt after the SELECT COUNT(*) UP TO 1 ROWS ?

(on oracle 10 it is always 1 if entries exist).

Rui

[former_member192616]

Hi Rui,

it is 1 as well. the result set of the count is 1 row

According to the documentation it is:

SQL statements set the content of sy-dbcnt to the number of processed table lines.

i would add in the database interface

Kind regards,

Hermann

[former_member182566]

sorry, but...

what if you do SELECT COUNT(*) INTO variable UP TO 1 ROWS ?

variable is also 1, or?

[former_member192616]

Hi Rui,

> what if you do SELECT COUNT(*) INTO variable UP TO 1 ROWS ?

> variable is also 1, or?

select count(*) into i from t100 up to 1 rows.

write: i, sy-dbcnt.

is 1 and 1.

for:

select count(*) into i from t100.

write: i, sy-dbcnt.

is 898.074 and 898.074

so my before mentioned in the database interface seems not to be correct... the processed lines in the DBI is 1 but sy-dbnct in this case has the number of counted rows (the result)

Kind regards,

Hermann

Edited by: Hermann Gahm on Nov 8, 2010 12:36 PM

[kerem_kayacan]

>

> Hi Kerem,
> 
> > Thanks for the information you shared. I actually don't know what 'database pages' means
> 
> the database stores the rows in pages (also called blocks). The I/O is done on a page basis not on 
> a row basis that is the smallest unit per i/O is a page (block) containing many rows... . "Get Pages" tells
> us now how many pages have been touched that is how much i/O was done for a statement.
> 
> >  but is there any chance that 1182 may be the records read until finding the right record?
> 
> no,  these 2 statements 
> 
>   select sprsl into sp from t100 up to 1 rows where sprsl = 'DE'. ENDSELECT.
> 
>     select single sprsl into sp from t100  where sprsl = 'DE'.
> 
> do only 1,5 "get pages"....
> 
> Kind regards,
> 
> Hermann

How could we learn the 'get pages' value?

[former_member192616]

Hi Kerem,

in transactin ST04 - Performance - Statement Cache

there is one column with "Average GETPAGE operations"

Kind regards,

Hermann

[Former Member]

This message was moderated.

[Former Member]

Interesting discussion, but the OP seems to have lost interest as soon as this was posted. No replies and no points assigned. I'd like to hear if he has gotten anything out of this.

Rob

[ThomasZloch]

I know he drops by from time to time, I always remember because of the delicate abbreviation of the last name in his posts (first spotted by pk a while ago).

We shall see

Thomas

[Former Member]

Hello to everybody have answered interesting things and contribute to solve this problem,

and hello also at moderator (they have answered some things too : )) aahahah

(The absence was because I had other problem in parallel with more priority face at problem's performance)

For me the best answer is done by Kerem Kayacan:

select count( * ) from dbtab up to 1 rows where field = value.

Why ? No data to declare, no variable to use !!!!

Anyway I was thinking at some commands like that:

Check exist DBTAB with X1 = Y1 and X2 = Y2.

and asking myself why sap donu2019t created a command like this to solve this frequently problem.

So, moderator, what is the best way to suggest it at SAP ?

Ciao

[Former Member]

>

> and asking myself why sap donu2019t created a command like this to solve this frequently problem.
> So, moderator,  what is the best way to suggest it at SAP ? 

Well, since it's probably the wrong answer, I wouldn't try suggesting it.

I think your first try (SELECT SINGLE) is probably the best.

Did you try a comparison??

Rob

[Former Member]

In select single I have to declare a variable where the results of the selection is stored ( or TABLES statement to use the header line).

In select count (*) I have nothing declare :

I prefere it if I have only check the record existence - why I have to declare some variables not used ??

Roberto.

[Former Member]

Your original question is "considering the performance...?"

I really doubt that any kind of aggregate function (COUNT) will give better performance than doing a SELECT SINGLE. But maybe I'm wrong. That's why I asked if you tried both and did a comparison. Have you done that yet??

How would avoiding declaring a variable improve performance? And what are you putting the results of the SELECT COUNT (*) into??

Rob

[kerem_kayacan]

SY-DBCNT holds the value. It's predefined anyway.

[former_member192349]

The abbreviation is brilliant!! Nice spot

[former_member192349]

For what it's worth: I did a comparison of SELECT SINGLE 1 field INTO versus SELECT COUNT(*) with PK fields in the where, both on big and small table:

SELECT SINGLE

Mean Runtime: 152,55 microseconds

SELECT COUNT(*)

Mean Runtime: 157,50 microseconds

So neglible difference.

However minimizing lines of code should never be the main driver when programming IMHO, it's more about structured programming and performance of course.

My 2 cents,

Glenn

[Former Member]

Caution, the result of your measurements depend on buffering and indexes. In particular, SELECT COUNT(*) like other aggregating SELECTs bypasses the buffer which might not be intended! Besides that, as Hermann Gahm already has indicated, there seem to be remarkable differences between the databases our customers use.

My favourite pattern is SELECT SINGLE <short_field_contained_in_an_index> [...].

In order to 'verify' whether this really is a good choice, I have compared several strategies for three different situations:

a) Large unbuffered table, where-clause specifies only part of key

b) Large table with single-record-buffering and where-clause specifying complete key

c) Same large table with single-record-buffering and where-clause specifying only part of key

My measurements ran in 7.40 systems on HANA and on MaxDB. Independent of the database, the quantitative relations between the results were similar and confirmed that my favourite is a good choice. Observe, in particular, how really bad the SELECT COUNT looks in the second example, where the table buffering otherwise saves enormously much time!

>>> Existence check for completely unbuffered table <<<

SELECT * FROM sbook INTO sbook_line
    WHERE carrid = 'LH'.
  EXIT.
ENDSELECT.
Duration in seconds for 500 executions:                 43,8334260

SELECT COUNT(*) FROM sbook UP TO 1 ROWS
    WHERE carrid = 'LH'.
Duration in seconds for 500 executions:                  0,7393800

SELECT * FROM sbook INTO sbook_line
  UP TO 1 ROWS
  WHERE carrid = 'LH'.
ENDSELECT.
Duration in seconds for 500 executions:                  0,7619870

SELECT carrid FROM sbook INTO carrid
  UP TO 1 ROWS
  WHERE carrid = 'LH'.
ENDSELECT.
Duration in seconds for 500 executions:                  0,5869470

SELECT SINGLE carrid FROM sbook INTO carrid
  WHERE carrid = 'LH'.
Duration in seconds for 500 executions:                  0,6001700

>>> Existence check for single-buffered table with complete key <<<

SELECT * FROM tadir INTO tadir_line
    WHERE     pgmid = 'R3TR'
          AND object = 'PROG'
          AND obj_name = sy-cprog.
  EXIT.
ENDSELECT.
Duration in seconds for 500 executions:                  0,0103200

SELECT COUNT(*) FROM tadir UP TO 1 ROWS
    WHERE     pgmid = 'R3TR'
          AND object = 'PROG'
          AND obj_name = sy-cprog.

Duration in seconds for 500 executions:                  0,6242380

SELECT * FROM tadir INTO tadir_line
  UP TO 1 ROWS
    WHERE     pgmid = 'R3TR'
          AND object = 'PROG'
          AND obj_name = sy-cprog.
ENDSELECT.
Duration in seconds for 500 executions:                  0,0103680

SELECT pgmid FROM tadir INTO pgmid
  UP TO 1 ROWS
    WHERE     pgmid = 'R3TR'
          AND object = 'PROG'
          AND obj_name = sy-cprog.
ENDSELECT.
Duration in seconds for 500 executions:                  0,0103690

SELECT SINGLE pgmid FROM tadir INTO pgmid
    WHERE     pgmid = 'R3TR'
          AND object = 'PROG'
          AND obj_name = sy-cprog.
Duration in seconds for 500 executions:                  0,0097770

>>> Existence check for single-buffered table with incomplete key <<<

SELECT * FROM tadir INTO tadir_line
    WHERE     pgmid = 'R3TR'
          AND object = 'PROG'.
  EXIT.
ENDSELECT.
Duration in seconds for 500 executions:                 25,2490160

SELECT COUNT(*) FROM tadir UP TO 1 ROWS
    WHERE     pgmid = 'R3TR'
          AND object = 'PROG'.
Duration in seconds for 500 executions:                  1,4555210

SELECT * FROM tadir INTO tadir_line
  UP TO 1 ROWS
    WHERE     pgmid = 'R3TR'
          AND object = 'PROG'.
ENDSELECT.
Duration in seconds for 500 executions:                  1,6207910

SELECT pgmid FROM tadir INTO pgmid
  UP TO 1 ROWS
    WHERE     pgmid = 'R3TR'
          AND object = 'PROG'.
ENDSELECT.
Duration in seconds for 500 executions:                  1,5180330

SELECT SINGLE pgmid FROM tadir INTO pgmid
    WHERE     pgmid = 'R3TR'
          AND object = 'PROG'.
Duration in seconds for 500 executions:                  1,2033210

[horst_keller]

From Release 7.40, SP05 on, you can do it like this:

http://help.sap.com/abapdocu_740/en/abensql_expr_literal_abexa.htm

Instead of a literal, you might also use a constant like abap_bool (I changed the example accordingly in SP08).

[ulrich_brenner2]

sry but these abap docu links you keep posting do not really work ->  ERR_CONNECTION_RESET

[Former Member]

for me was the same error...

I copied the link in a new Windows and was ok ...

[horst_keller]

"Don't know" says Button Bright .

For me they do work. And not only from inside SAP but also from tablet PCs in department stores ...

[SuhaSaha]

And not only from inside SAP but also from tablet PCs in department stores ...

Wow!! So you check the online documentation while shopping?

[horst_keller]

Yep, but my wife doesn't think its funny 

(and right she is ...)

[Former Member]

Maybe I'm totally missing something - I followed the code example you linked to get this:

SELECT SINGLE @abap_true

              FROM pa0002

              INTO @DATA(exists)
              WHERE perid = t_jk_dt-donorid
              AND   ENDDA = '99991231'.

IF ( exists = abap_true ).
  t_jk_dt-donorid = t_jk_dt-donorcode.
ENDIF.

However, upon error checking I get the following error:

(complete with the classic repetition bug ) Any ideas of what I'm doing wrong?

[horst_keller]

Using a release lower than 7.40, SP05?

See also

[Former Member]

I am on 7.40 SP10 (included screenshot of where I gather this, so that you'll understand if I'm doing this wrong)

Interestingly, if I try this code

1141 SELECT SINGLE pernr
1142       INTO @DATA(exists)
1143       FROM pa0002
1144       WHERE perid = t_jk_dt-donorid AND
1145             ENDDA = '99991231'.

I get this error:

Which is weird, because it is escaped! Could this have something to do with unicode/unicode checks?

Thanks