Performance issue in select query

[Former Member]

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

[Former Member]

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

Message was edited by: Markus V

[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!

[former_member185537]

Hi Akanksha,

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

[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

[Former Member]

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.

[Patrick_vN]

This requirement reminds me of entityset paging in a gateway service ($top & $skip). In which case one has several options:

a. SELECT UP lv_max ROWS.  DELETE it_tab TO iv_skip

lv_max = $top + $skip

b. CURSORS, similar to Open SQL statements above but use OPEN CURSOR, FETCH NEXT CURSOR etc. to control which entries are returned  

c. Native SQL,  CL_SQL_STATEMENT with SELECT LIMIT $top OFFSET $skip

[Former Member]

Hi Armin,

Thanks for the reply. I can not pass field details in where as everything in my query is getting generated dynamically. Thats why stuck here.

Is there anything else I can look for?

-Akanksha

[Former Member]

Hi Patrik,

I can't use cursor as query is dynamic. I think option a could be useful in my scenario. as I have Max rows and skip rows options available. But I am unable to use that because I am using @<datarow> as type any. Because output data is also getting generated dynamically.

Could you suggest way out to do this?

Thanks!

Akanksha

[Former Member]

What I tried to say: I see no possibility to improve the performance in ABAP in your case. You have only a choice at database level. You can create adequate indexes, if you know that the most people only search for specific fields on specific tables. Additionally you have to keep up to date the statistics of your database to support the optimizer of the database.

Of course you can also upgrade your hardware...

[Patrick_vN]

Hmm.. and would splitting the select into two be an option? A first select like you have now that only selects the key values, and a second (with FAE) that gets the non-key field data?

[Former Member]

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

[Former Member]

@Siddharth Shah In step 2 If I delete <datarow> I will be able to perform Do Endo operation as mentioned below? -

DO numberFields TIMES.

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

       dataFieldString = <datafield>.

       CONCATENATE returnRowString '^~^' datafieldstring INTO returnRowString.

     ENDDO.

Also When I tried to change select query as you mentioned it gives error saying "<datarow> is not an internal table.

-Akanksha

[Former Member]

Hi Akanksha,

Use 'INTO' and not 'INTO TABLE'. Also please prepare one internal table within SELECT and ENDSELECT.

please try below steps with correction.

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

     Prepare internal table based of <datarow> (e.g - IT_DATATAB)

ENDSELECT.

  2. Delete records from internal table IT_DATATAB from 1st line to ROWSKIPS.

3. After deleting the internal table just perform DO ENDDO operation. (try with <datarow> or IT_DATATAB ... and see whichever works for you.

Also whichever solution i am giving is based on my assumption and you might need to change little bit of code as per actual scenario.

Regards,

Sid

[Former Member]

Hi Siddharth,

I am unable to create internal table based on <datarow> as its getting generated at runtime. Can you suggest something on this?

field symbol: <datarow> type any.

Thanks!

[Sandra_Rossi]

To create a data object at runtime, use RTTC (classes cl_abap_*descr and statement CREATE DATA ... TYPE HANDLE object). It's precisely described in the ABAP documentation (runtime type services), and helped many many times in the forum.

[Former Member]

Hi Akanksha,

Please refer below SDN thread link. Hope it can help you.

Create Dynamic Structure based on Field-Symbol | SCN

Thanks,

Siddharth Shah

[Former Member]

Hi Siddharth,

I think question got little diverted from the main issue. Issue is select query here. Though we pass rowskips every time but this query selects records from 1 to end. For example if rowskip is 2000 then first time its picking 1-2000 records and later 1-4000, 1-6000 and so on. That's the reason for performance. Do Endo is not getting executed every time as it is within IF() condition of DBCount.

Is there any way that this query doesn't fetch records from beginning?

Regards

Akanksha

[Former Member]

Hi Akanksha,

Try with Below code directly in your program. Replace your SELECT - ENDSELECT with below code.

IF ROWCOUNT > 0.

    ROWCOUNT = ROWCOUNT + ROWSKIPS.

ENDIF.

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

     delete datarow from 1 to rowskips.

     loop at datarow ASSIGNING FIELD-SYMBOL(<datarow>).

      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.

  ENDLOOP.

[Former Member]

Hi Siddharth,

In code <datarow> is structure not table. Thats why we used Select-End Select here. So you suggest here is to change this structure to table?

-Akanksha

[Former Member]

Hi Akanksha,

If your ABAP version is supporting inline data declaration then the code I have given might work without any change as per your logic and it will create "datarow" as internal table directly as I have written INTO TABLE DATA(datarow) and also <datarow> will also be created as field symbol too as per the inline data declaration.

I have tried same code in my system and it is working and so it should work in your system too if your ABAP 7.3 and higher.

Please try same code directly and see if any difference is coming or not. If this is helpful then please mark thread accordingly.

Regards,

Siddharth Shah

[Former Member]

Hi Siddhartha,

I tried using your code but I get below error:

We are on release 740.

-Akanksha

[Former Member]

Hi Akanksha,

Either you are at lower version of ABAP or some syntax error which you might need to resolve at your end.

If you wish we can communicate over the mail for this issue. My mail id is mentioned on my profile.

Regards,

Siddharth Shah

[Former Member]

This message was moderated.

[Former Member]

This message was moderated.

[Former Member]

Hi,

This is getting interesting but lengthy now.

Now you need to define dynamic internal table <DATAROW> as field-symbol instead of "DATAROW" now. And also change above code to replace DATAROW with <DATAROW>.

Try to follow below link to declare dynamic table.

Create Dynamic Structure based on Field-Symbol | SCN

Lets see if this works for you or else I will try to build code with dynamic internal table and share later.

What I suspect here is, your select - endselect is causing issue as it is going inside to the IF condition for all records while it should only go from rowskips. I have updated my profile with required detail.

Regards,

Siddharth

[Former Member]

Hi Siddhartha,

datarow is already defined as field symbol of type any:

FIELD-SYMBOLS:  <datarow> TYPE ANY.

I also tried to change this to type ANY TABLE but it did not work on my case. As datarow is getting generated at run time with below code:

LOOP AT FIELDS INTO fieldsRow.

     fieldname = SY-TABIX.

     CONCATENATE 'string' fieldname INTO fieldname.

     CONDENSE fieldname.

     fieldDescr-name = fieldname.

     columnName = fieldsRow-TEXT.

     REPLACE FIRST OCCURRENCE OF SUBSTRING '~' IN columnName WITH '-' RESPECTING CASE.

     fieldDescr-type ?= cl_abap_typedescr=>describe_by_name( columnName ).

     APPEND fieldDescr TO fieldDescrTab.

   ENDLOOP.

   rowStructDescr = cl_abap_structdescr=>create( fieldDescrTab ).

   create data rowReference type HANDLE rowStructDescr.

   ASSIGN rowReference->* TO <datarow>.

Please let me know if I am doing things wrong here.

Thanks!

-Akanksha

[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