Up to 1 rows

Former Member · ‎2006 Jun 23

Hi experts,

What is the difference between select single * ..

and select up to 1 rows?

Former Member · ‎2006 Jun 23

Hi ravi,

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.

regards,

keerthi

RichHeilman · ‎2006 Jun 23

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

Regards,

Rich Heilman

former_member927251 · ‎2006 Jun 23

Hi Ravi,

Refer the following link with a good example for both

the cases:

http://www.sapsuperusers.com/forums/showthread.php?t=4867

<b>Reward points if it helps.</b>

Former Member · ‎2006 Jun 23

HI,

When you say SELECT SINGLE, it means that you are expecting only one row to be present in the database for the condition you're going to specify in the WHERE clause. so that means, you will have to specify the primary key in your WHERE clause. Otherwise you get a warning.

SELECT UP TO 1 ROWS is used in cases where you just want to make sure that there is at least one entry in the database table which satisfies your WHERE clause. Generally, it is meant to be used for existence-check.

You may not want to really use the values returned by the SELECT statement in this case (thought this may not necessarily be so).And in each case the database optimizer may choose a different strategy to retrieve the data.

Knowing when to use SELECT SINGLE or SELECT ... UP TO 1 ROWS

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.

Also have a look at:

regards,

Laxmi

former_member181962 · ‎2006 Jun 23

Hi ravi,

1) Select single expects the full key to be passed in the where clause.(Give error if the whole key is not passed in EPC checks).Select up to 1 rows doesn't have such restriction.

2) Select single doesn't have an endselect.

Regards,

ravi

Former Member · ‎2006 Jun 23

Is the where clause is must in select up to 1 rows?

RichHeilman · ‎2006 Jun 23

No. It is not required.



report zrich_0001.

tables: mara.

select * from mara up to 1 rows.

  write:/ mara.
endselect.

Regards,

Rich Heilman

former_member181962 · ‎2006 Jun 23

IN select up to one rows, where clause is not mandatory.

Regards,

ravi

Former Member · ‎2006 Jun 23

Hi All ,

Thanks .Points are given.

As i know select up to 1 rows means It will search entire database and retreives the first row of DB table.

Similarly up to 10 rows means it retrieves first 10 rows?

I am not sure..please correct me if i am wrong.

Former Member · ‎2006 Jun 23

hi Check this out

Regards,

Santosh

former_member181962 · ‎2006 Jun 23

Hi ravi,

By default the records inserted into the databse will be sorted by the key fields.

Hence select up to 10 records would give you the first 10 records(Sorted in ascending order by the key fields).

Regards,

ravi

Former Member · ‎2006 Jun 23

Hi all,

Thanks for the info.

Former Member · ‎2006 Jun 23

Hi Ravi,

Please close the thread by marking helpful answers.

Regards,

Santosh

Former Member · ‎2006 Jun 23

hi

Check out the link...

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

According to SAP Performance course the SELECT UP TO 1 ROWS is faster ( as it does not require where condition )than SELECT SINGLE because you are not using all the primary key fields (here you need to mention the where condition).

Regards,

Richa