12-18-2006 8:57 AM
Hi,
I have a select statement as shown below.
SELECT * FROM BSEG INTO TABLE ITAB_BSEG
WHERE BUKRS = CO_CODE
AND BELNR IN W_DOCNO
AND GJAHR = THISYEAR
AND AUGBL NE SPACE.
This select statement runs fine in all of R/3 systems except for 1. The problem that is shown with this particular system is that the query takes very long ( up to an hour for if W_DOCNO consists of 5 entries). Sometimes, before the query can complete, an ABAP runtime error is encountered as shown below:
<b>Database error text........: "ORA-01555: snapshot too old: rollback segment
number 7 with name "PRS_5" too small?"
Internal call code.........: "[RSQL/FTCH/BSEG ]"
Please check the entries in the system log (Transaction SM21). </b>
Please help me on this issue. However, do not give me suggestions about selecting from a smaller table (bsik, bsak) as my situation does not permit it.
I will reward points.
12-18-2006 9:02 AM
Hi ,
First and foremost please check whether you want all the fileds from the table BSEG , because it has very large number of fields.
Regards
Arun
12-18-2006 9:03 AM
Hi,
This happens as BSEG is a Cluster table. So you need to take care whenever querying on a cluster table. Check if you can give some more where conditions in your select statement.
12-18-2006 9:05 AM
i think your itab does not contain all the fields of bseg where as you are using select * and another thing use corresponding fields of table itab.
regards
shiba dutta
12-18-2006 9:07 AM
my itab is
DATA: ITAB_BSEG LIKE BSEG OCCURS 0 WITH HEADER LINE.
so this is not a problem.
12-18-2006 9:08 AM
then use
SELECT * FROM BSEG <b>into corresponding fields of table ITAB_BSEG</b>
WHERE BUKRS = CO_CODE
AND BELNR IN W_DOCNO
AND GJAHR = THISYEAR
AND AUGBL NE SPACE.
regards
shiba dutta
12-18-2006 9:17 AM
Does including "corresponding fields" makes a difference since
I am selecting all the columns anyway ?
12-18-2006 9:10 AM
dont use select * ....
instead u declare ur itab with the required fields and then in select refer to the fields in the select .
data : begin of itab,
f1
f2
f3
f4
....
end of itab.
select f1 f2 f3 f4 ..
into table itab
from bseg where ...
. this improves the performance .
select * is not advised .
regards,
vijay
12-18-2006 9:19 AM
as per your internal table declaration it does not need that but you can use and try with that.
regards
shiba dutta
12-18-2006 9:29 AM
Hi Yupinto,
Following Points may help you a lot
1. I think you should select the fields which are required instead of selecting all the fields 'SELECT *'
2. if you are not processing the too much with that table you can use select and endselect. it can avoid your short-dump
3. if possible increase the number of where conditions
Regards
Dinesh
12-19-2006 3:30 AM
If I removed the NE condition
AND AUGBL NE SPACE.
will it help to speed up the query ?
12-19-2006 3:35 AM
Hi ,
Yes it wil improve the performance a bit , but not drastically , it is generally advisable not to use negetive conditions in the select statement.
But to have improvement in the statement what i would suggest is that please select only those fields which are actually required in the program and not all fileds using select *.
If it is possible to modify it that way then it will surely improve the performace.
Regards
Arun
12-18-2006 11:05 AM
use only required fields instead of all fields...
avoid into correponding..instead use the fields as same order as database table.
avoid tables option at the top of the report..
i think these should increase u r performance..
12-19-2006 3:44 AM
hi,
Select the fields which are needed for you for the data retrieval instead of selecting all the values which will decrease the performance.
Declare your internal table with the fields u require in BSEG or else use INTO corresponding fields while giving the select Query.
This will improve the performance.
Also avoid using negative conditions in select statements.
-Priyanka.
12-19-2006 5:33 AM