Getting performance issues in searching DB table

[Former Member]

Hi Experts,

We are facing performance issues in ECC box when I try to save any entity like customer. Now just to brief our implementation. When we enter customer details in transaction XD01 and click save then we perform address validation and duplicate detection logic (Both customized) gets called. Now issue comes when duplicate detection logic gets triggered and we get pop-up showing duplicates of entered address. This duplicate detection is happening based on matchkey logic (Matchkey we generate from our product and save for all type of addresses in SAP). We have one custom table where we save these matchkey with address numbers and person numbers.

We had query to fetch records based on matchkey written like:

   SELECT *
     INTO TABLE lt_matchkey_tmp
     FROM xyz/matchkey

     WHERE ( matchkey LIKE v_searchkey_1
         OR matchkey LIKE v_searchkey_2
         OR matchkey LIKE v_searchkey_3
         OR matchkey LIKE v_searchkey_4
         OR matchkey LIKE v_searchkey_5
         OR matchkey LIKE v_searchkey_6 ).

Later to improve performance we got couple of suggestions from DBA team like:

Option 1:

SELECT * FROM "XYZ/MATCHKEY" a WHERE  "CLIENT"=:A0 AND  "MATCHKEY" LIKE :A1

UNION

SELECT * FROM "XYZ/MATCHKEY" a WHERE  "CLIENT"=:A0 AND "MATCHKEY"  LIKE :A2

UNION

SELECT * FROM "XYZ/MATCHKEY" a WHERE  "CLIENT"=:A0 AND "MATCHKEY"  LIKE :A3

UNION

SELECT * FROM "XYZ/MATCHKEY" a WHERE  "CLIENT"=:A0 AND "MATCHKEY" LIKE :A4

UNION

SELECT * FROM "XYZ/MATCHKEY" a WHERE  "CLIENT"=:A0 AND "MATCHKEY" LIKE :A5

UNION

SELECT * FROM "XYZ/MATCHKEY" a WHERE "CLIENT"=:A0 AND "MATCHKEY" LIKE :A6

Option 2:

SELECT /*+ index (a "XYZ/MATCHKEY~1") */  * FROM

sapsr3."XYZ/MATCHKEY" a

WHERE

  "CLIENT"=:A0 AND (

  "MATCHKEY" LIKE :A1

  OR "MATCHKEY"  LIKE :A2

  OR "MATCHKEY"  LIKE :A3

  OR "MATCHKEY" LIKE :A4

  OR "MATCHKEY" LIKE :A5

  OR "MATCHKEY" LIKE :A6

  )

After that I read many blogs and found that Unions are not possible in Open SQL query so I tried option 1 as :

SELECT * INTO TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND MATCHKEY LIKE v_searchkey_1.

SELECT * APPENDING TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND MATCHKEY LIKE v_searchkey_2.

SELECT * APPENDING TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND MATCHKEY LIKE v_searchkey_3.

SELECT * APPENDING TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND MATCHKEY LIKE v_searchkey_4.

SELECT * APPENDING TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND MATCHKEY LIKE v_searchkey_5.

SELECT * APPENDING TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND MATCHKEY LIKE v_searchkey_6.

And Option 2 as :

SELECT * INTO TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND (
MATCHKEY LIKE v_searchkey_1 or
MATCHKEY LIKE v_searchkey_2 or
MATCHKEY LIKE v_searchkey_3 or
MATCHKEY LIKE v_searchkey_4 or
MATCHKEY LIKE v_searchkey_5 or
MATCHKEY LIKE v_searchkey_6 ).

After doing all above steps also we noticed very small difference in performance.

This matchkey table is gets update for all customers, vendors and BPs and right now we have 2M records in it. Now the question is whether we should use index or not?

As of now we have index for field matchkey (Non-unique field which contains special characters as well for search Algorithm).

Please let me know if I can try any other option to improve performance here?

Thanks in advance!

-Akanksha

[Former Member]

Hi Experts,

We are facing performance issues in ECC box when I try to save any entity like customer. Now just to brief our implementation. When we enter customer details in transaction XD01 and click save then we perform address validation and duplicate detection logic (Both customized) gets called. Now issue comes when duplicate detection logic gets triggered and we get pop-up showing duplicates of entered address. This duplicate detection is happening based on matchkey logic (Matchkey we generate from our product and save for all type of addresses in SAP). We have one custom table where we save these matchkey with address numbers and person numbers.

We had query to fetch records based on matchkey written like:

   SELECT *
     INTO TABLE lt_matchkey_tmp
     FROM xyz/matchkey

     WHERE ( matchkey LIKE v_searchkey_1
         OR matchkey LIKE v_searchkey_2
         OR matchkey LIKE v_searchkey_3
         OR matchkey LIKE v_searchkey_4
         OR matchkey LIKE v_searchkey_5
         OR matchkey LIKE v_searchkey_6 ).

Later to improve performance we got couple of suggestions from DBA team like:

Option 1:

SELECT * FROM "XYZ/MATCHKEY" a WHERE  "CLIENT"=:A0 AND  "MATCHKEY" LIKE :A1

UNION

SELECT * FROM "XYZ/MATCHKEY" a WHERE  "CLIENT"=:A0 AND "MATCHKEY"  LIKE :A2

UNION

SELECT * FROM "XYZ/MATCHKEY" a WHERE  "CLIENT"=:A0 AND "MATCHKEY"  LIKE :A3

UNION

SELECT * FROM "XYZ/MATCHKEY" a WHERE  "CLIENT"=:A0 AND "MATCHKEY" LIKE :A4

UNION

SELECT * FROM "XYZ/MATCHKEY" a WHERE  "CLIENT"=:A0 AND "MATCHKEY" LIKE :A5

UNION

SELECT * FROM "XYZ/MATCHKEY" a WHERE "CLIENT"=:A0 AND "MATCHKEY" LIKE :A6

Option 2:

SELECT /*+ index (a "XYZ/MATCHKEY~1") */  * FROM

sapsr3."XYZ/MATCHKEY" a

WHERE

  "CLIENT"=:A0 AND (

  "MATCHKEY" LIKE :A1

  OR "MATCHKEY"  LIKE :A2

  OR "MATCHKEY"  LIKE :A3

  OR "MATCHKEY" LIKE :A4

  OR "MATCHKEY" LIKE :A5

  OR "MATCHKEY" LIKE :A6

  )

After that I read many blogs and found that Unions are not possible in Open SQL query so I tried option 1 as :

SELECT * INTO TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND MATCHKEY LIKE v_searchkey_1.

SELECT * APPENDING TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND MATCHKEY LIKE v_searchkey_2.

SELECT * APPENDING TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND MATCHKEY LIKE v_searchkey_3.

SELECT * APPENDING TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND MATCHKEY LIKE v_searchkey_4.

SELECT * APPENDING TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND MATCHKEY LIKE v_searchkey_5.

SELECT * APPENDING TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND MATCHKEY LIKE v_searchkey_6.

And Option 2 as :

SELECT * INTO TABLE lt_matchkey_tmp FROM XYZ/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt AND (
MATCHKEY LIKE v_searchkey_1 or
MATCHKEY LIKE v_searchkey_2 or
MATCHKEY LIKE v_searchkey_3 or
MATCHKEY LIKE v_searchkey_4 or
MATCHKEY LIKE v_searchkey_5 or
MATCHKEY LIKE v_searchkey_6 ).

After doing all above steps also we noticed very small difference in performance.

This matchkey table is gets update for all customers, vendors and BPs and right now we have 2M records in it. Now the question is whether we should use index or not?

As of now we have index for field matchkey (Non-unique field which contains special characters as well for search Algorithm).

Please let me know if I can try any other option to improve performance here?

Thanks in advance!

-Akanksha

[ThangaPrakash]

Hello Akanksha,

Try options like creating index or CURSOR or PACKAGE SIZE as like I have mentioned in the below thread.

Regards,

TP

[Former Member]

Hi Thanga,

I tried index and package size both options but did not find any performance improvements.

Just a query, Cursor thing is really going to help with performance improvements? Let me know is yes I will try that as well.

Please let me know if I can try anything else apart from these options.

Thanks!

Akanksha

[Former Member]

Hi Experts,

I tried deleting index to check performance and found only 3-4 seconds improvement. No I created secondary index again into the table and activated that but still getting below message:

Please let me know how I can work on solving this.

Looking for help to resolve performance of this table query. @Thomas Zloch @Adam Krawczyk

Thanks in advance.

-Akanksha

[adam_krawczyk1]

Hi Akanksha,

1. Regarding performance: do you need LIKE statement or can you replace it to EQ (=)? See hint from Suresh Kutam.

  SELECT *
     INTO TABLE lt_matchkey_tmp
     FROM xyz/matchkey

     WHERE ( matchkey = v_searchkey_1
         OR matchkey = v_searchkey_2
         OR matchkey = v_searchkey_3
         OR matchkey = v_searchkey_4
         OR matchkey = v_searchkey_5
         OR matchkey = v_searchkey_6 ).

If you have database index on the matchkey, this will be fast enough.

The problem with your statement is probably that you are using "LIKE" which can be more than just one record, so it takes time to compare all values. The index helps here , but if you change LIKE to EQUAL sign then you would benefit much more.

Or course replacing LIKE by EQ will change your functionality (you search for exactly matched values instead of prefix / suffix searching which LIKE statement offers.

2. Regarding your error "Index does not exists", go to SE11, enter table name, go to Utilities -> Database Object -> Database Utility and press the button "Activate and adjust database". This should do synchronization between ABAP table definition and database table definition (I hope it adjusts indexes too).

Regards,

Adam

[suresh_kutam]

Hi Akanksha,

Could you please change your select statement as below

Current select ::-

   SELECT *
     INTO TABLE lt_matchkey_tmp
     FROM xyz/matchkey

     WHERE ( matchkey LIKE v_searchkey_1
         OR matchkey LIKE v_searchkey_2
         OR matchkey LIKE v_searchkey_3
         OR matchkey LIKE v_searchkey_4
         OR matchkey LIKE v_searchkey_5
         OR matchkey LIKE v_searchkey_6 ).

Change to ::-

   SELECT *
     INTO TABLE lt_matchkey_tmp
     FROM xyz/matchkey
     WHERE matchkey in R_searchkey.

Build all your date in Range table T_SEARCHKEY.

Using individual OR statement will reduce the data base performance.

Hence Create a range table and polulate all u seatch key as IN

like

I EQ v_searchkey_1

I EQ v_searchkey_2

I EQ v_searchkey_3

I EQ v_searchkey_4

I EQ v_searchkey_5

I EQ v_searchkey_6

[Former Member]

Hello Everyone,

Issue has been resolved. We changed the query to:

SELECT * INTO TABLE lt_matchkey_tmp FROM /HSGRP1/MATCHKEY CLIENT SPECIFIED WHERE CLIENT = sy-mandt and (

                                                                                    MATCHKEY LIKE v_searchkey_1 or

                                                                                    MATCHKEY LIKE v_searchkey_2 or

                                                                                     MATCHKEY LIKE v_searchkey_3 or

                                                                                     MATCHKEY LIKE v_searchkey_4 or

                                                                                     MATCHKEY LIKE v_searchkey_5 or

                                                                                     MATCHKEY LIKE v_searchkey_6 ).

It improved performance on customer's box.

Thanks Everyone for your inputs.

Regards

Akanksha