Skip to Content
avatar image
Former Member

Difference between select single and select upto 1 rows

Hi,

What is the difference between select single and select upto 1 rows.

Regards,

Alex

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    Aug 16, 2007 at 09:53 AM
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 16, 2007 at 09:53 AM

    search da forum this question has been discuessed a thousand times

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 16, 2007 at 09:55 AM

    Hi,

    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,

    Bhaskar

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello boss ,

      if u spend 1 min to check this question in SDN before posting it U CAN FINDOUT NUMBER OF ANSWERS i dont y u post this question again

      SORRY IF SPELL ANYTHING WRONG

      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.

      Thanks

      Naveen khan

  • avatar image
    Former Member
    Aug 16, 2007 at 09:57 AM

    Hi

    SELECT SINGLE would be the most performance efficient if you supply complete KEY. Though SELECT SINGLE would work like SELECT.. UPTO 1 ROWS when you specify partial key.

    Whereas SELECT.. UPTO 1 ROWS.. Will select the first rows that the select fetches when you specify partial key.

    Reward points if useful.

    Thanks & Regards,

    Vasudeva Rao.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 16, 2007 at 09:58 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.

    Select Single is the best one compared to UPto one rows.

    Select Single will get the first record from the table which satisfies the given condition.So it will interact once with the database.

    UTO 1 rows will get the list of the records for the given match and iwll show the first record from the list.So it will take time to get the record.

    SELECT SINGLE VBELN from VBAK

    where MATNR = '1M20'.

    ---Thjis will get the first matched record and will display the record

    SELECT VBELN from VBAK

    where MATNR = '1M20' upto 1 rows.

    ---Thjis will get the list of matched records and will display the first record

    The Major difference between Select Single and Select UPTO 1 rows is The Usage Of Buffer for each.

    Select Single will search for all the satisfied data and bring all that data into Buffer and later it will give to that data to the program.

    Select UPTO 1 Rows will end the search after getting the 1st satisfied record and gives that record to the program.

    Thus Select Single will take much processing time when compare with Select UPTO 1 rows.

    Also

    check these threads..

    Difference between Select Single and Selct upto 1 row

    Difference between Select Single and Select upto 1 row

    Difference between select single and select upto one row

    Difference between 'select single *' and 'select upto 1 rows'

    difference between select single and select up to 1 rows

    regards,

    srinivas

    <b>*reward for useful answers*</b>

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 16, 2007 at 10:07 AM

    SELECT SINGLE

    If SINGLE is specified, the resulting set has a single line. If the remaining additions to the SELECT command select more than one line from the database, the first line that is found is entered into the resulting set. The data objects specified after INTO may not be internal tables, and the APPENDING addition may not be used.

    When SINGLE is being specified, the lines to be read should be clearly specified in the WHERE condition, for the sake of efficiency. When the data is read from a database table, the system does this by specifying comparison values for the primary key.

    SELECT SINGLE is also used mainly for validation

    SELECT SINGLE * FROM DTAB INTO DTAB WHERE CONDITION

    SELECT SINGLE COMES UNDER SINGLE BUFFERING

    SELECT UP TO N ROWS

    This addition restricts the number of rows in the result set. A data object of the Type i is expected for n. A positive number in n indicates the maximum number of rows in the result set. If n contains the value 0, all selected rows are passed to the result set. If n contains a negative number, an exception that cannot be handled is raised.

    If the addition ORDER BY is also specified, the rows of the hit list are sorted on the database server and only the number of sorted rows specified in n are passed to the result set. If the addition ORDER BY is not specified, n is filled with any number of rows in the result set that meet the WHERE condition.

    SELECT UP TO COMES UNDER FULL BUFFERING

    reward if usefull

    Add comment
    10|10000 characters needed characters exceeded