topic Re: Performance issue in select query in Application Development and Automation Discussions

Performance issue in select query

Former Member — Wed, 08 Jun 2016 12:31:59 GMT

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

Re: Performance issue in select query

Former Member — Wed, 08 Jun 2016 13:18:24 GMT

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

Re: Performance issue in select query

Former Member — Fri, 10 Jun 2016 06:35:49 GMT

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!

Re: Performance issue in select query

former_member185537 — Fri, 10 Jun 2016 07:43:05 GMT

Hi Akanksha,

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

Re: Performance issue in select query

Former Member — Fri, 10 Jun 2016 09:16:59 GMT

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.

Re: Performance issue in select query

Patrick_vN — Fri, 10 Jun 2016 10:15:02 GMT

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

Re: Performance issue in select query

Former Member — Tue, 14 Jun 2016 07:33:09 GMT

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

Re: Performance issue in select query

Former Member — Tue, 14 Jun 2016 07:43:00 GMT

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

Re: Performance issue in select query

Former Member — Tue, 14 Jun 2016 07:45:46 GMT

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

Re: Performance issue in select query

Former Member — Tue, 14 Jun 2016 10:21:58 GMT

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...

Re: Performance issue in select query

Patrick_vN — Tue, 14 Jun 2016 11:19:08 GMT

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?

Re: Performance issue in select query

Former Member — Tue, 14 Jun 2016 12:41:30 GMT

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

Re: Performance issue in select query

Former Member — Thu, 14 Jul 2016 11:41:01 GMT

@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

Re: Performance issue in select query

Former Member — Mon, 25 Jul 2016 13:30:36 GMT

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

Re: Performance issue in select query

Former Member — Fri, 19 Aug 2016 10:28:55 GMT

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!

Re: Performance issue in select query

Sandra_Rossi — Fri, 19 Aug 2016 13:51:47 GMT

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.

Re: Performance issue in select query

Former Member — Mon, 22 Aug 2016 05:49:29 GMT

Hi Akanksha,

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

Create Dynamic Structure based on Field-Symbol | SCN

Thanks,

Siddharth Shah

Re: Performance issue in select query

Former Member — Tue, 23 Aug 2016 08:01:15 GMT

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

Re: Performance issue in select query

Former Member — Tue, 23 Aug 2016 10:04:31 GMT

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.

Re: Performance issue in select query

Former Member — Tue, 30 Aug 2016 10:04:16 GMT

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