Regarding Performance issue

[Former Member]

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

ZY

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

[Former Member]

Hi ,

Create 2 ranges for remaining 2 fields in program

Use these 2 ranges without any values in selece statement.

Select ZX

ZY

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

[Former Member]

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

[dev_parbutteea]

Hi,

i_temp[ ] = i_ZTABLE[ ].

sort i_temp by f1.

delete adjacent duplicates from i_temp comparing f1.

Select ZX

ZY

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[ThomasZloch]

>

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

Never thought I'd live to read this

[Former Member]

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

[Former Member]

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.