Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Select Single Vs Select upto 1 row

Former Member
0 Kudos

Hi All,

Please tell me which of the two statements is better if we want to retrieve only one record from a table.

Select single or Select upto 1 rows?

Regards,

Saurabh

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Refer to the below related threads

7 REPLIES 7

former_member181995
Active Contributor
0 Kudos

Mathuria,

Select sengle is the better in performance way.

Why because select single up to one row has taken little time in aggrigation after selecting all entries which lies in where condition.

so select single single is the better in performance.

Amit.

Former Member
0 Kudos

Refer to the below related threads

Former Member
0 Kudos

hiii,

Select single * from table

It fetches single record from the database, based on the condition you specified in the where clause.

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.

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 or lack of, applies any aggregate, ordering or grouping functions to them and then returns the first record of the resultant result set.

Where as select * from table up to 1 row

Fetches first record if the condition specified in the where clause is satisfied, otherwise it doesn't fetch any record.

hopefully it clears your question..

reward if helpful..

thanks..

former_member194613
Active Contributor
0 Kudos

There is a lot of confusion in the replies and also in the other thread.

If yoou ask 'A versus B' then this implies that you can use both for the same task, which is here not really true.

SELEC SINGLE should be used for SELECT with full primary key. where only 1 record CAN come back.

UP TO 1 ROWS is the special case of UP TO n ROWS can be used with any WHERE condition and gives you the first record which is found.

>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 or lack of, applies any aggregate,

> ordering or grouping functions to them and then returns the first record of the resultant result

> set.

This in incorrect, the UP TO n ROWS does not read all, only if the ORDER BY is added, then you will get the first records in sort order which requires that all rfecords are read.

If you use UP TO 1 ROWS with a WHERE condition which is fulfilled by many records, then the first record is usually found very fast, even faster than a SELECT SINGLE.

If you use UP TO 1 ROWS or SELECT SINGLE with the same WHERE condition, then both are more or less the same.

Siegfried

0 Kudos

Hi,

On using the select up to 1 rows or select single the opearation is going to be same. The thing that you need to keep in mind for consideration is the performance based on the where clause. Make sure of the volume of the data. If the volume is less then any approach is fair enough to the system.

If in case the volume is large then create an index for the table so that the fectch is performed better.

Rgds,

Swetha.

0 Kudos

If you use UP TO 1 ROWS with a WHERE condition which is fulfilled by many records, then the first record is usually found very fast, even faster than a SELECT SINGLE.

Hey, I'm curious about this. What gets sent to the DBMS by the two forms to account for the difference? I seem to recall reading somewhere that SELECT SINGLE is implemented as a single DB fetch, whereas SELECT is a prepare with bindings followed by fetch. But my memory is quite fallible these days.

Former Member
0 Kudos

You need to think about what "better" means to you.

If you are asking which is faster, the difference is likely to so negligable that unless you are going to perform this selection 1,000,000 times you really shouldn't care. If you will perform the operation 1,000,000 times, maybe you are better off not performing it individually 1,000,000 times thereby leading you to an entirely different performance optimization question.

If you are asking from a logic standpoint, then this is only relevent if you expect to get more than one record back. In which case, do you care which record you get back?

If the answer is no, you might want to double check that you shouldn't care. One case I can think of for that is checking for the existance of any records. In this case, the answer leads back to a performance concern and so long as you are hitting the leading field or fields of an index somewhere, you should be fine.

If the answer is yes, then which record? The only examples I can think of for this are min/max type problems, because you can't effectively specify those with the grammar of the ABAP SELECT. In that case, SELECT ... UP TO 1 ROWS with an ORDER BY achieves the logical result that you want.