Question:
Dear Folks,
I have a problem with the volume of the data in a Custom table. The data volume is crossing 6 lakhs.
when I try to retrieve the data from that table, it is taking a lot of time to
get the table entries. Can any one of you advise any tips so that I can
increase the performance in terms of retrieval speed?
Are there any technical settings to be changed or any indexes to be created...etc.. will there be any problem if I create an index of my choice?
I thank you forward to all of you...
Regards,
BSR
Answer:
What are the table keys ?
What is your select statement ?
What indexes have been created ?
_________________
Regards
R
Abap KC
SFMDR
Answer:
R,
Thanks for your response.
All the fields are character fields and (total 11 Key fields). But for the querying, we have only 4 fields available to use.
I am not sure whether the customer will accept to create indexes on these
four fields, but would like to know whether is there any other solution by
which we can improve the retrieval speed.
If you set the buffer ON, will there be any impact? And one more observation is, the table is created for to store only 1,20,000 entries (From the tech settings) but there are 6lakhs entries stored. Is this reason
causes any problem?
Need your inputs...
Thanks & Regards,
BSR
Answer:
You have not answer my questions.
I will ask them again:
What are the table keys ?
What is your select statement ?
What indexes have been created ?
And a new question.
What is a lakh ??
_________________
Regards
R
Abap KC
SFMDR
Answer:
R,
Thanks for your response.
All the fields are character fields and (total 11 Key fields). But for the querying, we have only 4 fields available to use.
I am not sure whether the customer will accept to create indexes on these
four fields, but would like to know whether is there any other solution by
which we can improve the retrieval speed.
If you set the buffer ON, will there be any impact? And one more observation is, the table is created for to store only 1,20,000 entries (From the tech settings) but there are 6lakhs entries stored. Is this reason
causes any problem?
Need your inputs...
Thanks & Regards,
BSR
Generally, you should not buffer a table of that size. However without knowing any detail (and the number of key field does not help) it is hardly possible to say if the performance could be improved.
Have you run a sql trace? What was the output?
Again: What does the select statement look like.
Are the key fields being used in consecutive order from the beginning of an index? What about the diversity of the fields?
Christian
Answer:
R,
Here are the answers for your questions.
What are the table keys ?
Total number of fields are 17 fields, out of which 11 fields are KEY Fields. they are:
MANDT
Field1 - Char(6)
Field2 - Char(7)
Field3 - Char(7)
Field4 - Char(1)
Field5 - Char(6)
Field6 - Char(3)
Field7 - Char(2)
Field8 - Char(3)
Field9 - Char(4)
Field10 - Char(6)
Field11 - Char(5)
What is your select statement ?
select (field..list)
into table itab2
from ZTEST
for all entries in itab1
where field2 = itab1-field2
and field4 = itab1-field4
and field5 = itab1-field5
and field8 = itab1-field8.
What indexes have been created ?
- No Indexes have been created so far
And a new question.
What is a lakh ??
= 1 / 10 of One million.
I hope the above information will be able to understand the problem
Regards,
BSR
Answer:
R,
Here are the answers for your questions.
What are the table keys ?
Total number of fields are 17 fields, out of which 11 fields are KEY Fields. they are:
MANDT
Field1 - Char(6)
Field2 - Char(7)
Field3 - Char(7)
Field4 - Char(1)
Field5 - Char(6)
Field6 - Char(3)
Field7 - Char(2)
Field8 - Char(3)
Field9 - Char(4)
Field10 - Char(6)
Field11 - Char(5)
What is your select statement ?
select (field..list)
into table itab2
from ZTEST
for all entries in itab1
where field2 = itab1-field2
and field4 = itab1-field4
and field5 = itab1-field5
and field8 = itab1-field8.
What indexes have been created ?
- No Indexes have been created so far
And a new question.
What is a lakh ??
= 1 / 10 of One million.
I hope the above information will be able to understand the problem
Regards,
BSR
Since field1 is not given in the where clause - the select cannot benefit from the primary index I expect a full index scan or a full table scan as a result. If you cannot specify field1 in your select statement you should consider a secondary index there.
There are plenty of discussions on For all entries in this forum. Depending on the database you will run into problems if there are many records in itab1. In any case make sure that itab1 is not empty before you use for all entries.
Table buffering does not make sense here.
Christian
Answer:
Defining a table with 11 key fields hardly ever makes sense.
Answer:
Add an index with the selected fields with the same order
field2 and field4 and field5 and field8 .
Answer:
How about create a new index which contains only 4 fields U use in where clause?
Nin
Answer:
Now.... You wouldn't of got those answers with the original information, and I'd agree with them all!
_________________
Regards
R
Abap KC
SFMDR
Answer:
Helooooooooooooooooooooooooooo
Run the Damn Explain Plan????????
Answer:
Helooooooooooooooooooooooooooo
Run the Damn Explain Plan????????
Not necessary in this case.
The problem is obvious by just looking at the source code snippets and the table definition.