Select Endselect or Select single

[IanStubbings]

Consider the following code:

SELECT * FROM hrp1000

UP TO 1 ROWS

WHERE plvar = ls_i1001-plvar

AND otype = ls_i1001-otype

AND objid = ls_i1001-sobid

AND istat = '1'

AND begda =< lv_begda

AND endda => lv_endda

AND langu = sy-langu

ORDER BY PRIMARY KEY.

ENDSELECT.

Could this be replaced by a SELECT SINGLE?

We have a QA process which forbids SELECT...ENDSELECTs. In this instance I believe a SELECT SINGLE to be erroneous.

Any thoughts anyone?

Thanks in advance

Ian

[Former Member]

Hello Ian,

You can as well use a SELECT SINGLE in this case, but there's a small glitch. You would get the result you desire, but there will be a warning in the EPC (Extended Program Check)which says that you have not specified the complete primary key. As you might have noticed, the table has just one more key field , SEQNR.

If the EPC Warning is okay, I suppose you can go ahead with SELECT SINGLE, as long as you are just checking for the existence of some entries.

Regards,

Anand Mandalika.

[Former Member]

You can use SELECT INTO TABLE and avoid the SELECT...ENDSELECT issue.

[IanStubbings]

Thanks for the input guys.

My dilemma is that since it is an HR table and a date range is part of the key, if I am only using greater than less than expressions for these, a SELECT SINGLE could bring back the wrong information as it picks the first record it comes to. It could be possible that a person has changed their name for instance and therefore has a more current record which the SELECT SINGLE cannot determine explicitly.

Is my assumption correct?

Ian

[Former Member]

Possible workaround for this issue is to create a daterange and use this in your select statement. Overall only a select single can be sufficient if you have all key fields in you selection. In the case where date fields are part of the table key I believe a select single statement will not always ensure a correct command. Tiest.

[Former Member]

Hello Ian,

Are you sure that there will be more than 1 record which will satisfy the date range condition?

In that case, how do you think SELECT UP TO 1 ROWS...ENDSELECT would be a better choice?

Regards,

Anand Mandalika.

[Former Member]

HI Anamd!!

When there exists more than one record ..which optionone is suppose to use.can u plz let me know

regards

pnp

[Former Member]

When you consider a situation where more than one record exist, it automatically mean that the keys arent fully qualified. Hence Select SINGLE is not advisable (though can be used with warning).

Further,as i mentioned in the previous post, Incase the table has buferring allowed, select upto 1 rows will be a better option, as the repeated use of the statement can very well fetch data from the buffer instead of looking into the database everytime, which happens in case of SELECT SINGLE.

Hope this helps

Regards,

Vijay

[MauricioMiao]

Hello Ian,

As you properly said SELECT SINGLE in that case is erroneous.

The funny point is according to SAP Performance course, in that case the SELECT UP TO 1 ROWS is faster than SELECT SINGLE because you are not using all the primary key fields.

I belive your customer should restructure his QA process, because sometimes SELECT...ENDSELECT statements are the right option to be used.

Regards,

Mauricio

[Former Member]

Hi Ian,

The result of both select single and select-end-select will be the same.

As others said, it might be a question of performance as 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.

Since your coding standards prohibit the use of select-endselect and with a reason, you might try the solution suggested in a reply here to use 'select into table' option and then read the internal table for the first record available.

But, please remember that you will do a "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.

Regards,

Srinivas

[ChristianFi]

I have read a lot of threads concerning the issue - select single vs select * endselect and a lot of them stated that there would be a difference considering performance.

Personally I think this is a rumor but I confess that the investigation I made in this subject is limited to an oracle database and other databases may act differently.

What I did was rather simple.

I compared

select * from marc up to 1 rows where werks = l_werk.

endselect.

with

select single * from marc where werks = l_werk.

via st05 - sql trace.

<b>

Both statements were changed into the same native sql statement. So how would they react differently?

</b>

In my option - if you compare both selects you have to be fair. You may not use any select statements which makes a "sorting" of the result on databaselevel necessary such as aggregations or the "order by" statement.

If you do so you will get a different behaviour but then you simply do not the same anymore.

Also you should not test it on buffered table (here it makes a difference how the table is buffered.

Just my 2cts.

Christian

[IanStubbings]

Thanks to all for your responses.

What I am seeking to resolve is that a blanket ban on SELECT...ENDSELECT is not correct. Whilst in 99% of circumstances you should not use this construct, in this particular instance I believe it to be valid.

I have had incorrect data before when using a select single on an HR table and believe the creation of an internal table to get around the construct (while valid and used before by colleagues to avoid this issue) an unnecessary overhead.

What I would like to do is update our QA process with correct information rather than follow a procedure to potentially create incorrect code and retrieve bad data.

I agree with Mauricio's response entirely - obviously - and will use it as 'evidence'.

Before I close the thread, does anyone else want to add anything? Do you follow and formal standards documentation?

Cheers

Ian

[christian_wohlfahrt]

Hi,

I agree with Christian's analysis: as long as your statements are similar, most databases will translate into same native statement -> no DB performance difference can occur.

One minor point in ABAP part:

In debugging select ... up to 1 rows ... endselect you can follow execution:

- select statement

- endselect statement

- select statement

- first statement behind

So here mini loop is execute (but I'm discussing about nanoseconds, I know).

Select single is executed as single command.

I don't like changes of programs to avoid trouble with automatic program checks or QA rules.

So I would prepare myself with arguements and go into a fight (OK, never done it myself before).

Just because general rules don't fit to each situation.

Additional programing (like ... into table + read) might be an easy way, but more lines of code violate one of the most important rules:

Keep it small and simple!

Regards,

Christian

[Former Member]

Hi,

Could you please mention the reservation your QA have with using SELECT...ENDSELECT?

Please keep one thing in mind that no matter what type of statement you issue, SELECT INTO TABLE, SELECT ENDSELECT, SELECT SINGLE, etc, they all translate to a single DB select statement and sent to the DB only once (unless you have issued a SELECT inside a LOOP). The DB selects data and sends it back in fixed packets to the app server. It is the processing of the resultset on app server that varies depending upon the construct used.

Regards

[Former Member]

Hi All,

I did a Performance testing in the Split screen option in se30 with the modified form of Christian's code. I included matnr so as to satisy the primary key.

tables marc.

select single * from marc

where matnr = 'MAT'

and werks = 'ZZ02'.

select * from marc up to 1 rows

where matnr = 'MAT'

and werks = 'ZZ02'.

endselect.

The System test result showed that the variant Single * takes less time than Up to 1 rows. The database that I did the testing is on Oracle. On debugging I found that when 'endselect' is there the System goes to the database twice even when it is 1 row. So doesn't these statistics indicate that select single is faster.

regards,

Madhavan

[ChristianFi]

Hello I checked my coding again with st05

( I removed the where condition, though)

report zztest .

tables marc.

select single * from marc.

select * from marc up to 1 rows.
endselect.

The result (and please note that the coding contains both selects was 1 open and 1 fetch for each select.

 MARC       REOPEN      0      0 SELECT WHERE "MANDT" = '400' AND ROWNUM <= 1    
 MARC       FETCH       1      0                                                 
 MARC       REOPEN      0      0 SELECT WHERE "MANDT" = '400' AND ROWNUM <= 1    
 MARC       FETCH       1      0                                                 

NB in my example the select single was slowlier (probably because the database cache was not filled at the time the first statement was executed.

It could be however that the close cursor takes some extra time since it seems that this is done at the end of a select - endselect statement

       Brutto        Netto Zeile Include                          Aufruf                                                                                
62.451      5 /SEW/COPC_TEST01                 Select Single MARC  
                      870      7 /SEW/COPC_TEST01                 Fetch MARC          
                      468      7 /SEW/COPC_TEST01                 Open Cursor MARC    
                       20      7 /SEW/COPC_TEST01                 Fetch MARC          
                       12      8 /SEW/COPC_TEST01                 Close Cursor MARC                                                                                

[christian_wohlfahrt]

Hi!

Interesting news!

I copied Madhavan's example and made some tests.

First with complete key, then both statements where translated slightly different (Oracle database version 9.2.0.5.0) and select single was little bit faster.

But then I tested without matnr in where-clause. Then both statements where translated identical, runtimes where identical.

We were discussing selection without complete key - and then both statements are handled identical.

In case complete (primary) key is available, select single is out of question best solution.

I'm almost feeling like a student making researches...

Christian

[christian_wohlfahrt]

Hi,

here database differences appear:

my select single

5 MARC REOPEN 0 SELECT WHERE "MANDT" = '001' AND "MATNR" = 'XX' AND "WERKS" = '1000'

289 MARC FETCH 1 0

my select up to 1 rows

5 MARC REOPEN 0 SELECT WHERE "MANDT" = '001' AND "MATNR" = 'XX' AND "WERKS" = '1000' AND ROWNUM <= 1

299 MARC FETCH 1 0

I don't have close cursor. This would invoke additional network access (like I mentioned already with this 'mini-loop' example) - but depends obviously on database version.

Regards,

Christian

[Former Member]

Hi,

I think each one of us will be able to come up with certain results that will makes sense depending upon their own environment, work load, buffer condition, etc. I just tried out tracing both statements, specifying complete primary key.

For SELECT SINGLE, time taken to fetch was 12.938

For SELECT ENDSELECT WITH UP TO 1 ROWS, time was 6.592

Since both statements were translated for the first time, hence preparing them and opening the cursor took some time as well but we are not discussing that. What might be more important is that the execution plan is more or less the same, only an additional level for COUNT STOP KEY for SELECT ENDSELECT UP TO 1 ROWS. But the estimated cost and rows are the same.

Can anyone please refer to the SAP Note that says SELECT SINGLE is better, or the other way around...

Regards

[IanStubbings]

Thanks to all for taking the time to reply to my query and apologies for the late reply - very busy week.

I fear we did stray a little from the original thrust though. Performance is always important but the only reason for using the SELECT ENDSELECT this time was to produce the correct data where a SELECT SINGLE could not. This is due to the date range used in the key of the table and therefore a SELECT SINGLE will not necessarily return the record that I want. Using the ORDER BY PRIMARY KEY clause, I guarantee that the record being retrieved is correct.

SELECT * FROM hrp1000

UP TO 1 ROWS

WHERE plvar = ls_i1001-plvar

AND otype = ls_i1001-otype

AND objid = ls_i1001-sobid

AND istat = '1'

AND begda =< lv_begda

AND endda => lv_endda

AND langu = sy-langu

ORDER BY PRIMARY KEY.

ENDSELECT.

The constrct was rejected (initially) by QA for no other reason than the fact that ENDSELECTs are banned, even though in this case I also have the addition of UP TO ONE ROWS to ensure performance is not an issue.

Very interesting to see the the performance results which I can also use as evidence.

Thank you all

Ian

[christian_wohlfahrt]

Hi Ian!

So you have still some doubts.

As Anand and Srinivas already stated, both selects will bring correct result - just what I think (ups, thought - see below).

But there is this 'order by primary key'-question. I think, on transparent tables without 'for all entries' no difference should occur - but who knows? At least most databases translate select single without complete key into <i>same</i> native SQL as up to 1 row syntax.

You had already problems by switching to select single for a HR-table? Tell us in detail!!

Okay, I had a detailed look at this table. Coming from pricing conditions, I can say: using valid from as key field is a problem (and both validity dates is worth).

It's strongly depending on maintenance transaction, which different validity ranges can occur to identical rest of key (except seqnr). If you can have two valid entries (meaning valid from <= today <= valid to) with different valid from values (because second 'current' entry was created later), then you are in trouble. But 'order by primary key' won't help, either. Then you have to sort by begda <b>descending</b> (and not any longer by primary key). This leads to a statement, which can't any longer into identical select single -> you can 'survive' QA with endselect-statement (or you can prove rules as stupid).

I don't have seqnr > 0 (-> no example); I don't know maintenance transaction - but here crucial design questions are defined.

Here is still open question, I understand.

At least we got some very interesting performance analysis in between

Regards,

Christian

P.S.: Sorry for long sentences, please read carefully

[ChristianFi]

I think there is a misunderstanding

select * from table up to 1 rows. endselect. and select single * from table gives the same result back provided the access method determined by the cost based optimizer is the same.

However, to make sure that it is always in order of the primary key you have to add primary key which you cannot do with select single. So in this case you cannot rely on the result. Since he needs the order by primary key - his only choice is between

select up to 1 rows into table order by primary key

and

select up to 1 rows order by primary key.

Select single is not an option.

I would not talk of incorrect results but it could be different ones.

Christian

[christian_wohlfahrt]

Hi Christian,

boring Sunday, too?

The part of incorrect results should be solved with

 
SELECT * FROM hrp1000
UP TO 1 ROWS
WHERE plvar = ls_i1001-plvar
AND otype = ls_i1001-otype
AND objid = ls_i1001-sobid
AND istat = '1'
AND begda =< lv_begda
AND endda => lv_endda
AND langu = sy-langu
<b>ORDER BY begda descending.</b>  
    "first part of key is equal fixed value!
ENDSELECT.

And this is not possible to translate into select single - otherwise results are correct in both cases, as already discussed in detail.

Question: can entries exist, which need to be selected with descending valid from sorting? (Or am I totally wrong with my analysis? - Be carefull, here are two validity dates <b>and</b> a counter (seqnr) in key fields!)

I'm looking for further comments!

Christian