SELECT SINGLE VS SELECT UP TO 1 ROWS

[prad_k]

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?

[Sandra_Rossi]

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

[SuhaSaha]

Also check this out -

[Former Member]

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...

[Former Member]

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.

[Former Member]

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.

.

[Sandra_Rossi]

What is this link? The web site seems dummy. and it looks like an advertisement.

[raymond_giuseppi]

The first answer in the provided link is

Please do not provide link to answers from other site. If you could ping point, when I should use SELECTION SINGLE and when I should use SELECT UP TO 1 ROWs in real time project?

ROFL

[Patrick_vN]

I think that is the question actually.. not sure whether it is ping-pong, or pin-point though

[SuhaSaha]

Did you by any chance read the blog that i have mentioned in my previous response?

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.

Do you have any concrete evidence supporting this statement?

Did you perform SQL trace(s) on - SELECT SINGLE v/s SELECT UP TO 1 ROWS - without the whole (primary) key?

[Former Member]

Yes, I read that wonderful blog sometimes back.

In the same post, Horst says, "If you are interested in the contents of the single row, SELECT SINGLE should NOT be used with partial key specifications".

That is the point I wanted to take home.

If your only goal is to validate the entry in the database then even INTO statement is not needed. Why to even waste minimal memory for a structure or variable in INTO clause if you do not really care about it.

I will try to do the SQL trace and get back.

Regards,

Raju.

[Former Member]

Glad at least you figured out it was a question..

Thanks a ton!!

[prad_k]

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

[Jelena]

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)

[Former Member]

Thanks Jelena. This helps.

You are right, why would we really need to select one entry with non-key.

Problem is, some projects have 'Quality Inspectors' who go by books on SINGLE/UP TO 1 ROWs. They are the culprits for exaggerating these seemingly harmless two statements (for just entry check) .

Regards,

Raju.

[engswee]

I use SELECT UP TO 1 ROWS as a self-documenting code when I select without the full key specified.

Sometimes, it's just the way the table is designed (and maybe I can't change it) but I don't care, I just want the first row that meets the condition.

My intention is that if I were to read back the code at some point later, or some other poor soul have to figure out my code, I/they would know that I knowingly selected without the full key and ignored any possibility that a different row might have given a different result.

[Jelena]

Either way it doesn't seem like a huge deal but just scratching my head here: why is ...UP TO 1 ROWS more "self-documenting" than SELECT SINGLE? Maybe it's just me, but every time I see this construct (usually in the offshore consultants' code) I think "uhm, did you mean SELECT SINGLE?"

Of course, there are situations when you just need any one record. But I'm puzzled why would only "UP TO 1 ROWS" signal the intent? SELECT SINGLE is shorter and seems quite clear...

[Former Member]

"Of course, there are situations when you just need any one record. But I'm puzzled why would only "UP TO 1 ROWS" signal the intent? SELECT SINGLE is shorter and seems quite clear..."

It is the way the extended sysntax check works.

Select single gives you a warning when you don't specify the whole primary key and you need to set a pragma in order to ignore this warning.

Select ... UP to 1 Rows does not give this warning. So for Lazy programmers that want code to look good in the extended Syntax check you use UP TO 1 ROW when you want any row and SELECT SINGLE for cases where you want a specific row.

edit: don't ge me wrong, I use SELECT SINGLE even for the other caes, but I add the Pragma and a comment to signal that I only want any line and often also use a variable like lv_dummy to make it even more clear that I am not interested in the content at all.

[engswee]

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.

[Former Member]

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.

[Jelena]

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.

[horst_keller]

Fascinating ...

And I thought my blog covered the subject thoroughly

[horst_keller]

Hi,

I give my answer in another blog

Horst

[matt]

I don't think this needs discussing further. Locked.