Inclusion of MANDT in the SQL statement

[Former Member]

Hi,

My apologies if this is the incorrect forum to post this question in. Please feel free to direct me towards the correct one if needed. I am not an ABAP(er), so please bear with me.

My question is as follows-

What decides the inclusion of Mandt in the Where clause of the SQL Statement even if it is not specified in the ABAP code ? My problem is that MANDT makes the index very unselective.

Here is an example

ABAP Code :

Open cursor with hold wa_dbcur for

SELECT matnr bwkey lbkum salk3 verpr vksal BKLAS

FROM mbew

WHERE matnr in smatnr

AND bwkey in sbwkey

AND LBKUM ne 0.

do.

fetch next cursor wa_dbcur into corresponding fields of table it_collect

package size 10000.

if sy-subrc <> 0.

exit.

endif.

SQL statement :

SELECT

"MATNR" , "BWKEY" , "LBKUM" , "SALK3" , "VERPR" , "VKSAL" , "BKLAS"

FROM

"MBEW"

WHERE

"MANDT" = :A0 AND "MATNR" BETWEEN :A1 AND :A2 AND "BWKEY" BETWEEN :A3 AND :A4 AND "LBKUM" <> :A5

Also, is there a way to avoid this ? If there isn't a way to avoid this, what would be a workaround.

Thank you everyone in advance.

Kunal

[ThomasZloch]

Well, the MANDT field is always automatically added to your SQL query by the database interface (translating ABAP Open SQL to DBMS Native SQL), you can avoid it by adding CLIENT SPECIFIED to the ABAP statement, which is not recommended most of the time.

What problem do you have with the statement, is it too slow?

Did you get the SQL statement from ST05 trace? Then can also post the execution plan here to allow further analysis.

Thomas

[Former Member]

Thank you for the prompt response. Yes, the report takes too long to finish and spends 98% of the total time executing only 1 statement; the one mentioned above. I'm sure runtime can be improved significantly by tuning just one statement.

The execution plan of the statement is given below

SELECT STATEMENT ( Estimated Costs = 244 , Estimated #Rows = 200 )

3 FILTER

Filter Predicates

2 TABLE ACCESS BY INDEX ROWID MBEW

( Estim. Costs = 244 , Estim. #Rows = 200 )

Estim. CPU-Costs = 9,153,792 Estim. IO-Costs = 243

Filter Predicates

1 INDEX RANGE SCAN MBEW~0

( Estim. Costs = 187 , Estim. #Rows = 2,352 )

Search Columns: 3

Estim. CPU-Costs = 7,737,292 Estim. IO-Costs = 186

Access Predicates Filter Predicates

I created an index with 2 fields as given below.

Column Name #Distinct

KALN1 33,452,767

MATNR 527,177

And another one with MATNR and BWKEY which I haven't transported to Prod yet, but tested it in QA. The program uses only the Primary Index and not any of these. I'm not sure why though. Stats were collected on both MBEW and the indices individually.

Kunal

[ThomasZloch]

I think you can delete your two indexes again:

MATNR and BWKEY is the primary key minus the MANDT field, but MANDT is always filled during selection, so no gain here.

There is alreday index MBEW~ML1 with MANDT and KALN1, KALN1 seems very selective, so your own KALN1 and MATNR does not add value either.

The question is how your selection range SMATNR is filled during selection, the narrower the selection is, the faster the query will run.

Thomas

[Rui_Dantas]

Hello,

What do you have in smatnr and in sbwkey?

Regards,

Rui Dantas

[Former Member]

There are a lot of tools you can use to determine the problem:

Rob

[Former Member]

Thank you for the responses. It seems I will need the help of an ABAP Programmer to get this done. I was trying to fill in. I will keep this question open for another day or two. Just being optimistic

Thank you everyone for your inputs.

Kunal

[former_member195402]

Hi Kunal,

if you create own indexes, you should ever select MANDT as first index key field, if the database table has a primary key with MANDT (or another client field) as first key field!

Regards,

Klaus

[Rui_Dantas]

Hi.

>

> I will keep this question open for another day or two. Just being optimistic 

Hi. Being optimistic? Is there something still open you need help? Have you checked what is in smatnr and in sbwkey?

>

if you create own indexes, you should ever select MANDT as first index key field, if the database table has a primary key with MANDT (or another client field) as first key field!

Did you mean you should always or you should never?

(just checking what you meant, but anyway I would disagree with both)

Rui Dantas

[Former Member]

Rui,

I appreciate your help in this matter. To be honest, I did not follow your suggestion ("what is in smatnr and in sbwkey") and did not want to waste any more time asking for answers to simple straightforward questions. Besides, I don't see anything wrong with keeping the question open for another day or two.

Thank you to everyone again for your valuable inputs.

Kunal

[Former Member]

>

>("what is in smatnr and in sbwkey") 

Since this is likely the cause of the problem, you really ought to look at it.

Rob

[former_member194613]

>To be honest, I did not follow your suggestion ("what is in smatnr and in sbwkey") and did not want to waste any more time asking >for answers to simple straightforward questions. Besides, I don't see anything wrong with keeping the question open for another day

> or two.

I think he does not really want support, so better close this one!

[Former Member]

>

> I think he does not really want support, so better close this one!

That's why we have the sticky.

Rob