Solved: Please explain this behaviour of blank Ranges

Former Member · ‎02-07-2012

Need some light on using blank ranges in select queries for full key. I know they improve performance...just need to know how because I can't explain it when people ask me how!!!

Or are there any unwanted effects that can't just be spotted by naked eye

For ex.


SELECT VBELN FROM VBAK INTO TABLE IT_VBAK UP TO 1000 ROWS.

GET RUN TIME FIELD START.

  SELECT * FROM VBAP
    INTO TABLE IT1
      FOR ALL ENTRIES IN IT_VBAK
        WHERE VBELN = IT_VBAK-VBELN.

GET RUN TIME FIELD STOP.

ONE = STOP - START.
CLEAR: START, STOP.

GET RUN TIME FIELD START.


  SELECT * FROM VBAP
    INTO TABLE IT2
      FOR ALL ENTRIES IN IT_VBAK
        WHERE VBELN = IT_VBAK-VBELN
          AND POSNR IN R_POSNR.

GET RUN TIME FIELD STOP.

TWO = STOP - START.

I got

ONE = 8.

TWO = 3.

former_member205763 · ‎02-07-2012

try doing this :

SELECT VBELN FROM VBAK INTO TABLE IT_VBAK UP TO 1000 ROWS.

GET RUN TIME FIELD START.
START-OF-SELECTION.
  SELECT * FROM VBAP
    INTO TABLE IT1
      FOR ALL ENTRIES IN IT_VBAK
        WHERE VBELN = IT_VBAK-VBELN
          AND POSNR IN R_POSNR.


GET RUN TIME FIELD STOP.

ONE = STOP - START.
CLEAR: START, STOP.
END-OF-SELECTION.
GET RUN TIME FIELD START.


  SELECT * FROM VBAP
    INTO TABLE IT2
      FOR ALL ENTRIES IN IT_VBAK
        WHERE VBELN = IT_VBAK-VBELN.
*          AND POSNR IN R_POSNR.

GET RUN TIME FIELD STOP.

TWO = STOP - START.

 WRITE:/ ONE.
 WRITE:/ TWO.

i did and got these as result:

68,353

63,406

i had included start-of-selection and end-of-selection in above to see if they have any effect on teh SQL connection but you can remove them and check too. its still almost same result

Edited by: Kartik Tarla on Feb 7, 2012 11:22 PM

Former Member · ‎02-07-2012

Hi,

it does not improve performance - You just select twice the same records.

If You change order of Your select statements, You should get different result:

TWO = 8.

ONE = 3.

What is more, empty ranges does not have impact on sql statement.

Regards,

--

Przemysław

former_member205763 · ‎02-07-2012

try doing this :

SELECT VBELN FROM VBAK INTO TABLE IT_VBAK UP TO 1000 ROWS.

GET RUN TIME FIELD START.
START-OF-SELECTION.
  SELECT * FROM VBAP
    INTO TABLE IT1
      FOR ALL ENTRIES IN IT_VBAK
        WHERE VBELN = IT_VBAK-VBELN
          AND POSNR IN R_POSNR.


GET RUN TIME FIELD STOP.

ONE = STOP - START.
CLEAR: START, STOP.
END-OF-SELECTION.
GET RUN TIME FIELD START.


  SELECT * FROM VBAP
    INTO TABLE IT2
      FOR ALL ENTRIES IN IT_VBAK
        WHERE VBELN = IT_VBAK-VBELN.
*          AND POSNR IN R_POSNR.

GET RUN TIME FIELD STOP.

TWO = STOP - START.

 WRITE:/ ONE.
 WRITE:/ TWO.

i did and got these as result:

68,353

63,406

i had included start-of-selection and end-of-selection in above to see if they have any effect on teh SQL connection but you can remove them and check too. its still almost same result

Edited by: Kartik Tarla on Feb 7, 2012 11:22 PM

Former Member · ‎02-07-2012

Hi,

check also ST05 traces - there will be no difference in SQL query if You use blank ranges or not.

Regards,

--

Przemysław

Former Member · ‎02-08-2012

Well I got my explanation.I was wrong all this time...

But when I tried the same thing with KONV, when this progrma executed the first time...there was a vast difference. But not from the next time!!! Still curious...

Thanks

Edited by: Ncvajja on Feb 8, 2012 4:14 AM

former_member194613 · ‎02-08-2012

>I know they improve performance

there is no difference at all, the empty ranges are removed by the db interface, the statement send to the db is identical.

Use ST05, not for the times, but to view the statement details.

Times will always vary to some extend, first executions are much slower, because they fill the db cache, which is used by the second or all other executions.