Skip to Content
author's profile photo Former Member
Former Member

Performace on select query. Open cursor vs all entries

HI Experts,

I have a performance issue on the selecting the huge number of records in the RESB table with all entries, hence I am trying to fine tune the code as shown below in 2 ways.

1. I tried the Open cursor method to select the records and I checked with the get run time statement,however there is no much performance improvement and could not see any consistent performnace

2. The second option was to eliminate the duplicate entries in the table which I used in the for all entries, This also not made much difference in the performace.

note that the where clause used in the select query is also a key field in the table.

I am looking for some alternate solution for the above issue. Please let me know how to take care the performnace issue when we hit the big tables.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Oct 11, 2010 at 03:22 PM

    Hi,

    Sai, I think you covered all the points of performance tunning, I like to see your select query.

    Thanks,

    Anmol.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 11, 2010 at 03:22 PM

    Open cursor is not an option, IMHO....might be nice if you needed to process every row in an entire table.

    What's the WHERE clause look like? does it have an equal condition for every field in the RESB primary key? If it does, that's as fast as you're going to get, probably, unless there are also other fields named with performance-killers like NOT operators or IS INITIAL or something like that.

    What does ST05 show for access?

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 11, 2010 at 04:03 PM

    Sai Sai,

    Your need review your performance. It's recommended use SLIN Tcode, performance too, use of Sorted o Hashed Internal Tables, use of index, populate the table using INTO TABLE statement after of select and NOT INTO CORRESPONDING FIELDS

    For Example

    TYPES: BEGIN OF ty_resb,

    rsnum TYPE resb-rsnum,

    rspos TYPE resb-rspos,

    rsart TYPE resb-rsart,

    END OF ty_resb.

    DATA gti_resb TYPE SORTED TABLE OF ty_resb WITH NON-UNIQUE KEY rsnum rspos rsart. SORTED TABLE

    START-OF-SELECTION.

    select rsnum rspos rsart

    INTO TABLE gti_resb USE INTO TABLE for populate the INTERNAL TABLE

    FROM resb

    WHERE rsnum = '000000009'

    %_HINTS MSSQLNT 'index("RESB~0")'. RESB INDEX

    Remember that, when you use INTENAL TABLE, if you use an LOOP ITAB sentences, is better that ITAB was declared like SORTED TABLE and if you use READ TABLE ITAB, is better that ITAB was declared like HASHED TABLE.

    Regards,

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 11, 2010 at 04:11 PM

    Moderator message - Please post in the correct forum and search before asking post locked Rob

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.