topic Re: Difference between select single and select upto one row in Application Development and Automation Discussions

Difference between select single and select upto one row

Former Member — Tue, 21 Nov 2006 13:28:21 GMT

Hi Friends,

Its is simple, but at the movement iam not differenciate "<b>select single and select upto one row",</b> can any one place clarify my doubt.

Regs

Rams

Message was edited by:

ramesh bhavana

Re: Difference between select single and select upto one row

anversha_s — Tue, 21 Nov 2006 13:31:24 GMT

ex code


***********
Report Z_Difference 
Message-id 38 
Line-Size 80 
Line-Count 0 
No Standard Page Heading. 
* 
Start-Of-Selection. 
 
Data: w_Single type Posnr, 
t_Rows type standard table of Posnr initial size 0 
with header line. 
* 



select single Posnr 
from zDifference 
into w_Single. 
* 
Select Posnr 
into table t_Rows 
from zDifference 
up to 1 rows 
order by Posnr descending. 
* 
Write :/ 'Select single:', w_Single. 
Skip 1. 
Write :/ 'Up to 1 rows :'. 

Loop at t_Rows. 
Write t_Rows. 
EndLoop.

*******************************************

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.

You can refer to the below link..

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

rgds

Anver

Re: Difference between select single and select upto one row

Former Member — Tue, 21 Nov 2006 13:32:14 GMT

Hi Ramesh,

Logically there is no difference between them.

Select Single and Select Up to one row behaves same. But Technically If we pass all the key fields in where clause, select single is used to improve the performance.

Sreedhar

Re: Difference between select single and select upto one row

Former Member — Tue, 21 Nov 2006 13:33:19 GMT

hi,

primarily select single hits the data base only once and fetches the data at a time,

use select single if u have primary keys in where condition.

a select upto one row will hit the data base twice and fetch a single record this genrally is used when you are searching for a non primary key in the table.

generally these are used in validations use select single with check tables for better performance.

se se30 u have a button tips and tricks that would be more informative and descriptive i guess!

santhosh

Re: Difference between select single and select upto one row

Former Member — Tue, 21 Nov 2006 13:33:58 GMT

Basically they both return the same result.

But one important feature of SELECT UPTO is that it can fetch upto any number of records you want by specifying in the query like

SELECT * FROM t000 INTO TABLE i_t000 UP TO 10 ROWS. which is not possible in SELECT SINGLE as the result is always 1 or no records.

Regards

Kathirvel

Re: Difference between select single and select upto one row

Former Member — Tue, 21 Nov 2006 13:41:56 GMT

Ramesh,

select single: use when you can give all the priamary keys(so that u can get only one record) in the where condition.

no endselect is required.

select up to one rows: use when there is a posibility that there can be many records with ur where condition.

need ot use endselect.

-Anu

Re: Difference between select single and select upto one row

Former Member — Tue, 21 Nov 2006 13:45:54 GMT

Hi Ramesh

Interesting results.

SELECT SINGLE * FROM t000 INTO w_t000.

Execution time in microseconds:

ABAP 14.727 = 51,0%

Database 42 = 0,1%

System 14.111 = 48,9%

0% 50% 100% 28.880 = 100,0%

SELECT * FROM t000 INTO w_t000 UP TO 1 ROWS.

ENDSELECT.

Execution time in microseconds:

ABAP 13.480 = 90,2%

Database 40 = 0,3%

System 1.431 = 9,6%

0% 50% 100% 14.951 = 100,0%

UP TO 1 ROWS just takes 50% time taken by SELECT SINGLE.

Regards

Kathirvel

Re: Difference between select single and select upto one row

Former Member — Tue, 21 Nov 2006 13:47:49 GMT

hi Ramesh,

Check

Regards,

Santosh