select single & select upto n row .

[Former Member]

Hi,

Can anyone tell me the actual difference between select single & select upto n rows . which has the good performance .

Can anyone send a sample code for that.

[Former Member]

Hi,

Can anyone tell me the actual difference between select single & select upto n rows . which has the good performance .

Can anyone send a sample code for that.

[Former Member]

Hi

Difference Between Select Single and Select UpTo One Rows

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.

<b>Reward points for useful Answers</b>

Regards

Anji

[Former Member]

Hi,

this question is answered so many times in the forum

any how as far as my knowledge ...

select single will be used

when you have all the primary key values in your where used list

and select -- upto n rows

will be used when you are restricting the entries to some values

eg for select up to 1 row

select * from tcurr into table itab up to 1 rows

where FCURR = 'INR'.

here you dont have all the values of primary keys

this will fetch you the latest exchange rate maintained in this table

if you want to know the exchange rate on a particular date

select single * from tcurr into table itab where

give all primary key values.

reward points if helpful

thanks & regards,

venkatesh

[Former Member]

Hi

Further to add on to the point of Venkatesh the select single will be obviously faster and efficient than the select upto 1 row event though some times both of them return the same record. But in that case I would suggest that you use the select single. If it is a case that you want to read the read the first record always from a list of n records, use the select upto 1 rows.

Hope it helps.

- Irudayaraj Peter

[Former Member]

SELECT SINGLE...this gets the entire data based on the WHERE condition into the buffer and then returns only one record from the buffer.

SELECT UP TO 1 ROW gets only one row of data into the buffer....thus performance wise it is better over select single.

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

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 and applies any aggregate, ordering or grouping functions to them and then returns the first record of the result set

[former_member189059]

Refer this link

/thread/512224 [original link is broken]

[Former Member]

SELECT SINGLE...this gets the entire data based on the WHERE condition into the buffer and then returns only one record from the buffer.

If you have all the key fields value in where clause then only SELECT SINGLE is better than SELECT UPTO 1 ROWS.

SELECT UP TO 1 ROW gets only one row of data into the buffer....thus performance wise it is better over select single, if you don't have all the key fields to use in where clause.

SELECT UPTO 1 ROWS is a loop operation. So when ever you have all the key fields value then use SELECT SINGLE, it is better performance wise.

If it is help reward points.

with regards,

Srinivas.

[rainer_hbenthal]

A lot of wrong ideas here to read....

First of all select single does not follow the primary key, id does follow a unique index. A primary key has always a unique index, and if you know exactly what row you want to read from your database and the full index is given the optimizer can extract this record using a tree search in the index which is the fastest selection you can get.

If for some reason one or more keys in the where condition are missing, but the given keys still points to a unique row (might be that some fields are never used in the index) you will get a full index scan, which is slower then the above one, but still faster then the above.

The sentence up to n rows is just a hint for the optimizer that you will only use the first n rows delvered. This might result in a better access path. At least, you can mess up in getting a full tablespace scan which is the most expensive search in a database.

select single will never lead to full tablespace scan exceptional for one reason: the table is rather small and the optimizer comes to the opinion that seraching the index will not be faster then searching the whole table.

So, if you know all keys of a unique index use silect single, if you only want one row and you know only parts of the still use select single.

If you intend to show the user more then one row, but not all rows give the optimizer a hint with up to n rows. Using this with n = 1 is silly and will lead to a more worse performance. Up to n rows will allways transfer all selected rows to the application server, it will not limit the amount. Its just a hint for the db-optimizer.