05-08-2006 7:37 AM
Hi all,
Can anybody tell me the difference between SELECT SINGLE and SELECT UP TO 1 ROWS statements.
Thanks
Suresh
05-08-2006 7:40 AM
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
05-08-2006 7:39 AM
Hi
In SELECT SINGLE u need to specify the all the key fields inorder to uniquely identify the records.
But SELECT UP TO 1 ROWS will fetch the first entry from your table regardless of the key fields...Also check the ABAP FAQs Section in SDN....
Cheers,
Abdul Hakim
05-08-2006 7:40 AM
when ur using select single the where condition should have all the key fields of the table here .
select up to 1 rows can be used if keyfields of table are not used in where condition to resolve performance issue.
05-08-2006 7:40 AM
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
05-08-2006 7:44 AM
Hi Suresh,
Select Single : Will return with the first occurence of the record matching the where clause.
Select upto 1 row : Will select all the records matching the where clause but will return the first entry out of the matching set ofr records.
Performance wise the SELECT SINGLE is better.
<b>Please mark helpful answer.</b>
Regards,
Amit Mishra
05-08-2006 7:52 AM
Hey Amit,
U say SELECT SINGLE is faster than SELECT... UP TO 1 ROWS, but Shravanthi say the other way. im bit confused.
anyhow thanks to all of u...
05-08-2006 8:07 AM
hi suresh,
you can check the below link for your clarification..
Cheers,
Abdul Hakim
05-08-2006 7:45 AM
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).
rgds,
latheesh
05-08-2006 7:46 AM
Hai Suresh
1. SELECT [SINGLE [FOR UPDATE] | DISTINCT] *
2. SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn
3. SELECT [SINGLE [FOR UPDATE] | DISTINCT] (itab)
1) SELECT [SINGLE [FOR UPDATE] | DISTINCT] *
Effect
In the result set, the columns correspond exactly in terms of order, ABAP/4 Dictionary type and length to the fields of the database table (or view ) specified in the FROM clause .
Example
Output all flight connections from Frankfurt to New York:
TABLES SPFLI.
SELECT * FROM SPFLI
WHERE
CITYFROM = 'FRANKFURT' AND
CITYTO = 'NEW YORK'.
WRITE: / SPFLI-CARRID, SPFLI-CONNID.
ENDSELECT.
Example
Output all free seats on the Lufthansa flight 0400 on 28.02.1995:
TABLES SFLIGHT.
DATA SEATSFREE TYPE I.
SELECT SINGLE * FROM SFLIGHT
WHERE
CARRID = 'LH ' AND
CONNID = '0400' AND
FLDATE = '19950228'.
SEATSFREE = SFLIGHT-SEATSMAX - SFLIGHT-SEATSOCC.
WRITE: / SFLIGHT-CARRID, SFLIGHT-CONNID,
SFLIGHT-FLDATE, SEATSFREE.
2) SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn
Effect
The order, ABAP/4 Dictionary type and length of the columns of the result set are explicitly defined by the list s1 ... sn . Each si has the form
ai or ai AS bi .
Here, ai stands either for
a field f of the database table or
a aggregate print.
Examples
Output all flight destinations for Lufthansa flights from Frankfurt:
TABLES SPFLI.
DATA TARGET LIKE SPFLI-CITYTO.
SELECT DISTINCT CITYTO
INTO TARGET FROM SPFLI
WHERE
CARRID = 'LH ' AND
CITYFROM = 'FRANKFURT'.
WRITE: / TARGET.
ENDSELECT.
Output the number of airline carriers which fly to New York:
TABLES SPFLI.
DATA COUNT TYPE I.
SELECT COUNT( DISTINCT CARRID )
INTO COUNT FROM SPFLI
WHERE
CITYTO = 'NEW YORK'.
WRITE: / COUNT.
Output the number of passengers, the total weight and the average weight of luggage for all Lufthansa flights on 28.02.1995:
TABLES SBOOK.
DATA: COUNT TYPE I, SUM TYPE P DECIMALS 2, AVG TYPE F.
DATA: CONNID LIKE SBOOK-CONNID.
SELECT CONNID COUNT( * ) SUM( LUGGWEIGHT ) AVG( LUGGWEIGHT )
INTO (CONNID, COUNT, SUM, AVG)
FROM SBOOK
WHERE
CARRID = 'LH ' AND
FLDATE = '19950228'
GROUP BY CONNID.
WRITE: / CONNID, COUNT, SUM, AVG.
ENDSELECT.
3) SELECT [SINGLE [FOR UPDATE] | DISTINCT] (itab)
Effect
Works like SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn if the internal table itab contains the list s1 ... sn as ABAP/4 source code, and like SELECT [SINGLE [FOR UPDATE] | DISTINCT] * , if itab is empty. The internal table itab can only have one field which must be of type C and cannot be more than 72 characters long. itab must appear in parentheses and there should be no blanks between the parentheses and the table name.
Note
With this variant, the same restrictions apply as for SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn .
Example
Output all Lufthansa flight routes:
TABLES: SPFLI.
DATA: FTAB(72) OCCURS 5 WITH HEADER LINE.
REFRESH FTAB.
FTAB = 'CITYFROM'. APPEND FTAB.
FTAB = 'CITYTO'. APPEND FTAB.
SELECT DISTINCT (FTAB)
INTO CORRESPONDING FIELDS OF SPFLI
FROM SPFLI
WHERE
CARRID = 'LH'.
WRITE: / SPFLI-CITYFROM, SPFLI-CITYTO.
ENDSELECT.
SELECT UP TO 1 ROWS Means
It will fetch the first row depends on the where condition
and the rest of the rows omitted
SELECT Single Means
It will fetch exact row depends on the where condition
and the rest of the rows omitted
this will increase the performance
Thanks & Regards
Sreenivasulu P
Message was edited by: Sreenivasulu Ponnadi