Skip to Content

SELECT SINGLE VS SELECT UP TO 1 ROWS

By seeing the Heading (select single vs select up to 1 rows) many of you might suggest me to search the forum. I did search the forum and found few threads regarding the differences but I'm confused with the fact that in few threads it says SELECT UP TO 1 ROWS is faster which is marked as right answer(select single vs select upto one row | SCN) and in few threads it says performance wise there is no difference ( "SELECT SINGLE" vs. "SELECT UP TO 1 ROWS" | SCN ).

Which is right?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

11 Answers

  • Best Answer
    Posted on Jun 08, 2016 at 08:00 PM

    I was on a super-boring conference call, so had exactly enough time to write a simple short program to test different options. This is an older NW 7.01 system with Oracle DB. I used VBAK that has about 86K records in total. YMMV when using a much smaller/larger table, of course.

    The program has SELECT SINGLE and then SELECT ... UP TO 1 with a key field and then the same with non-key field. And I simply added timestamp in between. Here is the output:

    You can see there is really marginal difference in milliseconds. This is the execution plan (from ST05) for SELECT... UP TO 1:

    The same for SELECT SINGLE:

    SQL Trace summary:

    Even though this summary may make SELECT ... UP TO 1 look more appealing I avoid using it simply because it just looks stupid. SELECT SINGLE is more clear. And in real life I can't see many legitimate business scenarios for reading a single record from a large table with non-key field. (You'd probably have bigger problems in this case anyway.)

    Message was edited by: Jelena Perfiljeva (hit Submit too soon, sorry)


    select_1.jpg (35.2 kB)
    select_2.jpg (32.8 kB)
    select_3.jpg (26.2 kB)
    select_4.jpg (67.1 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • Maybe at the end of the day, it just boils down to one's own programming/coding style. The facts speak for itself - your test program and Horst's blog shows that the difference is negligible between both constructs. A style/approach that makes sense to you might mean the opposite to me and vice versa, and both are ok 😊

      As with any language (programming or spoken), there are more than one way to convey what one wants to say.

      Since both eventually generates the same SQL statement, maybe we can "shake hands" and accept one another's difference 😉 However, I would certainly disagree if someone says that I have used one approach instead of the other just because I'm lazy and want to look good in some code review. As with any programming language, some people code knowing exactly why they are coding that way, while some might just have copied it from somewhere else without thorough understanding of the code - sometimes it's not always easy to separate the sheeps from the goats.

  • Posted on Jun 10, 2016 at 04:53 PM

    Fascinating ...

    And I thought my blog covered the subject thoroughly 😕

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 06, 2016 at 05:42 PM

    No difference in performance : run a SQL trace and you'll see that it's the same statement executed at database side. It's only useful for a reason of warning message at compile time, depending if you specify the full primary key, or just partial key.


    Check the SQL trace and revert back, to definitely kill the myth ;-)

    My preferred thread: http://scn.sap.com/thread/1730155

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 06, 2016 at 07:03 PM
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 07, 2016 at 08:18 AM

    I can remember there was a difference in the past, where UP TO 1 ROWS was a little bit faster. But in the meanwhile the difference is resolved by SAP, so from a performance perspective there should be no difference.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 07, 2016 at 07:11 AM

    There is no performance difference, but the code inspector gives you a warning when using select single if you don't use where on the whole key of your source table.

    So you should use:

    SELECT SINGLE: for 1 unique element that is identified by the primary key

    SELECT ... UP TO 1 ROWS: for 1 random element

    As the others stated it's the same expression on the database...

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 08, 2016 at 02:59 AM

    Pradeep,

    Performance wise you would not find much difference.

    Question is WHEN you should use either of them?

    SELECT SINGLE MATNR WERKS MMSTA

    FROM MARC INTO ( LV_MATNR, LV_WERKS, LV_MMSTA )

    WHERE MATNR IN S_MATNR

    AND WERKS IN S_WERKS.

    SELECT MATNR WERKS MMSTA UP TO 1 ROWS

    FROM MARC INTO LWA_MARC

    WHERE WERKS IN S_WERKS

    AND MMSTA = 'Z1'.


    If your requirement is to fetch just one row (either for validation or for some other purpose) and you can provide complete PRIMARY KEYS (ie MATNR and WERKS) of the table in the WHERE clause, you should use SELECT SINGLE.


    If you cannot provide the complete PRIMARY KEYS in the WHERE clause, but you just want one entry from table, then you should use SELECT UP TO 1 ROWS because, partial primary keys or no primary keys in WHERE clause could pull multiple rows from table, but you want to restrict it to just 1 row.


    Hope this clarifies. You can see similar answer here.

    When to use SELECT SINGLE and SELECT UP TO 1 ROWs in actual project? – SAP Yard


    Regards,

    Raju.

    .

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 08, 2016 at 04:07 AM

    Thank you so much to all for taking time to provide the answers. Really helpful.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 10, 2016 at 02:53 PM

    I don't understand this comparison.

    Select single = You have the compete primary key = no one beats you.

    Select up to 1 rows = you may not have complete prim key = loser.

    🤣

    Add a comment
    10|10000 characters needed characters exceeded

    • Newbie SAP wrote:

      Select single = You have the compete primary key = no one beats you.

      Select up to 1 rows = you may not have complete prim key = loser.

      Not sure why you came to such conclusion... Markus explained above about the extended check. There are many things in ABAP that are really "potato-potahto" and come down to personal taste and local development guidelines.

  • Posted on Jun 11, 2016 at 09:23 AM

    Hi,

    I give my answer in another blog

    SELECT SINGLE vs. SELECT UP TO 1 ROWS

    Horst

    Add a comment
    10|10000 characters needed characters exceeded