BDCP2 is using ineffecient index.

[Former Member]

I am trying to put all the info in one screenshot here to capture everything in one place.  Pls excuse.

Problem Situation: Our production SAP system is on MSSQL. I hope you are aware that BDCP2 contains change pointers Processed/Unprocessed.  While trying to find change pointers tobe executed thru idoc, system utilized BDCP2 table.  When searching for unprocessed, it takes Estimated I/O of 12.xxx (as shows in the picture). Here is the SQL statement.

SELECT "MANDT" ,"MESTYPE" AS c ,"CPIDENT" AS c ,"PROCESS" AS c ,"TABNAME" AS c ,

"TABKEY" AS c ,"FLDNAME" AS c ,"CRETIME" AS c ,"ACTTIME" AS c ,"USRNAME" AS c ,

"CDOBJCL" AS c ,"CDOBJID" AS c ,"CDCHGNO" AS c ,"CDCHGID" AS c

FROM "BDCP2"

WHERE "MANDT" = @P1 AND "MESTYPE" = @P2 AND "PROCESS" = @P3

ORDER BY "MANDT" ,"CPIDENT"

/* R3:SAPLBD01:2047 T:BDCP2 M:010 */

In this where clause, fields MANDT,MESTYPE & PROCESS are in the order.  Based on this it should be using index BDCP2~001.  But system is using BDCP2~0 index.

Index BDCP2~0 which has fields

MANDT,

MESTYPE &

CPIDENT.

But there is another index BDCP2~001 that has fields

MANDT,

MESTYPE

PROCESS,

CRETIME &

CPIDENT

It would be more efficient if system uses BDCP2~001 as there are very few records that needs tobe processed in the system and it would reduce estimated IO cost.   Is there a way to get this done.  Please note that this is standard SAP program.

Thanks and Regards,

Giridhara Tadikonda.

[Former Member]

It seems thats how your database profile settings are, to do a full scan instead of using secondary index. Since its standard you can't even use database hints. Anyways have a look at Note 133381.

[Mohamed_Mukhtar]

Hi Giridhar,

Please check the quality of indices on BDCP2 table .

Ask your basis/DBA to update statistics and improve the indices quality .

Thanks

[Former Member]

Please note that this is standard SAP Program as mentioned in my original post.

Update stats don't help.  I see that it is retrieving 366k records and the Estimated I/O will be more or less same.even if I do full scan.  I did full scan in test system and little improvement.

  As I told if Mssql uses secondary index, then the number of records with secondary index are <1000 records and it should improve perfomance.

  I have seen the note 133381 and tried to use HINTs, but in ST05ACC, it is throwing syntax error.  this is the statement I have used.

SELECT "MANDT" ,"MESTYPE" AS c ,"CPIDENT" AS c ,"PROCESS" AS c ,"TABNAME" AS c ,

"TABKEY" AS c ,"FLDNAME" AS c ,"CRETIME" AS c ,"ACTTIME" AS c ,"USRNAME" AS c ,

"CDOBJCL" AS c ,"CDOBJID" AS c ,"CDCHGNO" AS c ,"CDCHGID" AS c

FROM "BDCP2"

WHERE "MANDT" = '010' AND "MESTYPE" = 'RS0011' AND "PROCESS" = ' '

ORDER BY "MANDT" ,"CPIDENT"

%_HINTS MSSQLNT ‘TABLE BDCP2 ABINDEX(001)‘

Error message in trace when I tried from ST05ACC -> Explain statement mentioned above:

C Tue Mar 22 15:57:15 2016

C  ERROR: -1 in function DoXxpl (SQLExecDirect) [line 21282]

C  (102) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'MSSQLNT'.

C  dbdsmss: DBSL99 SQL102

C  Incorrect syntax near 'MSSQLNT'.

B  ***LOG BY2=> sql error 102 performing OPC [dbds         398]

B  ***LOG BY0=> Incorrect syntax near 'MSSQLNT'. [dbds         398]

B  ***LOG BY1=> sql error 102 [dbacds       1823]

Any suggestions to let mssql use Hint at db level?

[RaymondGiuseppi]

Read 133381 - Database hints in Open SQL for MS SQL Server

%_HINTS MSSQLNT 'TABLE &TABLE& ABINDEX(001)'

Regards,

Raymond

[Former Member]

Same error even with &TABLE&.

[RaymondGiuseppi]

Are you really sure that index is active in database, and what is its name according to SE11 display?

[Former Member]

Yes. Index is existing in database.  I did full stats for this table and the estimated IO came down from 12.48 to 9.48 in TST environment.

[Former Member]

Not sure if your environment would show this, but you can simulate this via ST05ACC: "Enter SQL Statement" and give the below SQL: I have taken this SQL based on our environment. ================================================================== SELECT "MANDT" ,"MESTYPE" AS c ,"CPIDENT" AS c ,"PROCESS" AS c ,"TABNAME" AS c , "TABKEY" AS c ,"FLDNAME" AS c ,"CRETIME" AS c ,"ACTTIME" AS c ,"USRNAME" AS c , "CDOBJCL" AS c ,"CDOBJID" AS c ,"CDCHGNO" AS c ,"CDCHGID" AS c FROM "BDCP2" WHERE "MANDT" = '010' AND "MESTYPE" = 'RS0011' AND "PROCESS" = ' ' ORDER BY "MANDT" ,"CPIDENT" ================================================================== Explain; I could see extimated IO: 9.8143 Estimated Rows: 267836. but if I execute SE16 with table BDCP2 with the following criteria in selection: MSGTYPE: RS0011 Process: Exclude "X" then you see number of rows as 9185 (approximate). In my assumption, if system uses index 001, then the estimated IO cost would be negligible.

[Former Member]

I've the same problem on Oracle 12 DB.

This query

SELECT

  "MANDT", "MESTYPE", "CPIDENT", "PROCESS", "TABNAME", "TABKEY", "FLDNAME", "CRETIME", "ACTTIME",

  "USRNAME", "CDOBJCL", "CDOBJID", "CDCHGNO", "CDCHGID"

FROM

  "BDCP2"

WHERE

  "MANDT"='730' AND "MESTYPE"='YOILDEB' AND "PROCESS"=' ' AND "CRETIME"<='20160721203056' AND "CDOBJCL"=

  'CREDTEXT'

ORDER BY

  "MANDT", "CPIDENT";

use always BDCP2-0 instead of the right index BDCP2-001.

How can I solve?

Regards,

Mario

[Former Member]

Since the select statement is coming from program SAPLBD01, I have opened message with SAP and they provided SQL command to create index at (MSSQL) DB level.  It is not visible from SAP level.  I notice this is getting used now.  Even though the cost is high, it is less when compared to earlier.  Probably check from which program this is coming and open message to SAP accordingly.