topic Re: Regarding Performance issue in Application Development and Automation Discussions

Regarding Performance issue

Former Member — Mon, 09 Feb 2009 07:23:27 GMT

Hi All,

I have written one select on Z table, it is having some 40Laks records, I have the secondaty index on 5 fields, In my where clause iam using the first 3 fields. But still the select statement is taking more than 10 mins.

the select statement is below..

Select ZX

FROM TABLE ZSDR

INTO TABLE i_zsdr

FOR ALL ENTRIES IN i_ZTABLE

WHERE F1 = i_ztable-f1

AND f2 IN s_f2

AND f3 IN s_f3.

But still the program is taking more time, in i_ztable i have 80,000 records.

is there any other way to improve the performance of the program.

the secondary index avaiable in fields F1, F2, F3, F4 and F5..but iam using the first 3 fields in the same order.

Please suggest.

Regards,

Hari

Re: Regarding Performance issue

Former Member — Mon, 09 Feb 2009 07:26:31 GMT

Hi ,

Create 2 ranges for remaining 2 fields in program

Use these 2 ranges without any values in selece statement.

Select ZX

FROM TABLE ZSDR

INTO TABLE i_zsdr

FOR ALL ENTRIES IN i_ZTABLE

WHERE F1 = i_ztable-f1

AND f2 IN s_f2

AND f3 IN s_f3

and f4 in r_f4

and f5 in r_f5.

Kiran

Re: Regarding Performance issue

Former Member — Mon, 09 Feb 2009 07:33:42 GMT

Hi,

You can create secondary index with the three fields which you are using in select clause in the same sequence.It will increase the performance .

You can make the ZY table full Buffering .

Regards

Pinaki

Edited by: Pinaki Mukherjee on Feb 9, 2009 9:30 AM

Edited by: Pinaki Mukherjee on Feb 9, 2009 9:31 AM

Re: Regarding Performance issue

dev_parbutteea — Mon, 09 Feb 2009 08:28:17 GMT

Hi,

i_temp[ ] = i_ZTABLE[ ].

sort i_temp by f1.

delete adjacent duplicates from i_temp comparing f1.

Select ZX

FROM TABLE ZSDR

INTO TABLE i_zsdr

FOR ALL ENTRIES IN i_temp

WHERE F1 = i_temp-f1

AND f2 IN s_f2

AND f3 IN s_f3.

Regards.

Edited by: Dev Parbutteea on Feb 9, 2009 9:28 AM

Re: Regarding Performance issue

Former Member — Mon, 09 Feb 2009 08:34:21 GMT

better u go for creation of secondary index with the 3 fields, that ur using in ur select query WHERE clause.

Re: Regarding Performance issue

Former Member — Mon, 09 Feb 2009 09:08:07 GMT

quite a lot of nonsense ...

What is lakhs ... it is not real English, is it? I guess it is 100.000, such a table can not be buffered !!!!

The number of fields in an index is quite irrelevant, index f1, f2, f3, f4, f5 and f1 , f2 f3 are identical

if only the three fields are used.

But what is actually used, are there entries, in there f2 and f3 ranges?

What is the selectivity of f1?

This information ' i_ztable i have 80,000 records' is o.k., but how many records come back?????

That would the relevant information.

Siegfried

Re: Regarding Performance issue

Former Member — Mon, 09 Feb 2009 12:37:07 GMT

Hi,

If you realy want to increase performance try out:

1) Instead of two Select statements (For all entries) Use Joins

2) View is the best in performance create a view from all the fields and use it in the program

as normal structure as table.(Views can be buffered).

Use the second Option it is the best.

Regards,

Gurpreet

Re: Regarding Performance issue

ThomasZloch — Mon, 09 Feb 2009 15:50:35 GMT


> 1) Instead of two Select statements (For all entries) Use Joins

Never thought I'd live to read this

Re: Regarding Performance issue

Former Member — Tue, 10 Feb 2009 10:25:37 GMT

unfortunately it does not fit here !

We don't know whether there is another SELECT close to that you. I can be that i_ztable is the result of some manual input, of a long processing of data upload or whatever.

Siegfried

Re: Regarding Performance issue

Former Member — Tue, 10 Feb 2009 16:08:47 GMT

Sreehari,

I hope you have sorted the table i_ZTABLE on f1 f2 f3 or that matches to your where clause!!! Also did you try to see that extract all the records in itab and then delete unwanted based on your criterion. By all the records I mean by using as generic where clause as you can make.