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

Performance issue for select statement

Former Member — Fri, 27 Oct 2006 09:42:50 GMT

Hi Friends,

Iam getting the performance problem for this below statement and its running lot of time in debug mode itself and timingout and in catsdb table raufpl and raplzl fields are not keyelments .Actually for this select statement iam taking the data from AFVC table aufpl and aplzl.

IF NOT it_afvc[] IS INITIAL.

SELECT counter workdate raufpl raplzl status catshours FROM catsdb

INTO TABLE it_catsdb

FOR ALL ENTRIES IN it_afvc

WHERE workdate < sy-datum

and raufpl = it_afvc-aufpl

AND raplzl = it_afvc-aplzl

AND status EQ '30'.

Can you please suggest any one how to optize fro this statement

points will be award and your answers are highly apprciable

regards,

Govind

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 09:55:12 GMT

Create an Index on the table.

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 09:57:52 GMT

In your case it seems that the number of records it is trying to fetch is causing the issue...

<b>The solution for the same is to either select the data using package size OR use Cursors.</b>

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 10:18:37 GMT

generally...u need to compramise on performance or the no of records..fetch records in a fixed time and ur problem would be solved. and the other thing which may b small but advisable is to use symbols where ever possible donot use EQ instead use =.

regards,

santhosh

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 10:43:29 GMT

Hi Govind

I would say your problem is that you are not using keys or an index. The table already has 5 indices (on our system anyway) so adding another might affect the performance of any updates to this table. There are 2 existing indices which might be of use;

1. Uses Pernr, Status and Workdate

2. Uses Pernr and Workdate.

Obviously these are no use if you haven't got the Pernr - is there anyway that you can re-write your previous code to get a Pernr value? You can then make use of the either of these indices. Given that you are not testing for equality against workdate (i.e. workdate < sy-datum rather than workdate = a_date) the first one would be my preference.

Other than that you might have to use Packages or Cursors - it would be better to try and use an index though.

Hope this helps

Andrew

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 11:57:32 GMT

Hi ,

In my code pernr it wont come actually iam calculating for each project wise getting the total no of catshours.

Can u pls suggest how i will use package or cursor concept ?

thanks & Regards,

govind

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 11:57:55 GMT

govind

One thing you can try your hand at. just comment the code 'workdate < sy-datum'. once you select all the entries into table it_catsdb, then delete records where workdate < sy-datum.

2. just check how much of data is there in the table for your select query. if the volume of record is more, then check size of your internal table i.e., it_catsdb. if frequently paging is done, this will also hamper performance.

shane

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 12:01:52 GMT

Data : C1 type cursor.

Open cursor C1 for select * from dbtable where .

Do.

fetch next cursor C1 into wa.

if sy-subrc <> 0.

Close cursor.

Exit.

endif.

==>process the data

enddo.

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 12:05:25 GMT

Hi Shane,

okay, actually in it_catsdb getting around 90,000

records ,at the time of debugging the cursor its not moving out of this select statement and timingout .Is any other possible ways i will do?

Regards,

Govind

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 12:57:55 GMT

is it happening, even after removing code " workdate < sy-datum" from select query?

and secondly what is the size of your internal table. i mean to say you would have declared something like

it_catsdb initial size 100 / it_catsdb occurs 100. whatever you have declared, what is that size.

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 13:04:08 GMT

Hi Shane,

yes right now i removed workdate and still its timing out bcz of some 90,000 records and I declared internal table it_catdsdb size 0 ie occurs 0

Regards,

Govind

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 13:25:10 GMT

yes i can understand it is getting tough on you. but one thing you are selecting for all entries in it_afvc. so is there any means you can select " COUNTER " field into it_afvc prior to selecting data from db table CATSDB. do you understand what i mean to say. first you would be filling internal table IT_AFVC. so what i mean to say over here is, if you can fill this table by the field "COUNTER " prior to selecting data from CATSDB, then it may solve your problem. beacuse COUNTER is the key field over there. so that time your code looks like follows:

sort it_afvc by counter.

IF NOT it_afvc[] IS INITIAL.

SELECT counter workdate raufpl raplzl status catshours FROM catsdb

INTO TABLE it_catsdb

FOR ALL ENTRIES IN it_afvc

WHERE counter = it_afvc-counter

and raufpl = it_afvc-aufpl

AND raplzl = it_afvc-aplzl

AND status EQ '30'.

if there is no such provision of doing so, then probably you have to use the technique of PACKAGE SIZE.

hope it makes sense for you and works

shane

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 13:41:39 GMT

How many records (total) in catsdb?

Rob

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 14:08:18 GMT

Hi Shane,

I checked it in AFVC table here didn't have the counter field but its exist only in catsdb but u mentioned <b>WHERE counter = it_afvc-counter</b> ??

and can you please tell how i will use PACKAGE SIZE ?

Your answers are highly appreciable

Thanks in advance,

Govind

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 14:12:01 GMT

Hi Rob,

In catsdb total records around 6 lakh but at the time of seeing the data from AFVC table to CATSDB

ie afvc-aufpl = catsdb-raufpl and afvc-aplzl = catsdb-raplzl for this getting around 90,000 records for these records in debug mode the cursor not moving out from this select statement and timing out.

Thanks & Regards

Govind

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 14:25:35 GMT

OK - does this SELECT run more quickly or more slowly than yours:

SELECT counter workdate raufpl raplzl status catshours
  FROM catsdb
  INTO  TABLE it_catsdb
  WHERE workdate < sy-datum
  AND   status EQ '30'.

Rob

Re: Performance issue for select statement

Former Member — Fri, 27 Oct 2006 14:59:33 GMT

Hi Govind

I'm not sure about packages or cursors having never had to use them. However, I wonder if there would be any benefit in creating a select-option style table based on the counter field. You could then populate it with ranges of data, for example, 1 to 30000 (depending on the contents of the field). You could then put the main select within a loop that increments the range on each iteration i.e. 2nd iteration would be 30001 to 60000 until the select returns no records. After each select you would then append the lines to a main internal table that will eventually hold all the relevant records. It would be using the PK field but as it would be selecting all records I don't know if there would be a performance gain - perhaps other people would like to comment on the principle? The thought has only just occurred to me so I suspect it is fatally flawed somewhere but might be worth a shot!

I still suspect the poor performance is down to not using PK or index fields effectively as much as the number of records.

Kind regards

Andy

Re: Performance issue for select statement

Former Member — Mon, 30 Oct 2006 05:51:13 GMT

govind

i can see, this chapter getting hard on you. but finally, i have two proposals for you. firstly, if your selection criteria does not fit with any of the existing indexes, then probably you have to create one more index according to your selection criteria, which means your db index should consider the following fields in your case:

1. raplzl

2. raplzl

3. workdate

but mind one thing, the order of the index should be decided by you. like frequently, based on which fields and in what order you are retrieving data from CATSDB. also one more thing you have to look at. that is how many indexes are already existing in database for CATSDB. if there are more than 5 - 6 number of indexes already existing in database, then it is quite risky to build one more index, before deleting one.

the second and final proposal from my side is if non of the technique works in your case, then you should get back to your basis guyz regarding the timeout issue, probably they can increase the timeout duration for the test ambience.

hope it helps

shane

Re: Performance issue for select statement

Former Member — Mon, 30 Oct 2006 06:00:02 GMT

oops!

i would like to make slight change on my recent posting.

before creating new index, just give one trial with the existing index if any, with the fields as

1. PERNR

2. STATUS

3. WORKDATE

in your case you are using combination of the fields status and workdate. so just give a little trial to this index.

shane

Re: Performance issue for select statement

Former Member — Mon, 30 Oct 2006 14:12:21 GMT

Shane,

In my catsdb table already 5 database indexes are created i think for a database table only 5 database indexes are possible right? and my basis person also not ready to increase the time out period from 5 mts.

Actually as of now i used package size and cursor technique also still for these also timing out and the existing index pernr status workdate anyway its exist but still taking time (but in my select query pernr wont come).

Regards,

Govind