topic Re: PERFORMANCE TUNINIG in Application Development and Automation Discussions

PERFORMANCE TUNINIG

Former Member — Thu, 26 Jul 2007 07:05:54 GMT

Hi,

Please ans this que

what is the role of secondary index in performance?

will join conditions in sql queries affect perfomance? how?

will sorted internal tables help in performance? how ?

thank you

ASHOK KUMAR

Re: PERFORMANCE TUNINIG

Former Member — Thu, 26 Jul 2007 07:07:45 GMT

hi Ashok

go to se11 for best performance tips follow

ENVIRONMENT->EXAMPLES->PERFORMANCE EXAMPLES

regards

ravish

plz rewawrd points if helpful

Re: PERFORMANCE TUNINIG

Former Member — Thu, 26 Jul 2007 07:10:35 GMT

Hi,

You create secondary index for a database table when you access the database records using fields that are not in the Primary key.

So you create a secondary index using the fields with whihc you query your database most.

That is the fields that you use most in the WHERE clause of the SELECT statement. if these fields are not in the Primary key or say not many or in the primary key then you create a secondary index using these fields.

In the database this index gets created and when ever you use these fields in the SELECT query's WHERE clause this INDEX will be used to fetch the data faster

Refer to this related thread

Join - Used when the data in the table is less.

For all entries - Used when the data in the table is huge.

Refer /people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better

Sorted tables

This is the most appropriate type if you need a table which is sorted as you fill it. You fill sorted tables using the INSERT statement. Entries are inserted according to the sort sequence defined through the table key. Any illegal entries are recognized as soon as you try to add them to the table. The response time for key access is logarithmically proportional to the number of table entries, since the system always uses a binary search. Sorted tables are particularly useful for partially sequential processing in a LOOP if you specify the beginning of the table key in the WHERE condition.

REPORT ZREPORT_SORTED1.

DATA: BEGIN OF LINE,

COL1 TYPE I,

COL2 TYPE I,

END OF LINE.

DATA ITAB LIKE SORTED TABLE OF LINE WITH UNIQUE KEY COL1.

DO 4 TIMES.

LINE-COL1 = SY-INDEX.

LINE-COL2 = SY-INDEX ** 2.

INSERT LINE INTO TABLE ITAB.

ENDDO.

CLEAR LINE.

READ TABLE ITAB WITH TABLE KEY COL1 = 3

INTO LINE TRANSPORTING COL2.

WRITE: 'SY-SUBRC =', SY-SUBRC,

/ 'SY-TABIX =', SY-TABIX.

SKIP.

WRITE: / LINE-COL1, LINE-COL2.

The output is:

SY-SUBRC = 0

SY-TABIX = 3

0 9

REPORT ZREPORT_SORTED2.

DATA: BEGIN OF LINE,

COL1 TYPE I,

COL2 TYPE I,

END OF LINE.

DATA ITAB LIKE SORTED TABLE OF LINE WITH UNIQUE KEY COL1.

DO 4 TIMES.

LINE-COL1 = SY-INDEX.

LINE-COL2 = SY-INDEX ** 2.

INSERT LINE INTO TABLE ITAB.

ENDDO.

READ TABLE ITAB WITH KEY COL2 = 16 TRANSPORTING NO FIELDS.

WRITE: 'SY-SUBRC =', SY-SUBRC,

/ 'SY-TABIX =', SY-TABIX.

The output is:

SY-SUBRC = 0

SY-TABIX = 4

Regards

Re: PERFORMANCE TUNINIG

Former Member — Thu, 26 Jul 2007 07:15:25 GMT

Hi,

what is the role of secondary index in performance?

-- If the criteria field is not a primary key field then u should use secondary index. secondary indexes occupies more space but are very useful when serach field is not a key field,, if it is then it will serach using that index hence data fetch will be v fast.

will join conditions in sql queries affect perfomance? how?

-- if primary key fields are joined then there is no performacne issue at all , but if not key field are used then take more time to join tables. again if more than three field are used in join then it may affect performance.

will sorted internal tables help in performance? how ?

---yes, whille searching internal table key fields are access as per index...

Jogdand M B