DATABASE performance is very high

[vallamuthu_madheswaran2]

Hi,

I'm having the following code. its performance is very high. Is it possible to reduce the processing time?

SELECT objnr gjahr kstar beknz twaer

wtg001 wtg002 wtg003 wtg004 wtg005 wtg006

wtg007 wtg008 wtg009 wtg010 wtg011 wtg012

wkg001 wkg002 wkg003 wkg004 wkg005 wkg006

wkg007 wkg008 wkg009 wkg010 wkg011 wkg012

wog001 wog002 wog003 wog004 wog005 wog006

wog007 wog008 wog009 wog010 wog011 wog012

FROM cosp APPENDING TABLE i_cosp_t PACKAGE SIZE 2000

• FOR ALL ENTRIES IN p_i_vbakp

WHERE lednr EQ c_lednr_0 AND

versn EQ c_versn_0 AND

gjahr EQ p_gjahr AND

wrttp EQ c_wrttp AND

kstar IN p_r_kstar_cosp.

ENDSELECT.

• READ TABLE COSS AND APPEND TO P_I_COSP FILLED ABOVE

SELECT objnr gjahr kstar uspob beknz twaer

wtg001 wtg002 wtg003 wtg004 wtg005 wtg006

wtg007 wtg008 wtg009 wtg010 wtg011 wtg012

wkg001 wkg002 wkg003 wkg004 wkg005 wkg006

wkg007 wkg008 wkg009 wkg010 wkg011 wkg012

wog001 wog002 wog003 wog004 wog005 wog006

wog007 wog008 wog009 wog010 wog011 wog012

FROM coss APPENDING TABLE i_coss_t PACKAGE SIZE 2000

• FOR ALL ENTRIES IN p_i_vbakp

WHERE lednr EQ c_lednr_0 AND

versn EQ c_versn_0 AND

gjahr EQ p_gjahr AND

wrttp EQ c_wrttp AND

kstar IN p_r_kstar_coss.

ENDSELECT.

Thanks & Regards,

Vallamuthu.M

[Former Member]

The fields in the where condition of select statement should be in the same order as similar to data base table.

Thanks

[vallamuthu_madheswaran2]

One Field is differnt but now i changed it 1% time is reduce. it's helpfull for me is there any other way to reduce?

Thanks & Regards,

Vallamuthu.M

[Former Member]

>

> The fields in the where condition of select statement should be in the same order as similar to data base table.

This is a myth and incorrect.

Rob

[Former Member]

Hi,

Make sure that Cost Element range is sorted and lets try with increasing package size to 10000.

Thx

[Former Member]

Hi ,

Can't you avoid SELECT-ENDSELECT ? Try declaring the size of the internal table initially .

Regards,

Deepthi

[vallamuthu_madheswaran2]

The is no difference while increasing the package size.

Thanks & Regards,

Vallamuthu.M

[RaymondGiuseppi]

As you don't have the OBJNR key field, there is not much to do without building an index.

You could also try to find the OBJNR values associated to the cost centers (CSKS-OBJNR and CSSL-OBJNR) and select from COSS and COSP via OBJNR, PAOBJ or USPOB fields.

Regards,

Raymond

[vallamuthu_madheswaran2]

After getting the values we calculate the objnr

Thanks & Regards,

Vallamuthu.M

[Former Member]

your performance is not high, but very low, the requirement is high.

Please run an SQL trace and give the result of the SQL summary. Your problem is the support by the index, I guess.

[Former Member]

Moderator message - Please see before posting - thread locked

Rob

[Former Member]

> This is a myth and incorrect.

Yes, it is a myth, but it is still recommended. Performance is identical, but space used in cursor cache is different.

You should use the order of the table such that identical statement at different coding positions can be identified as identical and use only one statement in the cursor cache.

[Former Member]

>

> Yes, it is a myth, but it is still recommended. 

I'd like to see where this is recommended ands how much performance would be bettered.

Rob

[former_member188686]

Hi,

First of all create an index on table COSP - in the order

MANDT

LEDNR

GJAHR

WRTTP

VERSN

KSTAR

then use the following -

SELECT objnr gjahr kstar beknz twaer

wtg001 wtg002 wtg003 wtg004 wtg005 wtg006

wtg007 wtg008 wtg009 wtg010 wtg011 wtg012

wkg001 wkg002 wkg003 wkg004 wkg005 wkg006

wkg007 wkg008 wkg009 wkg010 wkg011 wkg012

wog001 wog002 wog003 wog004 wog005 wog006

wog007 wog008 wog009 wog010 wog011 wog012

FROM cosp Client specified APPENDING TABLE i_cosp_t PACKAGE SIZE 2000

• FOR ALL ENTRIES IN p_i_vbakp

WHERE MANDT = sy_mandt AND

lednr EQ c_lednr_0 AND

gjahr EQ p_gjahr AND wrttp EQ c_wrttp AND

versn EQ c_versn_0 AND kstar IN p_r_kstar_cosp.

ENDSELECT.

Then create an index on table COSS - in the order

MANDT

LEDNR

GJAHR

WRTTP

VERSN

KSTAR

• READ TABLE COSS AND APPEND TO P_I_COSP FILLED ABOVE

SELECT objnr gjahr kstar uspob beknz twaer

wtg001 wtg002 wtg003 wtg004 wtg005 wtg006

wtg007 wtg008 wtg009 wtg010 wtg011 wtg012

wkg001 wkg002 wkg003 wkg004 wkg005 wkg006

wkg007 wkg008 wkg009 wkg010 wkg011 wkg012

wog001 wog002 wog003 wog004 wog005 wog006

wog007 wog008 wog009 wog010 wog011 wog012

FROM coss APPENDING TABLE i_coss_t PACKAGE SIZE 2000

• FOR ALL ENTRIES IN p_i_vbakp

WHERE MANDT = sy-MANDT AND

lednr EQ c_lednr_0 AND gjahr EQ p_gjahr AND

wrttp EQ c_wrttp AND versn EQ c_versn_0 AND

kstar IN p_r_kstar_coss.

ENDSELECT.

And also if possible avoid

SELECT--ENDSELECT. Statement

Hope this will help you.

Regards

MADAN NAMDEO

[Former Member]

Hi Rob,

• OLTP like systems:

proportion of hard parse time to execution time of SQL high and with thousands of short transaction / sec: you will avoid hard parses of the statement due to the pinned statement in the sql cursor cache.

But I admit this would be mostly statements using bind variables and NOT of ad hoc type, so maybe the gain is not that high...

More often not to use bind variables is the killer in such systems and not the order of the statement.

• Data warehouse type:

proportion of hard parse time to execution time of SQL very low. Mostly (changing) literals and long running queries (i.e SAP BW cube) : not much to gain

Mostly not much to gain, but if you hit the right database type and the right access pattern it could be a benefit.

So the answer is : Yes, no, maybe.

Bye

yk

[Former Member]

OK - interesting. I stand corrected. I thought there could be absolutely no performance improvement.

But I think there may have been larger improvements in earlier releases and I think this may be why you often see this repeated.

Rob

[Rui_Dantas]

>

> OK - interesting. I stand corrected. I thought there could be absolutely no performance improvement.
> 
> But I think there may have been larger improvements in earlier releases and I think this may be why you often see this repeated.
> 
> Rob

Rob, just to avoid spreading the myth: putting the fields in the same order as in the table brings no performance improvement. What can bring some improvement is to make sure that the same select in different places is written the same way (so that the database can identify it is the same select). Writing the fields in the same order as in the table is just one convenient way to ensure that. Writing the fields in alphabetical order, for example, would be another way.

[Former Member]

no performance gain, that is what I already said.

> But I admit this would be mostly statements using bind variables and NOT of ad hoc type, so maybe the gain is not that high...

> More often not to use bind variables is the killer in such systems and not the order of the statement.

Bind variables are by default used by the Open SQL database interface !!!

[Former Member]

yes,yes - I left the SAP horizon because I did see other systems where this was not taken by heart.

Would be interesting how the Java environment could bring a SAP system down with not using binds!

what do you think?

bye

yk

[vallamuthu_madheswaran2]

solved: There is no way to solve this issue

[Former Member]

> solved: There is no way to solve this issue

nonsense there is always a way, you did not even really specify the problem