Select Statement

[Former Member]

Hello

iam using a select statement in my report and its taking a long time to fetch the data and thus ending up giving a short dump..The code is as below..Could you please help with solving this problem..

<b>SELECT VBELV VBTYP_V INTO (ITAB_VBAK-VBELV, ITAB_VBAK-VBTYP_V)

FROM VBFA UP TO 1 ROWS

WHERE VBELN = ITAB_OPEN-DOCNO.

IF ITAB_VBAK-VBTYP_V <> 'C'.

SELECT SINGLE VBELV INTO (ITAB_VBAK-VBELV)

FROM VBFA "UP TO 1 ROWS

WHERE VBELN = ITAB_VBAK-VBELV.

• ENDSELECT. "select VBFA

ENDIF. "IF itab

SELECT VBELN BSTNK KUNNR INTO (ITAB_VBAK-VBELN, ITAB_VBAK-BSTNK,

ITAB_VBAK-SOLDTO)

FROM VBAK UP TO 1 ROWS

WHERE VBELN = ITAB_VBAK-VBELV.

IF SY-SUBRC = '0'.

MOVE ITAB_VBAK-VBELN TO ITAB_OPEN-ORDNO. "Order Number

MOVE ITAB_VBAK-BSTNK TO ITAB_OPEN-BSTNK. "Purchase Order

MOVE ITAB_VBAK-SOLDTO TO ITAB_OPEN-SOLDTO. "SoldTo

• Load first Benteler Material Number - PBE

MOVE ' ' TO ITAB_OPEN-MATNR.

SELECT MATNR INTO (ITAB_OPEN-MATNR)

FROM VBRP UP TO 1 ROWS

• where vbeln = itab_vbak-vbelv.

WHERE VBELN = ITAB_OPEN-DOCNO.

ENDSELECT.

PERFORM TEXT_RETRIEVAL."Get first line PO Text

IF SY-SUBRC = '0'.

LOOP AT ITAB.

MOVE ITAB-TDLINE TO ITAB_OPEN-TDLINE. "First Line of Text

EXIT.

ENDLOOP. "Loop at ITAB

ENDIF. "IF sy-subrc

ENDIF. "IF sy-subrc

ENDSELECT. "select VBAK

ENDSELECT. "select VBFA</b>

Thanks

Vicky

[Former Member]

Try SELECT SINGLE instead of using UP TO 1 ROWS.

[Former Member]

Select single vs select up to 1 rows shouldn't make much of a difference, except select single without specifying the entire key will show up on an extended program check.

Move your "IF SY-SUBRC = 0" after the ENDSELECT for starters. By the way, you don't need the single quote around the 0.

[christian_wohlfahrt]

Hi Vicky!

Would be nice to know, how you fill itab_open-docno. Because the document index VBFA should be used to search subsequent documents (having the first, e.g. VBAK, and searching the second, e.g. VBRK).

But the other way around is often much easier: in most documents the preceding document number is stored (e.g. VBRP-VGBEL and VBRP-VGPOS). Then direct access to VBAK is possible.

Currently you have a non-index access to VBFA, this will never get fast (select single or up to 1 rows won't change performance, just readability).

Regards,

Christian

[Former Member]

VBFA is a large table and you are selecting it without using the key fields. Since you know VBELN, I suggest you go to the original table to get the preceding document number.

Rob

[Former Member]

I never used the index in select statement..Cud anyone help with an example..

Thanks

Vicky

[Former Member]

Vicky - I think you're going about this the wrong way. Table VBFA is the sales document flow table. If you give it a preceding document (such as an order), you will get the subsequent documents (such as a delivery or debit memo request). If you try to do it the other way (get the preceding document from the subsequent document), you cannot use the index, because there are no secondary indexes on this table.

Having the flow work this way makes sense. If you have an order, it may have many deliveries and each delivery may have many accounting documents, so you cannot attach all of these subsequent documents directly to the order. So you need a table like VBFA to hold these relationships.

But if you have a subsequent document like a delivery item, it can be for only one sales order item, so the preceding document can be put directly on that item.

So with this in mind, I think you can do something like:

Using BSID-VBELN (the billing document for the accounting document) go to the billing table VBRP-VBELN and get the sales document VBRP-AUBEL.

Depending on the document type of this document, you may have to go to one or more other tables to get the remaining preceding documents.

This is quite a bit more complicated that going to VBFA, but I think it's the correct (SAP) way.

Rob

[Former Member]

hi,

The following points improves performance a lot.

1) Do not use SELECT .. ENDSELECT. Instead make of INTO TABLE ITAB.

2) Use select single ... instead of select..up to 1 rows.

Regards,

Sailaja.

[Former Member]

What type of document number is in ITAB_OPEN-DOCNO?

[Former Member]

BSID-VBELN

Thanks

Vicky

[Former Member]

So you are starting with the billing document and trying to work your way backwards? Have you looked at the reference document fields in VBRK and VBRP? You likely have much faster access working back through the corresponding order tables instead of using VBFA.

What previous documents are you trying to retrieve?

[Former Member]

%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'

what does spfli~001 mean in this statement

Thanks

Vicky

[Former Member]

Vicky - please check OSS notes 130480 and 129385.

Rob

[Former Member]

hi,

We can force the select to execute with the specified secondary index. It can be done using HINTS statement. But HINTS vary from one backend to other backend.

Here is an example where oracle is the backend system.

please refer to Note 129385 in OSS.

SELECT carrid connid cityfrom

FROM spfli INTO (xcarrid, xconnid, xcityfrom)

WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'

%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.

WRITE: / xcarrid, xconnid, xcityfrom.

ENDSELECT.

More than 4 fields should not be specified in secondary index. Creation of multiple secondary indexes increases the load on database.

You can search a table for data records that satisfy certain search criteria faster using an index. An index can be considered a copy of a database table that has been reduced to certain fields. This copy is always in sorted form. Sorting provides faster access to the data records of the table, for example using a binary search.

The index also contains a pointer to the corresponding

record of the actual table so that the fields not contained in the index can also be read. The primary index is distinguished from the secondary indexes of a table. The primary index contains the key fields of the table and a pointer to the non-key fields of the table. The primary index is created automatically when the table is created in the database.

You can also create further indexes on a table in the ABAP Dictionary. These are called secondary indexes. This is necessary if the table is frequently accessed in a way that does not take advantage of the sorting of the primary index for the access.

Points to keep in mind while creating indexes:

1) The order of the fields in the index is very important for the accessing speed.

2) The first fields

should be those which have constant values for a large number of selections. During selection, an index is only of use up to the first unspecified field.

3) Only those fields that significantly restrict the set of results in a selection make sense for an index.

4) Additional indexes can also place a load on the system since they must be adjusted each time the table contents change. Each additional index therefore slows down the insertion of records in the table. If two indexes on a table have a large number of common fields, this could make it more difficult for the optimizer to choose the most selective index.

5) A unique index for a client-dependent table must contain the client field.

6) Several indexes on the same table are distinguished by a three-place index identifier. The index identifier may only contain letters and digits. The ID 0 is reserved for the primary index. The index name on the database adheres to the convention <Table name>~<Index ID>.

Ex: TEST~A is the name of the corresponding database index in the database for table TEST and the secondary index with ID A.

Regards,

Sailaja.

[Former Member]

hi

SELECT VBELV VBTYP_V INTO (ITAB_VBAK-VBELV, ITAB_VBAK-VBTYP_V)
FROM VBFA UP TO 1 ROWS
WHERE VBELN = ITAB_OPEN-DOCNO.

<b>endselect.</b> " This is misssing