difference between select single * & select upto 1 rows

[Former Member]

difference between select single * & select upto 1 rows

[Former Member]

Hi,

Actually in Select single * only one record is fetched

which ever come across first in data base query , but for

statment select ..upto 1 row , all records are fetched into

a buffer which are satisfied from Where condition in Data

base query , and from buffer first record is fetched .

So Select Single * is better for performance.

rgds,

bharat.

[Former Member]

difference between select single and select up to 1 row

http://www.sap-img.com/abap/difference-between-select-single-and-select-upto-one-rows.htm

[Former Member]

According to SAP Performance course the SELECT UP TO 1 ROWS is faster than SELECT SINGLE because you are not using all the primary key fields.

select single is a construct designed to read database records with primary key. In the absence of the primary key, it might end up doing a sequential search, whereas the select up to 1 rows may assume that there is no primary key supplied and will try to find most suitable index.

The best way to find out is through sql trace or runtime analysis.

Use "select up to 1 rows" only if you are sure that all the records returned will have the same value for the field(s) you are interested in. If not, you will be reading only the first record which matches the criteria, but may be the second or the third record has the value you are looking for.

The System test result showed that the variant Single * takes less time than Up to 1 rows as there is an additional level for COUNT STOP KEY for SELECT ENDSELECT UP TO 1 ROWS.

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.

Mainly: to read data from

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.

Regards

Giridhar

[Former Member]

Hi Gridhar

Select single * is anyday any time better and faster than select up to 1 row

You can check in SE30 but do go for the clause bypassing buffer because if you will go for the same data and same table it will first buffer that so check that with "BYPASSING BUFFER"

you will get the result

here is the sample for you:

===========================

TYPES: t_ekko type ekko,

t_ekpo type ekpo.

data: wa_ekko type t_ekko,

wa_ekpo type t_ekpo,

it_ekko type standard table of t_ekko,

it_ekpo type standard table of t_ekpo.

select single *

from ekpo bypassing buffer into wa_ekpo

where ebeln = '4500000001'.

In this case -- time: 1,463 microseconds

=========================

TYPES: t_ekko type ekko,

t_ekpo type ekpo.

data: wa_ekko type t_ekko,

wa_ekpo type t_ekpo,

it_ekko type standard table of t_ekko,

it_ekpo type standard table of t_ekpo.

select *

from ekpo up to 1 rows bypassing buffer into table IT_ekpo

where ebeln = '4500000001'.

In this case Runtime: 1,479 microseconds

============================

You can check this in se30--tips and tricks and copy paste the codes in vertically tiled section of coding area and click on measure runtime

also you can make a program of both and then analyze them

in select upto 1 row-- DATABASE time is -- 11.6%

in select single -- DATABASE time is - 5 %

so select single is faster than that becuase it doesnot fetch all the records only first record it finds it gives back result with that

Please dont copy paste the content from other websites use your own thinking .

Thanks and Regards

Shekhar

[Former Member]

Hi,

According to SAP Performance course the SELECT UP TO 1 ROWS is faster than SELECT SINGLE because you are not using all the primary key fields.

Select single is a construct designed to read database records with primary key. In the absence of the primary key, it might end up doing a sequential search, whereas the select up to 1 rows may assume that there is no primary key supplied and will try to find most suitable index.

The best way to find out is through sql trace or runtime analysis.

Use "select up to 1 rows" only if you are sure that all the records returned will have the same value for the field(s) you are interested in. If not, you will be reading only the first record which matches the criteria, but may be the second or the third record has the value you are looking for.

The System test result showed that the variant Single * takes less time than Up to 1 rows as there is an additional level for COUNT STOP KEY for SELECT ENDSELECT UP TO 1 ROWS.

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.

Mainly: to read data from

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.

Mainly: to check if entries exist.

Select Single

You need to mention all the key fields of the table.

No END SELECT required.

More performance compared to upto 1 row.

Where as UP to 1 row.

YOu can use if you do not have all the primiary key fields available.

END SELECT requeired.

Since all keys are not passing, possiblities of have other rows which satisfies the condition.

Select Statement with EndSelect is a loop, which in a single run retrieves a single Record. This Record has to be stored in a Work Area and then appended into an Internal Table.

Select Statements without EndSelect is not a loop and it retrieves the whole Record set matching the Criteria in a single shot and has to be Stored in an Internal Table Directly.

The most important thing to remember about the SELECT SINGLE is

There are several things to remember:

1) It retrieves only one row

2) It does not need an ENDSELECT statement

3) THE FULL KEY OF THE TABLE MUST BE INCLUDED IN

THE WHERE CLAUSE OF THE SELECT STATEMENT

Regards

[Former Member]

Hi Praveena

Select * means it will select all fields from corresponding table.

Select Upto 1 row means it will select only one row from the table.

Thanks

Pari

[Former Member]

Hi Praveena,

SELECT SINGLE should be used only if u r giving all the key fields in the query

SELECT up to 1 rows can be used even if u r not giving key fields

Select upto 1 rows is used as loop like

select up to 1 row from MARA where....

.....

endloop.

Select single is used like

Select single from mara where matnr = ......

Even though we are supposed to give key fieds for select single it will work even if key fields are not given

In select single single since we are supposed to give key fields in where condtions

it wont check for indexes.

Thanks Arjun

[Former Member]

Hi

According to SAP Performance course the SELECT UP TO 1 ROWS is faster than SELECT SINGLE because you are not using all the primary key fields.

select single is a construct designed to read database records with primary key. In the absence of the primary key, it might end up doing a sequential search, whereas the select up to 1 rows may assume that there is no primary key supplied and will try to find most suitable index.

The best way to find out is through sql trace or runtime analysis.

Use "select up to 1 rows" only if you are sure that all the records returned will have the same value for the field(s) you are interested in. If not, you will be reading only the first record which matches the criteria, but may be the second or the third record has the value you are looking for.

The System test result showed that the variant Single * takes less time than Up to 1 rows as there is an additional level for COUNT STOP KEY for SELECT ENDSELECT UP TO 1 ROWS.

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.

Mainly: to read data from

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.

[former_member200338]

Hi,

select single * will fetch the first matching value into a work area.

select upto fill fetch all the values and fill the internal table with first matching record.

Note: single * will use work area.

upto 1 rows will use internal table.

Edited by: Niyaz Ahamed on Mar 19, 2008 10:37 AM