Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

"SELECT SINGLE" vs. "SELECT UP TO 1 ROWS"

alejiandro_sensejl
Active Participant
0 Kudos

Hello everyone,

I've spend the last 2 hours comparing and researching the above mentioned statements and I don't have a clear result.

Is there any official documentation which statement should process a SELECT faster that I expect to return only one record as a result of accessing only non-(unique-)index fields?

Does this also depend on the used DBMS (ORACLE 10.2.0.4.0) and/or the SAP release (NW 7.01 SP6)?

Would be great to get some feedback from the performance gurus!

Thanks in advance and best regards,

Alej

28 REPLIES 28

martin_voros
Active Contributor

Hi,

according to note 135048 SAP adds same oracle hint FIRST_ROWS for both statements: UP TO x ROWS and SELECT SINGLE. So there should not be a big difference. It might be different for newer releases of Oracle. I use SELECT SINGLE when I have full primary key and UP TO 1 ROWS when I don't. You can use ST05 to figure out which query is better for your case.

Cheers

0 Kudos

Hi,

I think Martin's reply is correct. I use SELECT SINGLE when I have full primary key.

I use UP TO 1 ROWS when I have not full primary keyt.

Regards,

Gaito

0 Kudos

Hi,

 I use SELECT SINGLE when I have full primary key.
I use UP TO 1 ROWS when I have not full primary keyt. 

Sorry, But it is not necessary, whichever you want you can used it.

One major differnace is that For all enries not work with Select Single but work with Select up to 1 rows

But I don't think there is any performance difference between SELECT SINGLE and SELECT UP TO 1 ROWS

Kind Rgds

Ravi Lanjewar

Edited by: Ravishankar Lanjewar on Jul 8, 2010 11:49 AM

former_member194613
Active Contributor
0 Kudos

This is also my recommendation

I use SELECT SINGLE when I have full primary key.

I use UP TO 1 ROWS when I have not full primary key.

Reason is readability of code not performance!

Performance of the 2 statements is 99,9% identical, there is no performance issue related to these statements.

If you unterstand everything then it should be clear, that a SELECT SINGLE must always be fast.

However, a UP TO 1 ROWS can be fast but can also be very slow:

If there is no index support and only very few records fullfill the WHERE-clause, so the full table must be scanned.

However, this problem is not related to the statement, but only to the fact, that no index supports the WHERE. If done

frequently then an index is needed.

Siegfried

0 Kudos

> If you unterstand everything then it should be clear, that a SELECT SINGLE must always be fast.

> However, a UP TO 1 ROWS can be fast but can also be very slow:

> If there is no index support and only very few records fullfill the WHERE-clause, so the full table must be scanned.

> However, this problem is not related to the statement, but only to the fact, that no index supports the WHERE. If done frequently then an index is needed.

Here you again make a difference between SELECT SINGLE and UP TO 1 ROWS, that's wrong. There is no difference at all (as you say they are "99,9% identical"). What you indicate here is the issue when you don't use correct keys in the WHERE, that's not related to one of the 2 statements.

0 Kudos

very well said, Sandra. If anybody is in doubt, check this:

tables: t100.

select single * from t100 where text = 'not existing'.

select * from t100 up to 1 rows where text = 'not existing'. endselect.

e.g. on ORACLE: 2 full table scans (no index support)

reg. differences there are so often questions aobut select single / up to 1 rows...

I usually point out the single record buffer support

for select single but besides that do not make any comparison or differences since i think it

is not very helpful.

Same thing with "direct read" and "sequential read" ... i'm often asked what the difference is.... sigh

Former Member
0 Kudos

I believe the SELECT .. UP TO 1 ROWS has on theoretical level a longer runtime, though I'm pretty sure it's not measurable. Here's why: If you debug the statement, you'll see that it executes the SELECT, then goes to the ENDSELECT and comes back to the SELECT. So the interpreter seems to run a loop once, whereas a SELECT SINGLE is just one statement. (Ok, this is admittedly some handwaving argument...)

On Oracle the actual database statement (native SQL) is identical: Both use the hint FIRST_ROWS(1) and limit the result set via ROWNUM<=1.

Siegfried Boes wrote:

I use SELECT SINGLE when I have full primary key.

I use UP TO 1 ROWS when I have not full primary key.

Reason is readability of code not performance!

Interesting and in line with others, though in general I disagree with this statement. To me an idiomatic use of SELECT SINGLE is whenever you have conditions that ensure that only a single record can be returned. A unique index (not just primary key) should suffice and one could possibly argue to extend this with uniqueness known on application/functional level (admittedly more fragile and uncontrolled).

However, as far as actual usage is concerned I think the Extended Program Check spat out warnings if SELECT SINGLE is used without a primary key (don't have a system for checking this at the moment and if so, maybe there was some strong reason to do it that way?!). I think that was the reason to adopt usage of SELECT .. UP TO 1 ROWS in case of a SELECT SINGLE without a full primary key.

The part that I always disliked about the SELECT - ENDSELECT construct was the possibility of short dumps when debugging (commit). As far as I know this applies also with a UP TO 1 ROW limit, but not for the SELECT SINGLE.

Cheers, harald

0 Kudos

> The part that I always disliked about the SELECT - ENDSELECT construct was the possibility of short dumps when debugging (commit). As far as I know this applies also with a UP TO 1 ROW limit, but not for the SELECT SINGLE.

That's now corrected (most of time) with the new debugger as it runs by default in an "exclusive mode". See Note 726719 - ABAP debugging in production system / DEBUGGING_IMPOSSIBLE for more info.

0 Kudos

Sandra, Harald,

SLIN says:

The SELECT SINGLE is designed to allow a most efficient access to exactly one record of a database table. It requires that you specify the entire primary key in the WHERE condition with AND and EQ (or "="). Then an access to the database is possible, which requires only one communication step between application server and database server.

If you did not specify the entire primary key, instead of a direct access on the database server, a "normal" SELECT is performed, which is the same as a SELECT ... UP TO 1 ROWS. In this case, a cursor is opened, a record is read, and the cursor is closed. This requires a number of communication steps between application server and database server.

The extended program check provides a warning to inform you that with the mentioned SELECT SINGLE not the expected fast direct access can be performed.

but at least on ORACLE it seems to be not true.

On other databases it might be true... (would have to check) or maybe it USED to be true but is not

true anymore, or maybe database server should be database interface, or ... .


do 3 times.
select single * from t100 bypassing buffer where sprsl = 'D' and arbgb = 'D-' and msgnr = '001'.
select  * from t100 up to 1 rows where sprsl = 'D' and arbgb = 'D-' and msgnr = '001'. endselect.
enddo.

gives:


     7 T100       REOPEN             0 SELECT WHERE "SPRSL" = 'D' AND "ARBGB" = 'D-' AND "MSGNR" = '001'
   485 T100       FETCH       1      0
     6 T100       REOPEN             0 SELECT WHERE "SPRSL" = 'D' AND "ARBGB" = 'D-' AND "MSGNR" = '001' AND ROWNUM <= 1
   493 T100       FETCH       1      0
     6 T100       REOPEN             0 SELECT WHERE "SPRSL" = 'D' AND "ARBGB" = 'D-' AND "MSGNR" = '001'
   426 T100       FETCH       1      0
     5 T100       REOPEN             0 SELECT WHERE "SPRSL" = 'D' AND "ARBGB" = 'D-' AND "MSGNR" = '001' AND ROWNUM <= 1
   432 T100       FETCH       1      0
     5 T100       REOPEN             0 SELECT WHERE "SPRSL" = 'D' AND "ARBGB" = 'D-' AND "MSGNR" = '001'
   421 T100       FETCH       1      0
     5 T100       REOPEN             0 SELECT WHERE "SPRSL" = 'D' AND "ARBGB" = 'D-' AND "MSGNR" = '001' AND ROWNUM <= 1
   431 T100       FETCH       1      0

no visible difference in terms of calls. 2 calls for each variant. 6 index unique scans, full key specified.

the rownum is even omitted for the select single here... (if the primary key is fully specified, if not, it is added).

Kind regards,

Hermann

0 Kudos

Hi,

> However, as far as actual usage is concerned I think the Extended Program Check spat out warnings if SELECT SINGLE is used without a primary key

you are right here. For unique secondary indexes we get the warning as well.

> I believe the SELECT .. UP TO 1 ROWS has on theoretical level a longer runtime, though I'm pretty sure it's not measurable. Here's why: If you debug the statement, you'll see that it executes the SELECT, then goes to the ENDSELECT and comes back to the SELECT. So the interpreter seems to run a loop once, whereas a SELECT SINGLE is just one statement. (Ok, this is admittedly some handwaving argument...)

and i guess you might be right. In the DBI we usually distinguish between statements that select 1 record and statements that might select more. If there is a difference it is in the DBI i guess. But it should be so small that it doesn't matter. Maybe anybody want's to write a test (?)

Kind regards,

Hermann

0 Kudos

Sandra, thanks for pointing out the difference between old and new debugger and referencing the OSS note. I actually still use the old debugger frequently as I'm always struggling with the number of open sessions...

Hermann, it's always funny to me how those seemingly trivial questions turn out to be good challenges for one's understanding of how things work internally.

As far as usage is concerned I really like the idea of having a SELECT SINGLE in cases where the developer knows (on technical or functional/application level) that at most one record will match. It's nice, because by reading the code you implicitly learn something about the data model (especially if this cannot be derived by looking at the technical meta data). Unfortunately developers often get it wrong and use SELECT SINGLE, where they should have really prepared for processing multiple records. However a SELECT .. UP TO 1 ROWS would be as wrong in that case (and thus not help with my ideal interpretation for this case that the developer knew that possibly multiple records matched, but he only needed one among them).

former_member194613
Active Contributor
0 Kudos

... interesting that you don't understand ...

O.k. Harald is right, I did not add the unique secondayr indexes to the SELECT SINGLE to keep it simple and to use only the recommendation which was written above. As far as I know there not so many unique secondary indexes.

The second point was completely misunderstood.

> What you indicate here is the issue when you don't use correct keys in the WHERE, that's not related to one

> of the 2 statements.

If I follow the rule above then not the statement but the use cases are different!

A very common usage of UP TO 1 ROWS is the exstence check (never use COUNT(*) ), and there it can be that it is not worth to build an index. And it can be that no row fulfills the condition, then the UP TO 1 ROWS requires a full table scan of the whole table.

former_member194613
Active Contributor
0 Kudos

The UP TO 1 ROWS does not need a SELECT / ENDELECT also


SELECT *
       INTO  TABLE lt_dd03l
       FROM  dd03l
       UP TO 1 ROWS
       WHERE ...

The issue with the unique secondary key, I would stick to recommendation that I not take that into account, because the secondary key can be there, but does have to. I would leave it open, what is taken in that case.

If measured correctly, then the runtimes can not be distinguished. We are talking about a few hundret microseconds and

differences of a few microseconds close to measurement accuracy.

Siegfried

0 Kudos

Hi Siegfried,

> The UP TO 1 ROWS does not need a SELECT / ENDELECT also

>


> SELECT *
>        INTO  TABLE lt_dd03l
>        FROM  dd03l
>        UP TO 1 ROWS
>        WHERE ...
> 

interesting! i never thought about that so far.

conclusion:

SINGLE vs. UP TO

difference in extended syntax checks,

difference in single recor buffer access (SAP Release < 7.02)

difference in readability, coding guidlines, ...

difference with FAE (FOR ALL ENTRIES) (up to will work but emulated after selection, single won't)

NO DIFFERENCE IN PERFORMANCE

i think we could finalize it like this, can't we?

Kind regards,

Heramnn

Edited by: Hermann Gahm on Jul 9, 2010 5:02 PM

0 Kudos

Dear Friends,

Difference SELECT SINGLE:

1. It always retrieves only one record at a time

2. Here we must pass entire primary key combination in the where condition

3. This is used to retrieve a particular record.

SELECT UPTO ONE ROW:

1.It always read only one record

2.Here we pass part of key combination in the where condition it always picks the first record among the matched records.

3. This is used for validation

Regards,

K.S.Kannan

satyajit_mohapatra
Active Contributor
0 Kudos

In a training session, I was told that, we should use SELECT UPTO 1 ROWS, if we are not using the complete set of primary keys as in case of SELECT UPTO 1 ROWS, it will fetch only one row from database to buffer as well as to target field. But in SELECT SINGLE, it will transfer multiple rows to buffer, but pass only one row to target field, if all the primary keys are not used.

I think, it's right platform to verify it............ Kindly let me know, whether it's correct.

0 Kudos

Ooops,

didn't notice this one:

Posted: Jul 7, 2010 11:27 PM

It's in vain to wait for an answer from the topicstarter...

However, hope my answer is applicable to many questions of this kind.

former_member194613
Active Contributor
0 Kudos

> was told that, we should use SELECT UPTO 1 ROWS, if we are not using the complete set of primary keys

yes

> as in case of SELECT UPTO 1 ROWS, it will fetch only one row from database to buffer as well as to target field.

yes

> But in SELECT SINGLE, it will transfer multiple rows to buffer, but pass only one row to target field, if all the primary

> keys are not used.

no

matt
Active Contributor
0 Kudos

This thread now added to

Great discussion - many thanks to all participants.

Former Member
0 Kudos

Hi,

what Manabu Kuwagaito has quoted is absolutely correct if we have the full primary key then its better to use SELECT SINGLE where as in other situation where u r not having the full primary key combination then we will use SELECT UP TO 1 ROWS.

Regards,

karan lokesh.

Former Member
0 Kudos

hi,

Judging from my experience, using select single and select * up to 1 rows for all entries don't have much difference in performance.

But select * up to 1 rows **** endselect will get a worse performance.

Desmond

Former Member
0 Kudos

Alejiandro Sensejl,

I've spend the last 2 hours comparing and researching the above mentioned statements and I don't have a clear result.

Maybe you'd better spend some time for obtaining and analyzing DB plans for yor concrete query? Then you can decide what's better for this particular situation.

For example, in the same Oracle system and NW version like yours i wrote the code:

DATA: ls_ekpo TYPE ekpo.

SELECT SINGLE *
  FROM ekpo
  BYPASSING BUFFER
  INTO ls_ekpo
  WHERE ebeln = 'XXXXXXXXXX'.

SELECT *
  FROM ekpo
  UP TO 1 ROWS
  BYPASSING BUFFER
  INTO ls_ekpo
  WHERE ebeln = 'XXXXXXXXXX'.
ENDSELECT.

and then used the ST05.

Plans are the same, SAP passes to Oracle the hint FIRST_ROWS and uses rownum<=1:

SQL Statement
SELECT
/*+
  FIRST_ROWS (1)
*/
  *
FROM
  "EKPO"
WHERE
  "MANDT" = :A0 AND "EBELN" = :A1 AND ROWNUM <= :A2
Execution Plan
 SELECT STATEMENT ( Estimated Costs = 5 , Estimated #Rows = 2 )
  |
  ---   3 COUNT STOPKEY
      |   Filter Predicates:	ROWNUM<=TO_NUMBER(:A2)
      |
      ---   2 TABLE ACCESS BY INDEX ROWID EKPO
          |   ( Estim. Costs = 5 , Estim. #Rows = 2 )
          |   Estim. CPU-Costs = 46.827 Estim. IO-Costs = 5
          |
          ------1 INDEX RANGE SCAN EKPO~0
                  ( Estim. Costs = 4 , Estim. #Rows = 18 )
                  Search Columns: 2
                  Estim. CPU-Costs = 28.886 Estim. IO-Costs = 4
                  Access Predicates:	"MANDT"=:A0 AND "EBELN"=:A1

Former Member
0 Kudos

Hi,

SELECT SINGLE : select single is based on PRIMARY KEY and It will take the first record directly without searching all the records.

SELECT UPTO 1 ROW : It will check all records for given condition and take

the first record .

So SELECT SINGLE seems more efficient.

Regards,

Priyanka Kaushik.

former_member194613
Active Contributor

Please lock that thread, before the discussion starts again

>SELECT SINGLE : select single is based on PRIMARY KEY and It will take the first record directly without searching all the records.

what is the first record dircetly

>SELECT UPTO 1 ROW : It will check all records for given condition and take the first record .

often repeated nonsense!

they are identical identical identical!!! because the same statement goes to the database, they are onyl different in ABAP (or Open SQL)!!

0 Kudos

Enough.

Rob

0 Kudos
  • If a SINGLE will be only used when we have all primary key
  • And up to 1 rows when we have don't have all primary key.
  • There will be no major difference in terms of performance but there is a chance that data may be inconsistent when we use up to 1 rows.
  • If all primary key is not passed, there are the chances of having multiple records with a given condition. So, here up to 1 rows supports sorting to get our required record.
  • If UP TO 1 ROWS is used, the addition ORDER BY can be specified to determine the first row in a multirow set of hits.

0 Kudos
  • If a SINGLE will be only used when we have all primary key
  • And up to 1 rows when we have don't have all primary key.
  • There will be no major difference in terms of performance but there is a chance that data may be inconsistent when we use up to 1 rows.
  • If all primary key is not passed, there are the chances of having multiple records with a given condition. So, here up to 1 rows supports sorting to get our required record.
  • If UP TO 1 ROWS is used, the addition ORDER BY can be specified to determine the first row in a multirow set of hits.

gasparerdelyi
Active Participant
0 Kudos

There is a difference not mentioned yet, however only up to Netweaver 7.0 EhP1, where SELECT UP TO 1 is bypassing single record buffering even if your WHERE condition has the whole primary key.
In those old releases, single record buffering only works if you use the SELECT SINGLE syntax.