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: 

Getting a single value with a FOR ALL ENTRIES

Former Member
0 Kudos
3,024

Hi everyone,

I have to get a single value from a for all entries query. What would be more performant?

1)

  SELECT mdocm FROM imrg
   INTO l_mdocm
   UP TO 1 ROWS
   FOR ALL ENTRIES IN t_impt_tab
    WHERE point EQ t_impt_tab-point
      AND mdtxt EQ gv_clmno.
  ENDSELECT.

2)

  DATA: t_mdocm TYPE STANDARD TABLE OF imrg-mdocm.

  SELECT mdocm FROM imrg
    INTO TABLE t_mdocm
    UP TO 1 ROWS
    FOR ALL ENTRIES IN t_impt_tab
      WHERE point EQ t_impt_tab-point
        AND mdtxt EQ gv_clmno.

  READ TABLE t_mdocm INDEX 1 INTO l_mdocm.

Please tell me which one and why. Database is MSSQL release 9.00.3042.

Thanks!

Pablo

1 ACCEPTED SOLUTION

Former Member
0 Kudos
470

Hi Pablo,

I think that both statements have the same performance since your are stating explicitly that only 1 row has to be read. In other words only one block has to be retrieved from the database to the application server.

I have analyzed your statements with the transaction ST05 and got a INDEX SKIP SCAN for the index A (MANDT, POINT, INVTS). Since I'm working with an Oracle 10.2 DB and the cardinality of the column MANDT (the one who is missed in your statement) is very low. The result is fine for me.

If your MSSQL doesn't have this feature I would recommend you to include the field MANDT in your query.

Suerte!

Jaime

9 REPLIES 9

Former Member
0 Kudos
470

Does either pass a syntax check?

Well, I was a bit surprised - it does pass.

So what does the EXPLAIN function of ST05 show?

Rob

Edited by: Rob Burbank on Jul 15, 2008 11:30 AM

former_member70391
Contributor
0 Kudos
470

Hi ,

You can write this code and execute the program via SE30 to check the performance. In the field Program enter the program name and click on execute. Come back and click on evaluate to check the performance.

Do the same thing for other code and compare the eprformnace.

In Normal scenario data base access should be green.

Hope this helps.

Thanks & Regards,

Nagaraj Kalbavi

0 Kudos
470

Nagaraj - buffering will be an issue in SE30. The second run will usually show a lower execution time. The EXPLAIN function in ST05 will give more consistent results.

Rob

Former Member
0 Kudos
470

this is not recommended...generally

because the internal table allocate more space than a variable.

if the intenal table had to catch many no of single entries then it is must

0 Kudos
470

Hi,

when you are fetching single record there is not recommonded for internal table. better to youe variable instead of internal table.

Regards.

Sriram.

Former Member
0 Kudos
470

Performance aside - the logic isn't necessarily correct. If you're only after a single value then you should specify the full table key as you are not guaranteed a unique single value unless you do so.

0 Kudos
470

You are right, but I'm not interested in all values, just one, and it will be correct. I'm only wondering about performance.

As I have been reading, for fetching a single variable it's not recommended to use an internal table, despite the use of the select - endselect.

I'll try to measuring times, but there is too little data at the moment.

Thanks,

Pablo

Former Member
0 Kudos
471

Hi Pablo,

I think that both statements have the same performance since your are stating explicitly that only 1 row has to be read. In other words only one block has to be retrieved from the database to the application server.

I have analyzed your statements with the transaction ST05 and got a INDEX SKIP SCAN for the index A (MANDT, POINT, INVTS). Since I'm working with an Oracle 10.2 DB and the cardinality of the column MANDT (the one who is missed in your statement) is very low. The result is fine for me.

If your MSSQL doesn't have this feature I would recommend you to include the field MANDT in your query.

Suerte!

Jaime

Former Member
0 Kudos
470

Hi Pablo,

If u are passing key fields in the 'Where' clause it's better to use 'Select single '.

If the fields in the 'Where' clause r not primary key fields use 'Select up to 1 rows'.

Why cant u use variable instead of internal table.

U want single MDOCM so populate the value into V_MDOCM(variable) instead of i_mdocm.