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

Performance issue in select query

Hi Experts,

I have written one query to fetch data from SAP which is giving performance issue. Query is:

IF ROWCOUNT > 0.

ROWCOUNT = ROWCOUNT + ROWSKIPS.

ENDIF.


SELECT (outdata_itab-line) FROM (fromClauseString) INTO @<datarow> BYPASSING BUFFER UP TO @ROWCOUNT ROWS WHERE (whereClauseString).

CLEAR: returnRowString.

IF SY-DBCNT GT ROWSKIPS.

DO numberFields TIMES.

ASSIGN component sy-index of structure <datarow> to <datafield>.

dataFieldString = <datafield>.

CONCATENATE returnRowString '^~^' datafieldstring INTO returnRowString.

ENDDO.

dataline = returnRowString.

INSERT dataline INTO TABLE data.

ENDIF.

ENDSELECT.


Everything mentioned in select query is coming dynamically from front end. We have 2 parameters called ROWCOUNT and ROWSKIP based on which data is going to front end. Input for these 2 fields comes in loop from front end.


If condition mentioned in bold is giving issue when sy-dbcnt is more.


Please help me in finding way to escape this.


Thanks!


Akanksha



Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jun 14, 2016 at 12:41 PM

    Hi Akansksha,

    With given logic with RowCount and RowSkip i understood it is something related to paging where your front end will display selected records on each page. Say for example..it is displaying 10 records on first page and on second page it is selecting 20 records from database and deleting first 10 records and display only remaining 10 records on second page and so on.

    With this assumption I think you may take out the logic you have written inside the SELECT and ENDSELCT and try with below approach.

    1. SELECT (outdata_itab-line) FROM (fromClauseString) INTO table @<datarow> BYPASSING BUFFER UP TO @ROWCOUNT ROWS WHERE (whereClauseString).

    ENDSELECT.

    2. Delete records from internal table <datarow> from 1st line to ROWSKIPS.

    3. After deleting the internal table just perform DO ENDDO operation.

    Regards,

    Sid

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Akanksha,

      Check below code. It is working here. Hope this may help you.

      REPORT ztest_query.
       PARAMETER: p_rcount TYPE i,
                  p_rskips TYPE i,
                  p_table TYPE char30,
                  p_fields TYPE i.
      
       DATA: rowcount        TYPE i,
             rowskips        TYPE i,
             returnrowstring TYPE string,
             datafieldstring TYPE string,
             dataline        TYPE string,
             data            TYPE STANDARD TABLE OF string,
             dref            TYPE REF TO data,
             numberfields    TYPE i.
      
       FIELD-SYMBOLS: <dyn_tab> TYPE ANY TABLE.
      
       START-OF-SELECTION.
      
         CREATE DATA dref TYPE STANDARD TABLE OF (p_table).
         ASSIGN dref->* TO <dyn_tab>.
      
         numberfields = p_fields.
         rowcount = p_rcount.
       ***  DO. " TIMES.
         IF rowcount > 0.
           rowcount = 7 + rowskips.
         ENDIF.
         SELECT * "(outdata_itab-line)
                FROM (p_table)
             INTO TABLE <dyn_tab>
               BYPASSING BUFFER UP TO rowcount ROWS.
         LOOP AT <dyn_tab> ASSIGNING FIELD-SYMBOL(<datarow>).
           CLEAR: returnrowstring.
           CHECK sy-tabix > rowskips.
           DO numberfields TIMES.
             ASSIGN COMPONENT sy-index OF STRUCTURE <datarow> TO FIELD-SYMBOL(<datafield>).
             datafieldstring = <datafield>.
             CONCATENATE returnrowstring '^~^' datafieldstring INTO returnrowstring.
           ENDDO.
           dataline = returnrowstring.
      
           INSERT dataline INTO TABLE data.
           CLEAR dataline.
         ENDLOOP.
         cl_demo_output=>display( data ).
       *    IF rowskips > sy-dbcnt.
       *      EXIT.
       *    ELSE.
       *      cl_demo_output=>display( data ).
       *    ENDIF.
       *    rowskips = rowskips + p_rskips.
       *    REFRESH data.
       ***  ENDDO.
      

      I hope this will help you to build your code and improve performance of your code too.

      Regards,

      Siddharth Shah

      Regards,

      Siddharth

      Screen.PNG (7.2 kB)
      Output.PNG (14.4 kB)
  • author's profile photo Former Member
    Former Member
    Posted on Jun 08, 2016 at 01:18 PM

    Hi akanksha,

    I think the performance is mainly derived by the frontend input given in this case.

    Also is there a specific requirement to BYPASS BUFFER ?

    How big are the tables accessed? Are you selecting by key/index? Those parts can have a major inpact on the performance.

    Regards

    Markus

    edit:

    you should also take a look at the Topic SQL Injection when you want to use such kind of highly dynamic code in any productive environment.

    SQL Injection - Secure Programming - SAP Library

    How to Protect Your ABAP Code Against SQL Injection Attacks

    Message was edited by: Markus V

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello Markus,

      DB size could be any number (Mostly big tables with millions or billions of records). Sy-DBCNT is giving issue in my query. Is there any other way I can fetch records in batches from tables? Because we can not fetch all records together I think?

      Correct me if I am wrong!

  • Posted on Jun 10, 2016 at 07:43 AM

    Hi Akanksha,

    Why do you want to by pass buffer. Can you debug and get the clauses which is causing the problem

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Pras,

      Problem area is If statement :

      IF SY-DBCNT GT ROWSKIPS.

      DO numberFields TIMES.

      ASSIGN component sy-index of structure <datarow> to <datafield>.

      dataFieldString = <datafield>.

      CONCATENATE returnRowString '^~^' datafieldstring INTO returnRowString.

      ENDDO.

      dataline = returnRowString.

      INSERT dataline INTO TABLE data.

      ENDIF.

      I found in debug that is it taking hell time to come out of this loop.

      -Akanksha

  • author's profile photo Former Member
    Former Member
    Posted on Jun 10, 2016 at 09:16 AM

    I see the problem, but I've no satisfying solution therefore yet. You want to implement a kind of paging in your front end i.e. you want "page" 2 of your selection result and this are e.g. the rows 10 to 20 of your SELECT.

    So you need something like SELECT ... FROM start_row TO end_row.

    But finally this would not improve the performance, because the database has to walk through the table the same you do it now.

    I currently see only one way to improve the performance. You have to analyze the most relevant search fields in your WHERE statement and create fitting indexes at the affected tables.

    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.