What is difference between Select Single and Select UPTO 1 rows?

[Former Member]

I had search on many sites but got difference answers as Select Single is faster than upto 4 rows or

upto one rows is fatser than select single...

one of the answer i have got is like

If you Google, you will see lots of results that will say SELECT SINGLE is faster and efficient than SELECT UPTO 1 ROWS.
But that is 100% incorrect.

SELECT UPTO 1 ROWS is faster than SELECT SINGLE.
If for a WHERE condition, only one record is present in DB, then both are more or less same.
However, If for a WHERE condition multiple records are present in DB, SELECT UPTO 1 ROWS will perform better than SELECT SINGLE.

and on this link opposite is Given to this

http://wiki.sdn.sap.com/wiki/display/HOME/Difference+between+select+single+and+select+upto.

(mention line: he System test result showed that the variant Single * takes less time than Up to 1 rows)

Can any one please tell with simple and presise language...

Thanks...

[former_member282823]

Hi,

suppose in a table you have 10 records.

Select single * --> using this will fetch you the exact record,the catch is you need to use all the primary key in the where condition.

select upto 1 row -> it will fetch you the first record. suppose in the select query is not based on the primary key, then it may fetch more than one record and in that it will fetch the first record which is there.

Regards,

Ramesh.

[uppu_narayan]

Hi nikita,

In order to check for the existence of a record then it is better to use SELECT SINGLE than using SELECT ..UP TO 1 ROWS since it uses low memory and has better performance.

The only difference is the ABAP syntax prevents from using ORDER BY with SELECT SINGLE, but it is allowed with SELECT .. UP TO 1 ROWS. Thus, if several records may be returned and we want to get the highest record for example, SELECT SINGLE cannot be used, but SELECT …UP TO 1 ROWS WHERE … ORDER BY .. may be used.

thanks and regards,

narayan

[Former Member]

Hi Nikitha,

Select single is the fastest select statement to get the data from the database table but here the manadatory thing is you should use the complete primary key.

If we are not having the complete primary key if u r using select single it will not fetch the exact record which you want.

If we are not having the complete primary key then we will go for select upto 1 rows and one more important thing is dont use more than 1 rows when u r going upto n rows statement it will be performance issue.

so atlast, if you are having complete primary key use select single else go for select upto 1 rows.

I hope this clears your doubt.

Thanks & Regards.

Pavan Neerukonda.

[kabil_g]

Hi nikita ,

               Select single will fetch the particular Ex: sales order ; 1000006

                select upto 1 row will fetch the original sales order no:1000006

select single is for more dynamic and for performance issu we use that.

select upto rows its performance is little bit later....for performing the actions

[Former Member]

Hi

While using Select Single it is necessary to specify all the key fields in the table. Whereas, using Select upto 1 rows, then its not mandatory to specify all the key fields.

[Former Member]

Hi,

Check the below link .

http://scn.sap.com/message/5019598#5019598

or

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.

diff between select single * or upto one row

select single vs select upto one row

difference between select single and select upto 1

Regards

Mahesh

[Former Member]

Hi,

I hope that this is a simple explanation:

select ... up to n ROWS is a ways that could read a fixed number of data records.

Only the requested number of data records are trasferred from the DB to the application.

select single * ...allows you to read a single record from a DB table.

for this scope, all the key fields must be filled by WHERE condition.

Regards

Ivan

[Former Member]

Hi Nikita

For simple understanding, select upto 1 row will fetch the first record from the database table irrespective of evrything. just will fetch the very first record.

In select single, it will fetch also only one record but based on certain condition of primary key etc, hence statement will execute till it satisfies the condition to display the first record found.

Moreover, lets say a database table has 100000 record . select single conditions might be satissfies for the last record i.e. record number 100000 which is not in the case of select upto 1 row.

regards

Vaibhav

[adam_krawczyk1]

Hi Nikita,

There are many posts about this in SCN, however it is not easy to get final correct information as many copy paste same examples in different posts.

I recommend you to go through this discussion:

http://scn.sap.com/thread/1730155

In general there is rather no performance difference between SELECT SINGLE and SELECT UP TO 1 ROW, for sure you will not notice it. Two important functional differences:

• SELECT SINGLE has simpler syntax.
• SELECT UP TO offers more additional options like grouping, sorting etc.

I read opinions that SELECT UP TO in addition prepares full query result in database buffer, but still returns only single row result. Although theoretically this means it would work slower, I have not noticed it in my test when searching for 116 572 duplicates have similar results for SELECT UP TO and SELECT SINGLE so I doubt if all these rows are cached. But this may depend on database implementation as well.

To clarify what some people wrote, it is not true that you cannot use SELECT SINGLE if you do not use primary key in WHERE condition or if there are duplicated results found - SELECT SINGLE and SELECT UP TO have same functional meaning, can be used at any time, both will return just one row that fulfils criteria of WHERE condition. In addition with SELECT UP TO you can sort results and influence which first row you want to get.

After reading many different opinions from people, I was curious about real results and I did small test with ABAP report, ORACLE database and 10000 queries repetition on GLPCA table with rows 116 572, not too much but still some representatives. Table has secondary indexes that I also use for comparison. And results are as expected -  sometimes SELECT SINGLE was faster, sometimes SELECT UP TO 1 ROW - there is no rule. Maybe I could notice difference if table size was much bigger, but I do not think there would be remarkable difference anyhow.

Important figure of ST05 results:

- Select statement on primary key is represented as slightly different database queries in ST05 logs - SELECT SINGLE do not adds ROWNUM validation to database query:

SELECT SINGLE * FROM glpca INTO ls_glpca WHERE gl_sirid = '000000000000104901' .

is 4th row in the ST05 results figure:

SELECT WHERE "RCLNT"=:A0 AND "GL_SIRID"=:A1

SELECT * FROM glpca UP TO 1 ROWS INTO ls_glpca WHERE gl_sirid = '000000000000104901'.

is 3rd row in the ST05 results figure:

SELECT WHERE "RCLNT"=:A0 AND "GL_SIRID"=:A1 AND ROWNUM <=:A2

So we see only small difference in addition "AND ROWNUM <= :A2". Because it is primary and only single value will be found, it seems to be unnecessary to add ROWNUM which is done for SELECT UP TO 1 row statement.

Both queries use same primary index.

- Select statement on field that is not primary key and has many duplicated values  (all rows in table have same value of RLDNR) or has only single value (only one DOCNR) are also represented as same database query - no difference in ST05 query between SELECT SINGLE and SELECT UP TO 1 row. We see 20 000 of same statement execution for first and second line of ST05 results figure - it means that SELECT SINGLE and SELECT UP TO 1 ROW are treated as same statement and use same secondary index in case of non primary key query:

SELECT WHERE "RCLNT"=:A0 AND "RLDNR"=:A1 AND ROWNUM <=:A2 

Both queries SELECT SINGLE and SELECT UP TO 1 ROW use same secondary index.

And time results related to this ST05 measurement:

And another example:

If I run test many times, advantage of different queries changes randomly so there is no constant pattern. That is why I would not judge which one is faster. I always use SELECT SINGLE as it has simpler syntax, often using it to check if row with values exists in database or not (do not care how much, just one is enough for confirmation). I would use UP TO 1 ROW if I wanted to sort by some column and get first row that meets WHERE conditions with maximum value for column that I sorted.

Regards,

Adam

[Jelena_Perfiljeva]

Great response, Adam - thank you! Hopefully we can put this to rest now.

[Former Member]

This message was moderated.

[Former Member]

Thank U All for your valuable replay..