Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

About performance of COPA table CE41000

pole_li
Active Participant
0 Kudos
819

Hi All,

In our production system, when creating account document with copa, it takes a little bit long time, so when we create billing in batch data, the program run for hours, but in the snapclone system of production(copy from production system, less about 4 months data) we do the same data range of billing generation test, it run fast(1/10 time of production).

I debug the program and found that in COPA post routine,there is a SQL:


SELECT SINGLE * FROM CE41000                      "#EC *
      WHERE AKTBO = X_S_CE4-AKTBO
        AND KNDNR = X_S_CE4-KNDNR
        AND ARTNR = X_S_CE4-ARTNR
        AND FKART = X_S_CE4-FKART
        AND KAUFN = X_S_CE4-KAUFN
        AND KDPOS = X_S_CE4-KDPOS
        AND AUFNR = X_S_CE4-AUFNR
        AND BUKRS = X_S_CE4-BUKRS
        AND KOKRS = X_S_CE4-KOKRS
        AND WERKS = X_S_CE4-WERKS
        AND GSBER = X_S_CE4-GSBER
        AND VKORG = X_S_CE4-VKORG
        AND VTWEG = X_S_CE4-VTWEG
        AND SPART = X_S_CE4-SPART
        AND PRCTR = X_S_CE4-PRCTR
        AND PPRCTR = X_S_CE4-PPRCTR
        AND KSTRG = X_S_CE4-KSTRG
        AND PSPNR = X_S_CE4-PSPNR
        AND MEINH = X_S_CE4-MEINH
        AND MATKL = X_S_CE4-MATKL
	AND WW001 = X_S_CE4-WW001
	AND WW002 = X_S_CE4-WW002
	AND WW003 = X_S_CE4-WW003
	AND MVGR2 = X_S_CE4-MVGR2
	AND MVGR3 = X_S_CE4-MVGR3
	AND MVGR4 = X_S_CE4-MVGR4
	AND MVGR5 = X_S_CE4-MVGR5
	AND PAPH1 = X_S_CE4-PAPH1
	AND PAPH2 = X_S_CE4-PAPH2
	AND PAPH3 = X_S_CE4-PAPH3
	AND PRODH = X_S_CE4-PRODH
	AND VKBUR = X_S_CE4-VKBUR
	AND VKGRP = X_S_CE4-VKGRP
	AND KVGR1 = X_S_CE4-KVGR1
	AND PLTYP = X_S_CE4-PLTYP
	AND MVGR1 = X_S_CE4-MVGR1
	AND WW004 = X_S_CE4-WW004
	AND PARTNER = X_S_CE4-PARTNER
	AND WW005 = X_S_CE4-WW005
	AND WW006 = X_S_CE4-WW006
	AND WW007 = X_S_CE4-WW007
	AND VV900_ME = X_S_CE4-VV900_ME
	AND VV901_ME = X_S_CE4-VV901_ME
	AND VV902_ME = X_S_CE4-VV902_ME
	AND ABSMG_ME = X_S_CE4-ABSMG_ME
	AND VV904_ME = X_S_CE4-VV904_ME
	AND VV905_ME = X_S_CE4-VV905_ME.

In Snapclone system, this sql run very fast, in PRD system, it takes almost 2 seconds, but we check the table indexs are same, and the statistices are up to date, the only big difference is that snapclone system has about 30% less data , may it

affect the performance so much? Or any other factor can cause the performance problem in PRD? thanks a lot!

Regards,

Pole

1 ACCEPTED SOLUTION

volker_borowski2
Active Contributor
0 Kudos
140

> In Snapclone system, this sql run very fast, in PRD system, it takes almost 2 seconds, but we check the table indexs are same, and the statistices are up to date, the only big difference is that snapclone system has about 30% less data , may it

> affect the performance so much? Or any other factor can cause the performance problem in PRD? thanks a lot!

>

> Regards,

> Pole

If statistics are "up to date" they might be indeed different in both systems, esp. if one system is smaller.

This might lead to different execution plans, even if the same indexes are available.

Esp. if you clone has two or three clients and PRD just one, the plan could be completely different

due to the num_distinct on MANDT column. In this case the clone-system will surely favor any indexes.

You need to sqltrace your statement and crosscheck the execution plan on both systems in ST04/ST05.

Second check: How many rows are returned in both systems?

Volker

2 REPLIES 2

volker_borowski2
Active Contributor
0 Kudos
141

> In Snapclone system, this sql run very fast, in PRD system, it takes almost 2 seconds, but we check the table indexs are same, and the statistices are up to date, the only big difference is that snapclone system has about 30% less data , may it

> affect the performance so much? Or any other factor can cause the performance problem in PRD? thanks a lot!

>

> Regards,

> Pole

If statistics are "up to date" they might be indeed different in both systems, esp. if one system is smaller.

This might lead to different execution plans, even if the same indexes are available.

Esp. if you clone has two or three clients and PRD just one, the plan could be completely different

due to the num_distinct on MANDT column. In this case the clone-system will surely favor any indexes.

You need to sqltrace your statement and crosscheck the execution plan on both systems in ST04/ST05.

Second check: How many rows are returned in both systems?

Volker

0 Kudos
140

Hi Volker,

Thanks for your reply.

I found that if field "PRCTR" has value, the SQL will run long time, if no, it's very fast, so I added the field to index 1, and the problem was solved.

Regards,

Pole