topic Re: DATABASE performance is very high in Application Development and Automation Discussions

DATABASE performance is very high

vallamuthu_madheswaran2 — Tue, 01 Dec 2009 05:31:54 GMT

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

Re: DATABASE performance is very high

Former Member — Tue, 01 Dec 2009 06:39:10 GMT

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

Thanks

Re: DATABASE performance is very high

vallamuthu_madheswaran2 — Tue, 01 Dec 2009 06:49:19 GMT

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

Re: DATABASE performance is very high

Former Member — Tue, 01 Dec 2009 07:40:06 GMT

Hi,

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

Thx

Re: DATABASE performance is very high

Former Member — Tue, 01 Dec 2009 08:47:54 GMT

Hi ,

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

Regards,

Deepthi

Re: DATABASE performance is very high

vallamuthu_madheswaran2 — Tue, 01 Dec 2009 08:56:19 GMT

The is no difference while increasing the package size.

Thanks & Regards,

Vallamuthu.M

Re: DATABASE performance is very high

RaymondGiuseppi — Tue, 01 Dec 2009 09:07:22 GMT

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

Re: DATABASE performance is very high

vallamuthu_madheswaran2 — Tue, 01 Dec 2009 09:53:12 GMT

After getting the values we calculate the objnr

Thanks & Regards,

Vallamuthu.M

Re: DATABASE performance is very high

Former Member — Tue, 01 Dec 2009 11:44:13 GMT

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.

Re: DATABASE performance is very high

Former Member — Tue, 01 Dec 2009 14:09:28 GMT


> 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

Re: DATABASE performance is very high

Former Member — Tue, 01 Dec 2009 14:10:10 GMT

Moderator message - Please see before posting - thread locked

Rob

Re: DATABASE performance is very high

Former Member — Tue, 01 Dec 2009 15:36:03 GMT

> 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.

Re: DATABASE performance is very high

Former Member — Tue, 01 Dec 2009 15:46:17 GMT


> 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

Re: DATABASE performance is very high

former_member188686 — Wed, 02 Dec 2009 05:26:15 GMT

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

Re: DATABASE performance is very high

Former Member — Wed, 02 Dec 2009 08:02:44 GMT

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

Re: DATABASE performance is very high

Former Member — Wed, 02 Dec 2009 14:07:38 GMT

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

Re: DATABASE performance is very high

Rui_Dantas — Wed, 02 Dec 2009 14:20:21 GMT


> 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.

Re: DATABASE performance is very high

Former Member — Wed, 02 Dec 2009 14:49:44 GMT

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 !!!

Re: DATABASE performance is very high

Former Member — Thu, 03 Dec 2009 08:04:10 GMT

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

Re: DATABASE performance is very high

vallamuthu_madheswaran2 — Mon, 07 Dec 2009 11:15:40 GMT

solved: There is no way to solve this issue