Performance Tuning Using Index

[Former Member]

Hi,

I would like to discuss about the following code.In Below code S_Doc contains 200 Ranges.

-

---

SELECT blart opbel INTO TABLE tb_dfkkop_bloc

FROM dfkkop

WHERE opbel in s_doc and augbl = space.

-

---

Time taken for this query is <b>4 sec</b>. so i rewritten the query like below

-

---

SELECT blart opbel INTO TABLE tb_dfkkop_bloc

FROM dfkkop WHERE augbl = space.

-

---

since there is a index on AUGBL, Query took less time as <b>1 Sec</b>.

since only fetching is done here i have used delete statement to filter the data as desired.

delete tb_dfkkop_bloc where opbel not in s_doc

Then it was taking more time than the first query as 5 Sec.

Since the data fetching is done in a good manner, can we approch this method for a huge table?

[Former Member · Accepted solution]

Hi Lijo,

I guess not operator is the performance constraint in this case..

Try filtering the data using EQ and put the contents in another internal table..

Please let me know the result..

Regards,

Tanveer.

Please mark helpful answers

[Former Member]

Hi Lijo,

You are right to consider the fact that the load on the DB should be minimum..

The second approach would be better approach in case of database that huge.

Regards,

Tanveer.

Please mark helpful answers.

[former_member188685]

Hi,

Yes you can use that approach if it is Huge table, since you have an index for AUGBL , it will fetch the data fast.after that you can filter using selection .

what ever you are doing is fine.

Regards

vijay

[Former Member]

Thank you friends.

Vijay,I would like to explain it.OPBEL is a field from primary key.

<b>Case 1.</b>

WHERE augbl = space and opbel in s_doc.

Time Taken :- 2 Sec

<b>Case 2.</b>

WHERE augbl = space.

Time Taken :- < 1Sec

<b>Case 3.</b>

WHERE augbl = space and opbel in s_doc.

DELETE tb_dfkkop_bloc WHERE opbel NOT IN s_doc.

Time Taken :- 3 Sec

This table contain around 870 Million data. I am trying to reduce the load on DB.So will it be good if i do so?

Thanks

Lijo Joseph

[Former Member]

Hi Lijo,

  Have you tried with first using OPBEL in s_DOc , then after that filtering AUGBL from internal table as i seeOPBEL is primary key in DFKKOP.

SELECT blart opbel INTO TABLE tb_dfkkop_bloc
FROM dfkkop
WHERE opbel in s_doc.

delete table itab where augbl NE SPACE.

Message was edited by: Sekhar

[Former Member]

Hi Lijo

You could also try changing the order of your where clause in the original SQL so that it is:

WHERE augbl = space AND opbel in s_doc

If there is an index on augbl and not on opbel this should improve performance, you will also be extracting less data from the database. I believe it is always a good idea to order the statements in the where clause as they appear in the table's primary key or index.

Hope that helps

Andy

[Former Member]

Hello Lijo,

Always remember that database access is the biggest bottle neck in terms of performance, so if u are reducing the database access by doing a good fetch then the internal table processing can be made fast. Since the internal table prcocessing is contrained by memeory u can still enhance the performance.