sql access for large table

[Former Member]

hi,

if a table has more than 6,000,000 records, any way to optimise to access so that can be faster.

i try not to use sql statement alot. i try to manipulate data in internal table but first time i also need to have select statement to copy to internal table.

any advice.

thanks

[Former Member]

Hi Eliana,

You should restrict maximum data selection in the SELECT query itself by providing proper WHERE clause conditions. Make sure that the indexes are used correctly then definately access will improve.

Regards,

Atish

[Former Member]

you can try access to it by primary key or index

[Former Member]

hi,

i created secondary index but the performance still the same.

just to confirm, after create the index and transported, i define the secondary index field at the where clause and the system should perform faster right? but my case no.

i check in se30 and the database table fetching time still not much difference.

thanks

[Former Member]

Hi Eliana,

Sometime you need to activate the index at DB level also. Just check with your basis for this if it is active at DB level or not.

Regards,

Atish

[Former Member]

Would you please post the code for your SELECT and also the key fields in the secondary index that you created.

Rob

[Former Member]

Tips

1)in select include all primary keys in where condition to fetch data

2)delare table without header line and with out occurs statement. and use work area to handle it

Ex:-

TYPES:BEGIN OF gty_kna1, " General Data in Customer Master

kunnr TYPE kna1-kunnr, " Payer Number

name1 TYPE kna1-name1, " Name1

telf1 TYPE kna1-telf1, " Communication

konzs TYPE kna1-konzs, " Corporate Group

END OF gty_kna1.

data:gs_kna1 TYPE gty_kna1, " General Data in Customer Master

gt_kna1 TYPE TABLE OF gty_kna1, " General Data in Customer Master

Note:

• In a SELECT statement, only the fields (field-list) which are needed are selected in the order that they reside on the database, thus network load is considerably less. The number of fields can be restricted in two ways using a field list in the SELECT clause of the statement or by using a view defined in ABAP/4 Dictionary. The usage of view has the advantage of better reusability.

• SELECT SINGLE is used instead of SELECT-ENDSELECT loop when the entire key is available. SELECT SINGLE requires one communication with the database system, whereas SELECT-ENDSELECT needs two

• Always specify the conditions in the WHERE-clause instead of checking them with check-statements, the database system can then use an index (if possible) and the network load is considerably less. You should not check the conditions with the CHECK statement because the contents of the whole table must be read from the database files into DBMS cache and transferred over the network. If the conditions are specified in the where clause DBMS reads exactly the needed data.

• Complex code is not embedded within a SELECT / ENDSELECT statement.

• No complex WHERE clauses, since complex where clauses are poison for the statement optimizer in any database system.

• For all frequently used SELECT statements, try to use an index. You always use an index if you specify (a generic part of) the index fields concatenated with logical ANDs in the Select statement's WHERE clause

• When loading data into Internal table, INTO TABLE OR APPENDING TABLE is used instead of a SELECT/APPEND combination. It is always faster to use the INTO TABLE version of a Select statement than to use APPEND statements.

• Use a select list with aggregate functions instead of checking and computing, when trying to find the maximum, minimum, sum and average value or the count of a database column.

Rewards if useful...............

Minal