Select

[Former Member]

Hi

Can anyone tell me when to use SELECT SINGLE and SELECT up to 1 rows...

Dont' they mean the same?

Thanks in advance

Preeti

[Former Member]

Hi

You use SELECT SINGLE when you are passing all the key fields in the where condition. This fetches the unique record from the db table.

If you do not pass all the required fields, it will not give any error but will give a warning the extended programming check.

You use SELECT up to 1 rows when you wish to select any one record satisfying the where condition, but not a record in particular.

hope this helps.

Regards,

Navneet

[Former Member]

Hi,

The main difference between SELECT SINGLE and ..UP TO n ROWS is this:

Normally a SELECT statement looks like this

SELECT x FROM y INTO z.

 "handle Z here..

END SELECT.

In fact its a loop.

When you use SELECT SINGLE

the following syntax is used

SELECT SINGLE x FROM y INTO z.

So, no loop structure.

Also:

SELECT SINGLE allows for special DB buffering features (use buffered data, or bypass DB buffer..)

UP TO n ROWS allows for retreival of any fixed number of records (not just one)

SELECT SINGLE is less expensive (less stress on DB) if used well.

Hope this helps,

Regards,

Joris Bots

[Former Member]

Hi,

Check this..

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

Thanks,

Naren

[former_member186741]

I think they are virtually the same. The select a single does give you a warning message if you don't specify the full key but it does NOT give an error. I think the history of it is that 'up to n rows' was introduced in a later release of abap whereas select single was in from the beginning. I don't believe one will have performance benefits over the other if the same key fields are specified(or omitted).

So basically use whichever you feel like if you want to get one record from a table. Both of them return the <b>first record</b> which satisfies the criteria <b>neither option continues searching to the end of the database</b>.

<b>Check it out in SE30.</b> Note both selects specify the same amount of key information and they have similar performance when keys are fully specified or partially specified. <b> There is negligible difference</b>.

Use SE30 'Tips and tricks' to do a comparison. I used these two and found the performance comparable. Sometimes one was slightly bigger than the other but they were always of the same order.:

1. fully specified keys

SELECT single * FROM SBOOK INTO SBOOK_WA

WHERE CARRID = 'LH' AND

CONNID = '0400'.

versus:

SELECT * FROM SBOOK up to 1 rows INTO SBOOK_WA

WHERE CARRID = 'LH' AND

CONNID = '0400' .

ENDSELECT.

2. partially specified keys

SELECT single * FROM SBOOK INTO SBOOK_WA

WHERE CARRID = 'LH'.

versus:

SELECT * FROM SBOOK up to 1 rows INTO SBOOK_WA

WHERE CARRID = 'LH'.

ENDSELECT.

[Former Member]

hi Preethi,

Check out this thread ,...

Regards,

Santosh

[Former Member]

SELECT SINGLE is used when ever u want to get a single record when the key fields are given to use them in the where condition

This is mostly used

SELECT Up TO 1 ROWS is used to do validation , to check if a record exists in the table

[shishupalreddy]

Hi ,

select single with all primary keys in the selection condition gets a unique record and thenumber of hits will be atleast one .

select up to one row will hit the databsae once and gets the firslt record frm the database .

regards,

[Former Member]

Though somethimes the output of these two statments is same, they search database table in different manner.

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.

Best Regards,

Vibha Deshmukh

*Plz mark useful answers

[Former Member]

Hi everybody,

Both differs a lot in case of performance.

If you have all the key fields then you can use SELECT SINGLE. Because there is no chance of duplicate records; as soon as it finds a record it DB sends the record. If you don't give full key it keeps on searching till the end of the table. So it's a burden for DB.

If you don't have full key, you can go for SELECT UP TO 1 ROWS. As soon as it finds a matching record no matter of duplicate it just takes the record and stops searching. But as this technique is a looping the burden will be more on Application Server and network.

<b>So it is always preferable to use SELECT SINGLE with full key; if you don't have full key use UP TO 1 ROWS.</b>

Regards

Surya.

[Former Member]

Hi,

The SELECT clause is a loop, so is always used with ENDSELECT. Its structure is like this:

SELECT * FROM mara.

..........

ENDSELECT.

The SELECT SINGLE clause is not a loop. This clause searches for the records in the relation. As soon as it encounters the first matching record, further searching is stopped.

For both SELECT....ENDSELECT and SELECT SINGLE, the value of SY-SUBRC is set to zero else it is non-zero(typically 4).

The SELECT SINGLE statement is typically used, whenever you want to validate a value entered by the end-user in the selection-screen through parameters/select-options.

I guess that would answer your question.

Regards,

Saurabh A. Buksh