11-21-2006 1:28 PM
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
11-21-2006 1:31 PM
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
11-21-2006 1:31 PM
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
11-21-2006 1:32 PM
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
11-21-2006 1:33 PM
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
11-21-2006 1:33 PM
Hi
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
11-21-2006 1:41 PM
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
11-21-2006 1:45 PM
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
11-21-2006 1:47 PM