topic Re: Open Cursor (Does this drastically improve performance) in Application Development and Automation Discussions

Open Cursor (Does this drastically improve performance)

Former Member — Fri, 15 Feb 2008 08:32:17 GMT

Experts, Rob/Thomas,

Kindly give me some education regarding Open Cursor functionality.

Please give me Blogs/links explaining the usage/advantages

I am trying to fine tune the below code




==========
*Check if datapackage is initial or not
  IF NOT itab_temp[] IS INITIAL.                      "(1)

    REFRESH itab_zfccoo02.

*******************

  OPEN CURSOR dbcur FOR
   SELECT DISTINCT REF_DOC_NO FISCPER3 /BIC/ZCOREFBZ COMP_CODE FISCPER
   CREDITOR PART_CCTR PART_COORD PART_WBSEL /BIC/ZPCOOBJNR AUXACCVAL
   AUXACCTYPE CO_DOC_NO CO_ITEM_NO /BIC/ZCOBUSTRN /BIC/ZCOVTYPE
   DOC_DATE /BIC/ZCOREFBZ
   FROM /bic/azfccoo0200
            for all entries in itab_temp
            where comp_code       = itab_temp-comp_code
            and fiscyear          = itab_temp-fiscyear
            and fiscper           = itab_temp-fiscper
            and /bic/zcovtype    NE '11'
            and /bic/zcovtype    NE '60'.



  DO.
    FETCH NEXT CURSOR dbcur APPENDING TABLE itab_zfccoo02
                                   PACKAGE SIZE 20000.
    IF sy-subrc <> 0.
      EXIT.
    ENDIF.
  ENDDO.

  CLOSE CURSOR: dbcur.

===========

Appreciate your Help and replies

Thanks,

Aby Jacob

Re: Open Cursor (Does this drastically improve performance)

Former Member — Fri, 15 Feb 2008 14:37:23 GMT

There was a question about this earlier this week or last.

OPEN CURSOR is used so that you can have two access paths to the same table at the same time. I've never seen anything that leads me to believe that it helps performance.

Rob

Re: Open Cursor (Does this drastically improve performance)

Former Member — Sat, 16 Feb 2008 07:17:05 GMT

Thanks Rob,

I found this Help documentation helpful

http://help.sap.com/saphelp_nw04s/helpdata/en/fc/eb3b23358411d1829f0000e829fbfe/frameset.htm

Regards,

Aby

Re: Open Cursor (Does this drastically improve performance)

Former Member — Mon, 18 Feb 2008 09:09:54 GMT

FETCH NEXT CURSOR dbcur APPENDING TABLE itab_zfccoo02

PACKAGE SIZE 20000

First I thought it looks o.k.

The main usage for open cursor is the creation of package size, and 20.000 is a good size! It is not about performance improvement, but about prevention of performance deterioration. The database creates redo logs in the background, where it keeps the information to do a rollback if the database update does not work.

But here is not update, only a select and package of size 20.000 are selected into an internal table which must hold everything. This is nonsense!

The FOR ALL ENTRIES selects anyway in packages of much much smaller size (5 to 30). The FOR ALL ENTRIES can create duplicates which are automatically

deleted when the final result is available, this will not work with packages.

It also depends what you want to do with the table itab_zfccoo02.

Siegfried

Re: Open Cursor (Does this drastically improve performance)

Former Member — Mon, 18 Feb 2008 11:16:12 GMT

Thanks Siegfried,

Really appreciate your Suggestion.

Would it be Okay to remove the Open Cursor functionality

altogether from the SELECT statement ??

==========


SELECT DISTINCT REF_DOC_NO FISCPER3 /BIC/ZCOREFBZ COMP_CODE FISCPER
   CREDITOR PART_CCTR PART_COORD PART_WBSEL /BIC/ZPCOOBJNR AUXACCVAL
   AUXACCTYPE CO_DOC_NO CO_ITEM_NO /BIC/ZCOBUSTRN /BIC/ZCOVTYPE
   DOC_DATE /BIC/ZCOREFBZ
   
into TABLE itab_zfccoo02

FROM /bic/azfccoo0200
            for all entries in itab_temp
            where comp_code       = itab_temp-comp_code
            and fiscyear          = itab_temp-fiscyear
            and fiscper           = itab_temp-fiscper
            and /bic/zcovtype    NE '11'
            and /bic/zcovtype    NE '60'.

==========

Please advice.

Warm Regards,

Aby

======

Re: Open Cursor (Does this drastically improve performance)

Former Member — Mon, 18 Feb 2008 11:22:14 GMT

I would try a different version, and always don't remove old coding directly

Use

IF ( 1 = 2 )

ELSE.

ENDIF.

to test new versions.

How many records do you expect in itab_zfccoo02 in production?

Siegfried

Re: Open Cursor (Does this drastically improve performance)

ThomasZloch — Mon, 18 Feb 2008 12:49:37 GMT

Hi Aby,

like the other gentlemen I am doubtful whether OPEN CURSOR has a significant advantage over regular SELECT statements regarding performance improvement.

There is one application where I have used OPEN CURSOR in the past, and that is with the addition WITH HOLD, regarding block processing of mass data where I have to do a COMMIT WORK after each block has been processed. A standard SELECT ... PACKAGE SIZE would result in a shortdump because the DB cursor is lost, not so with OPEN CURSOR ... WITH HOLD and then FETCH ... PACKAGE SIZE.

Greetings

Thomas

Re: Open Cursor (Does this drastically improve performance)

Former Member — Mon, 18 Feb 2008 13:12:31 GMT

Thanks Thomas,

Let me try out your suggestion.

"" OPEN CURSOR ... WITH HOLD and then FETCH ... PACKAGE SIZE.""

Warm Regards,

Aby

Re: Open Cursor (Does this drastically improve performance)

Former Member — Tue, 26 Feb 2008 08:53:45 GMT

Friends,

I am Closing this Thread

Many Thanks for all your help and support.

Warm Regards,

Aby Jacob