Diff between SELECT SINGLE & SELECT UPTO 1 ROW

[Former Member]

Dear All,

What is better to use from performance point, when we don't have full key - SELECT SINGLE or SELECT UPTO 1 ROW?

Thanks in advance.

Prasad

[Former Member]

Hi!

SELECT SINGLE has better performance, because it is not starting to read the whole table (of course restricted by the WHERE), only 1 line.

The other statement is reading the whole table first, but after the 1st record it is stopping the processing. It is only better if you need more than 1 lines, maybe you are interested in the first 100, then you can use the UPTO 100 entries.

Regards

Tamá

[former_member194613]

There is no performance difference.

Select Single can be used with full key

Up to x rows with any WHERE condition.

Siegfried

[Rodrigo-Giner]

Here is your answer:

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/840ad679-0601-0010-cd8e-9989fd65...

SELECT SINGLE is faster (we r talking of NANOSECONDS) -_-

[Former Member]

Prasad,

If you have the full key then always use select single. Select upto 1 rows first get all the relevant records from the database & then send the first one to the application server where select single pick up the first matched record.

-Ashim

[Former Member]

select sinle will work well.

[Former Member]

select single will work well

[former_member194613]

Lots of comments, but actually the things are not so easy:

When I measure it myself, then the UP TO 1 ROWS is a bit slower, but only

when executed first. When it is repeated after the SELECT SINGLE then it is most

times similar to the SELECT SINGLE

1. SELECT  UP TO 1 ROWS  1. field    Time:        614
2. SELECT  SINGLE        1. field    Time:        158
1. SELECT  UP TO 1 ROWS  1. field    Time:        148

see also SQL trace:

Duration Program Obj. Name Op.

      345 ZSB_TES  DD08T      OPEN
       61 ZSB_TES  DD08T      FETCH
       24 ZSB_TES  DD08T      CLOSE

       92 ZSB_TES  DD08T      OPEN
       11 ZSB_TES  DD08T      FETCH
        6 ZSB_TES  DD08T      CLOSE

       88 ZSB_TES  DD08T      OPEN
       12 ZSB_TES  DD08T      FETCH
        6 ZSB_TES  DD08T      CLOSE

Feel free to do your own tests:

*& Report  ZSB_TEST_SGL
REPORT  zsb_test_count_sdn.

DATA:
  start        TYPE i,
  stop         TYPE i,
  t1           TYPE i,
  t2           TYPE i,
  t3          TYPE i,
  lv_tabname   LIKE dd08t-tabname.

* SELECT 1. FIELD (index only possible)---------------------
  GET RUN TIME FIELD start.
  SELECT tabname
         INTO  lv_tabname
         FROM  dd08t
         UP TO 1 ROWS.
  ENDSELECT.
  GET RUN  TIME FIELD stop.
  t1 = stop - start.

* SELECT SINGLE 1. FIELD (index only possible)-----------------
  GET RUN TIME FIELD start.
  SELECT SINGLE tabname
         INTO  lv_tabname
         FROM  dd08t.
*  ENDSELECT.
  GET RUN  TIME FIELD stop.
  t2 = stop - start.

* SELECT 1. FIELD (index only possible)---------------------
  GET RUN TIME FIELD start.
  SELECT tabname
         INTO  lv_tabname
         FROM  dd08t
         UP TO 1 ROWS.
  ENDSELECT.
  GET RUN  TIME FIELD stop.
  t3 = stop - start.

WRITE:
 / '1. SELECT  UP TO 1 ROWS  1. field  ',' Time:',t1,
 / '2. SELECT  SINGLE        1. field  ',' Time:',t2,
 / '1. SELECT  UP TO 1 ROWS  1. field  ',' Time:',t3.

Siegfried

[Former Member]

Hi Siegfried,

Just executed your program with each query executed once more. This is what I got:

1. SELECT UP TO 1 ROWS 1. field Time: 800

2. SELECT SINGLE 1. field Time: 368

2. SELECT UP TO 1 ROWS 1. field Time: 317

3. SELECT SINGLE 1. field Time: 297

3. SELECT UP TO 1 ROWS 1. field Time: 301

Regards,

Munish

[Former Member]

Executed same program once more: this time select single being the first query:

2. SELECT SINGLE 1. field Time: 1,223

2. SELECT UP TO 1 ROWS 1. field Time: 424

3. SELECT SINGLE 1. field Time: 316

3. SELECT UP TO 1 ROWS 1. field Time: 310

4. SELECT SINGLE 1. field Time: 293

4. SELECT UP TO 1 ROWS 1. field Time: 297

[Former Member]

Hello,

Try this on a non-buffered table !

SELECT SINGLE is better since it has only 1 database hit. This can be used in case you have KEY FIELDS in WHERE clause.

SELECT UPTO 1 ROW can be used to determine if there is any value existing if you do not have KEY FIELDS in WHERE Clause.

SELECT UPTO 1 ROW uses 2 database hits so is having worse performance than SELECT SINGLE if you use key fields.

Regards

Nishant.

[Former Member]

The two will never conflict. If you need just one record from a database table and are accessing the table by mentioning the complete primary key you would use SELECT SINGLE.

<b>COMPLETE PRIMARY KEY</b>

For example if I wanted to retrieve one line of a Sales Order and I know the Sales Order Number VBAP-VBELN and the item number VBAP-POSNR that I want, I would use the SELECT SINGLE statement (Note: The primary key fields of table VBAP are VBELN and POSNR).

SELECT SINGLE <field list>

FROM vbap

INTO <work area>

WHERE vbeln EQ <order number>

AND posnr EQ <line item number>.

<b>PARTIAL PRIMARY KEY OR WITHOUT USE OF PRIMARY KEY</b>

However if I have only a Sales Order number and just want to pick any one of the line items numbers of the sales order, I would use the SELECT UP TO 1 ROWS option.

SELECT <field list>

FROM vbap

INTO <work area>

WHERE vbeln EQ <order number>.

ENDSELECT.

The general rule is

a) Do not use SELECT SINGLE if you are not using the complete primary key. Use SELECT UP TO 1 ROWS.

b) If you are using the complete primary key it is preferable to use SELECT SINGLE.

From a performance point of view a SELECT SINGLE statement should be more efficient, however we would be splitting hairs here because in most cases especially when you are using part of the primary key the difference is negligible.

[former_member194613]

@Nishant,

I don't think you are right, check the SQL trace, there is no difference!

And the DD08T is not buffered!

Siegfried

[Former Member]

Hi Siegfried

Every next time you execute the same select query, it will take from the buffer, as long as you are in the same session. So the time of execution will degrade on the 2nd run.

But performance wise, SELECT SINGLE is always better that SELECT UP TO 1 ROWS.

SELECT UPTO 1 ROWS performs in a Loop, as ENDSELECT is given in this case.

Thanks

Ipsita

[Former Member]

Hi

<b>Knowing when to use SELECT SINGLE or SELECT ... UP TO 1 ROWS</b>

A lot of people use the SELECT SINGLE statement to check for the existence of a value in a database. Other people prefer to use the 'UP TO 1 ROWS' variant of the SELECT statement.

So what's the difference between using 'SELECT SINGLE' statement as against a 'SELECT .... UP TO 1 ROWS' statement ?

If you're considering the statements

SELECT SINGLE field INTO w_field FROM table.

and

SELECT field INTO w_field FROM table UP TO 1 ROWS. ENDSELECT.

then looking at the result, not much apart from the extra ENDSELECT statement. Look at the run time and memory usage and they may be worlds apart.

Why is this ?? The answer is simple.

The 'SELECT SINGLE' statement selects the first row in the database that it finds that fulfils the 'WHERE' clause If this results in multiple records then only the first one will be returned and therefore may not be unique.

The 'SELECT .... UP TO 1 ROWS' statement is subtly different. The database selects all of the relevant records that are defined by the WHERE clause, applies any aggregate, ordering or grouping functions to them and then returns the first record of the result set.

Get the difference ??

If not, here is a good example, credit for this example goes to Richard Harper, a friend of mine on sapfans.com :

Create a Ztable called ZDifference with 2 fields in it, MANDT of type MANDT and POSNR of type POSNR. Make sure both of these are keys. Also create a table maintenance dialog for it (SE11->Utilities->Table Maintenance Generator). Fill the table with ten rows 000001-000010.

Then run the program shown below:

Code:

• Program: Z_Difference

• Purpose: A program that demonstrates the difference

• between SELECT SINGLE and SELECT UP TO n ROWS.

• This program requires the data table Z_DIFFERENCE

• to have been created according to the structure

• outlined in the text above and populated with

• at least 10 records.

• Creation Date: 21/04/2004

• Requested By:

• Reference Doc:

• Author: R Harper

• Modification History:

• Date Reason Transport Who

Report Z_Difference

Message-id 38

Line-Size 80

Line-Count 0

No Standard Page Heading.

Start-Of-Selection.

Data: w_Single type Posnr,

t_Rows type standard table of Posnr

initial size 0

with header line.

Select single Posnr

from zDifference

into w_Single.

Select Posnr

into table t_Rows

from zDifference

up to 1 rows

order by Posnr descending.

Write 😕 'Select single:', w_Single.

Skip 1.

Write 😕 'Up to 1 rows :'.

Loop at t_Rows.

Write t_Rows.

EndLoop.

You should see the output:

Select single: 000001

Up to 1 rows : 000010

The first 'SELECT' statement selected the first record in the database according to any selection criterion in the 'WHERE' clause. This is what a 'SELECT SINGLE' does. The second 'SELECT' has asked the database to reverse the order of the records before returning the first row of the result.

In order to be able to do this the database has read the entire table, sort it and then return the first record. If there was no ORDER BY clause then the results would have been identical (ie both '000001') but the second select if given a big enough table to look at would be far slower.

Note that this causes a problem in the Extended Program Check if the full key is not specified in a 'SELECT SINGLE'. Replacing the 'SELECT SINGLE' by an "UP TO 1 ROWS" will give the same exact results without any warning but the program will run slower and consume more memory. This is a good example of a warning that we should ignore... considering you are sure of what you are doing !!

<b>Working around the limitations of a range in SELECT...WHERE...IN</b>

When you use a range table in a select (SELECT * FROM sflight WHERE carrid IN lt_carrid), you sometimes get a short dump with the error DBIF_RSQL_INVALID_RSQL.

A lot of people think that a maximum number of records was reached; rumour has it that it's somewhere between 1000 and 2000 records. This is false and groundless. In fact, the problem is that the "IN" keyword is not native SQL and the compiler converts it into native SQL. There is a limitation on the length of the generated SQL string.

Here is a small example of a program to create a short dump :

DATA : lt_range TYPE RANGE OF sflight-carrid WITH HEADER LINE,

lt_sflight TYPE TABLE OF sflight.

DO 5000 TIMES.

lt_range-sign = 'I'.

lt_range-option = 'EQ'.

lt_range-low = 'AA'.

APPEND lt_range.

ENDDO.

SELECT * FROM sflight INTO TABLE lt_sflight WHERE carrid IN lt_range.

The compiler convert the SELECT in native SQL like that :

... WHERE carrid = lt_range[1]-low OR

lt_range[2]-low OR

lt_range[3]-low OR

....

lt_range[5000]-low.

The short dump occur when the generated SQL caracter string is over a certain threshold. This threshold is variable, could be between 2k and 32k (usually 4k or 8k) depending on the DB system. The threshold is stored in dbs/io_buf_size and could NOT be change by programmers because this is maintained on DB level (oracle, DB2, etc...) by IT.

There are two ways to avoid this problem :

Create some small packages (500 records) and call the SELECT within a loop with APPENDING TABLE keyword

Usually the best way to do it is using a SELECT... FOR ALL ENTRIES IN... instead.

<b>Calling function modules dynamically</b>

When you only know the function name to call at run-time, you have to call your function dynamically. Here is an example of how to call a function dynamically (note: comments were added to an existing example from the standard help)

REPORT ZDANY_DYN_FM_CALL.

*The constants and structures required to use the dynamic function call

*is stored in the type pool ABAP.

type-pools abap.

*This is the name of the function you want to call.

data NAME type STRING value `READ_SPFLI_INTO_TABLE`.

• Parameter table, where you store all parameters, importing, exporting

• and changing data PARA_TAB type ABAP_FUNC_PARMBIND_TAB. data PARA_LINE like line of PARA_TAB.

• Exception table to handle the exception that can occur during the

• execution of the function

data EXCP_TAB type ABAP_FUNC_EXCPBIND_TAB.

data EXCP_LINE like line of EXCP_TAB.

data CARRIER type SPFLI-CARRID.

data JTAB type SPFLI_TAB.

CARRIER = 'XYZ'.

• Name of the first parameter

PARA_LINE-NAME = 'ID'.

• type of the first parameter, could be :

• abap_func_exporting value 10,

• abap_func_importing value 20,

• abap_func_tables value 30,

• abap_func_changing value 40.

PARA_LINE-KIND = ABAP_FUNC_EXPORTING.

*We need the datatype of the parameter to pass

get reference of CARRIER into PARA_LINE-VALUE.

append PARA_LINE to PARA_TAB.

*Same thing for parameter 2

PARA_LINE-NAME = 'ITAB'.

PARA_LINE-KIND = ABAP_FUNC_IMPORTING.

get reference of JTAB into PARA_LINE-VALUE.

append PARA_LINE to PARA_TAB.

*Now we create the possible exceptions

EXCP_LINE-NAME = 'NOT_FOUND'.

EXCP_LINE-VALUE = 1.

insert EXCP_LINE into table EXCP_TAB.

EXCP_LINE-NAME = 'OTHERS'.

EXCP_LINE-VALUE = 4.

insert EXCP_LINE into table EXCP_TAB.

*... and we dynamically call the function with the parameter-table and

• exception-table addition

call function NAME

parameter-table

PARA_TAB

exception-table

EXCP_TAB.

• We check the result code

case SY-SUBRC.

when 1.

message id SY-MSGID type SY-MSGTY number SY-MSGNO.

when 2.

message E888(SABAPDOCU) with 'Error in function module'.

endcase.

Back to top

Downloading ABAP code to your local PC

Creating Z* test programs is a popular method for conducting tests. However, when an environment is about to get shut down, keeping your test programs becomes tricky. You have to launch se38/37/24/80, select your programs one by one, and choose system -> list -> save -> local file, or use function module WS_DOWNLOAD. This is a lengthy procedure that does not even enable you to save screens, tables, or structures.

An ABAP program available for free download at http://www.dalestech.com/ can help you save your test programs.

Choose home -> R/3 entreprise -> direct download entreprise 1.2

download the .ZIP file,

Unzip

Cut and paste in an ABAP program

Compile and run

All your projects (including multiple classes, programs, function groups, screens and tables) can be saved in less than one minute.

If you want to backup your work in order to keep a certain stable version while you are programming, it might be preferable use a tool that is already embedded in the Workbench: in se38/37/24/80, choose utilities -> versions -> generate version

[Former Member]

This message was moderated.

[former_member194613]

@Guest

Your answer is not readable and not correct:

>The database selects all of the relevant records that are defined by the WHERE clause, applies any aggregate, ordering or

> grouping functions to them and then returns the first record of the result set. Get the difference ??

This is not generally true, it depends on the database whether the UP TO n ROWS or the aggregate is processed first.

I did not read the rest.

[ravi_lanjewar]

Hi Prasad,

I am agree with Siegfried Boes. There is not difference between Select Single & Select upto 1 row on performance point of view.

Select upto 1 row having one advantage i.e. We can used the aggrigate function with it, But not with Select Single.