07-07-2010 10:27 PM
Hello everyone,
I've spend the last 2 hours comparing and researching the above mentioned statements and I don't have a clear result.
Is there any official documentation which statement should process a SELECT faster that I expect to return only one record as a result of accessing only non-(unique-)index fields?
Does this also depend on the used DBMS (ORACLE 10.2.0.4.0) and/or the SAP release (NW 7.01 SP6)?
Would be great to get some feedback from the performance gurus!
Thanks in advance and best regards,
Alej
07-08-2010 1:57 AM
Hi,
according to note 135048 SAP adds same oracle hint FIRST_ROWS for both statements: UP TO x ROWS and SELECT SINGLE. So there should not be a big difference. It might be different for newer releases of Oracle. I use SELECT SINGLE when I have full primary key and UP TO 1 ROWS when I don't. You can use ST05 to figure out which query is better for your case.
Cheers
07-08-2010 2:35 AM
Hi,
I think Martin's reply is correct. I use SELECT SINGLE when I have full primary key.
I use UP TO 1 ROWS when I have not full primary keyt.
Regards,
Gaito
07-08-2010 7:15 AM
Hi,
I use SELECT SINGLE when I have full primary key.
I use UP TO 1 ROWS when I have not full primary keyt.
Sorry, But it is not necessary, whichever you want you can used it.
One major differnace is that For all enries not work with Select Single but work with Select up to 1 rows
But I don't think there is any performance difference between SELECT SINGLE and SELECT UP TO 1 ROWS
Kind Rgds
Ravi Lanjewar
Edited by: Ravishankar Lanjewar on Jul 8, 2010 11:49 AM
07-08-2010 10:17 AM
This is also my recommendation
I use SELECT SINGLE when I have full primary key.
I use UP TO 1 ROWS when I have not full primary key.
Reason is readability of code not performance!
Performance of the 2 statements is 99,9% identical, there is no performance issue related to these statements.
If you unterstand everything then it should be clear, that a SELECT SINGLE must always be fast.
However, a UP TO 1 ROWS can be fast but can also be very slow:
If there is no index support and only very few records fullfill the WHERE-clause, so the full table must be scanned.
However, this problem is not related to the statement, but only to the fact, that no index supports the WHERE. If done
frequently then an index is needed.
Siegfried
07-08-2010 2:23 PM
> If you unterstand everything then it should be clear, that a SELECT SINGLE must always be fast.
> However, a UP TO 1 ROWS can be fast but can also be very slow:
> If there is no index support and only very few records fullfill the WHERE-clause, so the full table must be scanned.
> However, this problem is not related to the statement, but only to the fact, that no index supports the WHERE. If done frequently then an index is needed.
Here you again make a difference between SELECT SINGLE and UP TO 1 ROWS, that's wrong. There is no difference at all (as you say they are "99,9% identical"). What you indicate here is the issue when you don't use correct keys in the WHERE, that's not related to one of the 2 statements.
07-08-2010 2:37 PM
very well said, Sandra. If anybody is in doubt, check this:
tables: t100.
select single * from t100 where text = 'not existing'.
select * from t100 up to 1 rows where text = 'not existing'. endselect.
e.g. on ORACLE: 2 full table scans (no index support)
reg. differences there are so often questions aobut select single / up to 1 rows...
I usually point out the single record buffer support
for select single but besides that do not make any comparison or differences since i think it
is not very helpful.
Same thing with "direct read" and "sequential read" ... i'm often asked what the difference is.... sigh
07-08-2010 2:06 PM
I believe the SELECT .. UP TO 1 ROWS has on theoretical level a longer runtime, though I'm pretty sure it's not measurable. Here's why: If you debug the statement, you'll see that it executes the SELECT, then goes to the ENDSELECT and comes back to the SELECT. So the interpreter seems to run a loop once, whereas a SELECT SINGLE is just one statement. (Ok, this is admittedly some handwaving argument...)
On Oracle the actual database statement (native SQL) is identical: Both use the hint FIRST_ROWS(1) and limit the result set via ROWNUM<=1.
Siegfried Boes wrote:
I use SELECT SINGLE when I have full primary key.
I use UP TO 1 ROWS when I have not full primary key.
Reason is readability of code not performance!
Interesting and in line with others, though in general I disagree with this statement. To me an idiomatic use of SELECT SINGLE is whenever you have conditions that ensure that only a single record can be returned. A unique index (not just primary key) should suffice and one could possibly argue to extend this with uniqueness known on application/functional level (admittedly more fragile and uncontrolled).
However, as far as actual usage is concerned I think the Extended Program Check spat out warnings if SELECT SINGLE is used without a primary key (don't have a system for checking this at the moment and if so, maybe there was some strong reason to do it that way?!). I think that was the reason to adopt usage of SELECT .. UP TO 1 ROWS in case of a SELECT SINGLE without a full primary key.
The part that I always disliked about the SELECT - ENDSELECT construct was the possibility of short dumps when debugging (commit). As far as I know this applies also with a UP TO 1 ROW limit, but not for the SELECT SINGLE.
Cheers, harald
07-08-2010 2:28 PM
> The part that I always disliked about the SELECT - ENDSELECT construct was the possibility of short dumps when debugging (commit). As far as I know this applies also with a UP TO 1 ROW limit, but not for the SELECT SINGLE.
That's now corrected (most of time) with the new debugger as it runs by default in an "exclusive mode". See Note 726719 - ABAP debugging in production system / DEBUGGING_IMPOSSIBLE for more info.
07-08-2010 3:00 PM
Sandra, Harald,
SLIN says:
The SELECT SINGLE is designed to allow a most efficient access to exactly one record of a database table. It requires that you specify the entire primary key in the WHERE condition with AND and EQ (or "="). Then an access to the database is possible, which requires only one communication step between application server and database server.
If you did not specify the entire primary key, instead of a direct access on the database server, a "normal" SELECT is performed, which is the same as a SELECT ... UP TO 1 ROWS. In this case, a cursor is opened, a record is read, and the cursor is closed. This requires a number of communication steps between application server and database server.
The extended program check provides a warning to inform you that with the mentioned SELECT SINGLE not the expected fast direct access can be performed.
but at least on ORACLE it seems to be not true.
On other databases it might be true... (would have to check) or maybe it USED to be true but is not
true anymore, or maybe database server should be database interface, or ... .
do 3 times.
select single * from t100 bypassing buffer where sprsl = 'D' and arbgb = 'D-' and msgnr = '001'.
select * from t100 up to 1 rows where sprsl = 'D' and arbgb = 'D-' and msgnr = '001'. endselect.
enddo.
gives:
7 T100 REOPEN 0 SELECT WHERE "SPRSL" = 'D' AND "ARBGB" = 'D-' AND "MSGNR" = '001'
485 T100 FETCH 1 0
6 T100 REOPEN 0 SELECT WHERE "SPRSL" = 'D' AND "ARBGB" = 'D-' AND "MSGNR" = '001' AND ROWNUM <= 1
493 T100 FETCH 1 0
6 T100 REOPEN 0 SELECT WHERE "SPRSL" = 'D' AND "ARBGB" = 'D-' AND "MSGNR" = '001'
426 T100 FETCH 1 0
5 T100 REOPEN 0 SELECT WHERE "SPRSL" = 'D' AND "ARBGB" = 'D-' AND "MSGNR" = '001' AND ROWNUM <= 1
432 T100 FETCH 1 0
5 T100 REOPEN 0 SELECT WHERE "SPRSL" = 'D' AND "ARBGB" = 'D-' AND "MSGNR" = '001'
421 T100 FETCH 1 0
5 T100 REOPEN 0 SELECT WHERE "SPRSL" = 'D' AND "ARBGB" = 'D-' AND "MSGNR" = '001' AND ROWNUM <= 1
431 T100 FETCH 1 0
no visible difference in terms of calls. 2 calls for each variant. 6 index unique scans, full key specified.
the rownum is even omitted for the select single here... (if the primary key is fully specified, if not, it is added).
Kind regards,
Hermann
07-08-2010 3:27 PM
Hi,
> However, as far as actual usage is concerned I think the Extended Program Check spat out warnings if SELECT SINGLE is used without a primary key
you are right here. For unique secondary indexes we get the warning as well.
> I believe the SELECT .. UP TO 1 ROWS has on theoretical level a longer runtime, though I'm pretty sure it's not measurable. Here's why: If you debug the statement, you'll see that it executes the SELECT, then goes to the ENDSELECT and comes back to the SELECT. So the interpreter seems to run a loop once, whereas a SELECT SINGLE is just one statement. (Ok, this is admittedly some handwaving argument...)
and i guess you might be right. In the DBI we usually distinguish between statements that select 1 record and statements that might select more. If there is a difference it is in the DBI i guess. But it should be so small that it doesn't matter. Maybe anybody want's to write a test (?)
Kind regards,
Hermann
07-08-2010 9:16 PM
Sandra, thanks for pointing out the difference between old and new debugger and referencing the OSS note. I actually still use the old debugger frequently as I'm always struggling with the number of open sessions...
Hermann, it's always funny to me how those seemingly trivial questions turn out to be good challenges for one's understanding of how things work internally.
As far as usage is concerned I really like the idea of having a SELECT SINGLE in cases where the developer knows (on technical or functional/application level) that at most one record will match. It's nice, because by reading the code you implicitly learn something about the data model (especially if this cannot be derived by looking at the technical meta data). Unfortunately developers often get it wrong and use SELECT SINGLE, where they should have really prepared for processing multiple records. However a SELECT .. UP TO 1 ROWS would be as wrong in that case (and thus not help with my ideal interpretation for this case that the developer knew that possibly multiple records matched, but he only needed one among them).
07-09-2010 8:00 AM
... interesting that you don't understand ...
O.k. Harald is right, I did not add the unique secondayr indexes to the SELECT SINGLE to keep it simple and to use only the recommendation which was written above. As far as I know there not so many unique secondary indexes.
The second point was completely misunderstood.
> What you indicate here is the issue when you don't use correct keys in the WHERE, that's not related to one
> of the 2 statements.
If I follow the rule above then not the statement but the use cases are different!
A very common usage of UP TO 1 ROWS is the exstence check (never use COUNT(*) ), and there it can be that it is not worth to build an index. And it can be that no row fulfills the condition, then the UP TO 1 ROWS requires a full table scan of the whole table.
07-09-2010 10:33 AM
The UP TO 1 ROWS does not need a SELECT / ENDELECT also
SELECT *
INTO TABLE lt_dd03l
FROM dd03l
UP TO 1 ROWS
WHERE ...
The issue with the unique secondary key, I would stick to recommendation that I not take that into account, because the secondary key can be there, but does have to. I would leave it open, what is taken in that case.
If measured correctly, then the runtimes can not be distinguished. We are talking about a few hundret microseconds and
differences of a few microseconds close to measurement accuracy.
Siegfried
07-09-2010 10:42 AM
Hi Siegfried,
> The UP TO 1 ROWS does not need a SELECT / ENDELECT also
>
> SELECT *
> INTO TABLE lt_dd03l
> FROM dd03l
> UP TO 1 ROWS
> WHERE ...
>
interesting! i never thought about that so far.
conclusion:
SINGLE vs. UP TO
difference in extended syntax checks,
difference in single recor buffer access (SAP Release < 7.02)
difference in readability, coding guidlines, ...
difference with FAE (FOR ALL ENTRIES) (up to will work but emulated after selection, single won't)
NO DIFFERENCE IN PERFORMANCE
i think we could finalize it like this, can't we?
Kind regards,
Heramnn
Edited by: Hermann Gahm on Jul 9, 2010 5:02 PM
12-28-2010 6:28 AM
Dear Friends,
Difference SELECT SINGLE:
1. It always retrieves only one record at a time
2. Here we must pass entire primary key combination in the where condition
3. This is used to retrieve a particular record.
SELECT UPTO ONE ROW:
1.It always read only one record
2.Here we pass part of key combination in the where condition it always picks the first record among the matched records.
3. This is used for validation
Regards,
K.S.Kannan
07-09-2010 11:15 AM
In a training session, I was told that, we should use SELECT UPTO 1 ROWS, if we are not using the complete set of primary keys as in case of SELECT UPTO 1 ROWS, it will fetch only one row from database to buffer as well as to target field. But in SELECT SINGLE, it will transfer multiple rows to buffer, but pass only one row to target field, if all the primary keys are not used.
I think, it's right platform to verify it............ Kindly let me know, whether it's correct.
12-29-2010 9:17 AM
Ooops,
didn't notice this one:
Posted: Jul 7, 2010 11:27 PM
It's in vain to wait for an answer from the topicstarter...
However, hope my answer is applicable to many questions of this kind.
07-09-2010 3:51 PM
> was told that, we should use SELECT UPTO 1 ROWS, if we are not using the complete set of primary keys
yes
> as in case of SELECT UPTO 1 ROWS, it will fetch only one row from database to buffer as well as to target field.
yes
> But in SELECT SINGLE, it will transfer multiple rows to buffer, but pass only one row to target field, if all the primary
> keys are not used.
no
07-09-2010 3:58 PM
This thread now added to
Great discussion - many thanks to all participants.
08-19-2010 5:54 AM
Hi,
what Manabu Kuwagaito has quoted is absolutely correct if we have the full primary key then its better to use SELECT SINGLE where as in other situation where u r not having the full primary key combination then we will use SELECT UP TO 1 ROWS.
Regards,
karan lokesh.
08-25-2010 8:03 AM
hi,
Judging from my experience, using select single and select * up to 1 rows for all entries don't have much difference in performance.
But select * up to 1 rows **** endselect will get a worse performance.
Desmond
12-29-2010 9:05 AM
Alejiandro Sensejl,
I've spend the last 2 hours comparing and researching the above mentioned statements and I don't have a clear result.
Maybe you'd better spend some time for obtaining and analyzing DB plans for yor concrete query? Then you can decide what's better for this particular situation.
For example, in the same Oracle system and NW version like yours i wrote the code:
DATA: ls_ekpo TYPE ekpo.
SELECT SINGLE *
FROM ekpo
BYPASSING BUFFER
INTO ls_ekpo
WHERE ebeln = 'XXXXXXXXXX'.
SELECT *
FROM ekpo
UP TO 1 ROWS
BYPASSING BUFFER
INTO ls_ekpo
WHERE ebeln = 'XXXXXXXXXX'.
ENDSELECT.
and then used the ST05.
Plans are the same, SAP passes to Oracle the hint FIRST_ROWS and uses rownum<=1:
SQL Statement
SELECT
/*+
FIRST_ROWS (1)
*/
*
FROM
"EKPO"
WHERE
"MANDT" = :A0 AND "EBELN" = :A1 AND ROWNUM <= :A2
Execution Plan
SELECT STATEMENT ( Estimated Costs = 5 , Estimated #Rows = 2 )
|
--- 3 COUNT STOPKEY
| Filter Predicates: ROWNUM<=TO_NUMBER(:A2)
|
--- 2 TABLE ACCESS BY INDEX ROWID EKPO
| ( Estim. Costs = 5 , Estim. #Rows = 2 )
| Estim. CPU-Costs = 46.827 Estim. IO-Costs = 5
|
------1 INDEX RANGE SCAN EKPO~0
( Estim. Costs = 4 , Estim. #Rows = 18 )
Search Columns: 2
Estim. CPU-Costs = 28.886 Estim. IO-Costs = 4
Access Predicates: "MANDT"=:A0 AND "EBELN"=:A1
12-30-2010 9:09 AM
Hi,
SELECT SINGLE : select single is based on PRIMARY KEY and It will take the first record directly without searching all the records.
SELECT UPTO 1 ROW : It will check all records for given condition and take
the first record .
So SELECT SINGLE seems more efficient.
Regards,
Priyanka Kaushik.
12-30-2010 11:31 AM
Please lock that thread, before the discussion starts again
>SELECT SINGLE : select single is based on PRIMARY KEY and It will take the first record directly without searching all the records.
what is the first record dircetly
>SELECT UPTO 1 ROW : It will check all records for given condition and take the first record .
often repeated nonsense!
they are identical identical identical!!! because the same statement goes to the database, they are onyl different in ABAP (or Open SQL)!!
12-30-2010 5:54 PM
05-16-2020 9:28 PM
05-16-2020 9:29 PM
05-17-2020 11:31 PM
There is a difference not mentioned yet, however only up to Netweaver 7.0 EhP1, where SELECT UP TO 1 is bypassing single record buffering even if your WHERE condition has the whole primary key.
In those old releases, single record buffering only works if you use the SELECT SINGLE syntax.