topic Re: SQL problem in Application Development and Automation Discussions

SQL problem

Former Member — Wed, 12 Mar 2008 07:14:56 GMT

Hi all,

This is with ref to SE30 results what i found for my program is that the below SQL statement which is as below takes 61% of the database load,any suggestions to overcome this load,

Useful suggestions rewarded,

Rgrds.

select werks baugr matnr lgort pspel rsnum rspos bdter bdmng enmng meins aufnr bdart dbskz kzaus schgt techs cuobj umwrk postp plnum kdauf kdpos sobkz shkzg nfpkz nafkz nomng nomat

into table i_mdrs

from mdrs

where matnr <> space

and werks in s_werks

and xloek = space "Not deleted

and kzear = space "Not final issued

and bdter <= p_seldt

and sobkz in r_sobkz "Plant, project,sales order

and bdmng > 0

and dbskz <> c_dbskzf "Internal production

and ndisp = space "Relevant to MRP

and ( kzvbr <> c_kzvbrv "Direct consumption

and kzvbr <> c_kzvbra ) "Consumption to asset

and txtps = space "Ignore text items

and dumps = space "Ignore phantoms

and vrpla = space "Ign planning w/o final assy

and postp in r_t418 "Ignore direct procurement

Oracle hints option was added to improve performance

%_hints oracle 'FULL( MDRS ) PARALLEL( MDRS, 4 )'.

Re: SQL problem

Former Member — Wed, 12 Mar 2008 08:18:59 GMT

u use the table RESB directly, and in where condition

fields order should be in the order of fields in the data base table ,

This might help u .

Thanks

Re: SQL problem

Former Member — Wed, 12 Mar 2008 10:15:26 GMT

Hi,

Remove negative condition(not equal to).

better,Create index for near of your requierment for the table RESB.

L.Velu

Re: SQL problem

Former Member — Wed, 12 Mar 2008 11:16:17 GMT

Run the SQL Trace and read the explain

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

I guess your statement does not use any index.

Either you add fields to the Where condition which are an index.

Or you need to create an index. But for a new index you should ask somebody with more experience.

61% is onyl a relative number, what is it in total?

Siegfried

Re: SQL problem

Former Member — Wed, 12 Mar 2008 12:47:41 GMT

Thanks,

Still some information has to be given I suppose,

Total load on database is 84%,

8 flds of index in where clause,

There is same sql in the code before the one posted,only diff is making a check for all entries I doubt cause both sql fetching from the same table loading same object,

Kindly Expecting more suggestions out of the above info,

Regards

Re: SQL problem

Former Member — Wed, 12 Mar 2008 13:35:48 GMT

> Total load on database is 84%,

the SE30 will not help you here, go to the SQL Trace, measure the time for this select.

> 8 fields of an index

does not help, see 'explain' whether this index is actually taken, and count only fields without gaps from the first one.

Oracle hints will of course only help with Oracle databases, so I expect you are using Oracle, the Oracle explain is quite easy to read,

double-click on the colored table name in the explain, this tells you total number of lines in table, and different values of fields in index.

Siegfried

Re: SQL problem

Former Member — Wed, 12 Mar 2008 17:03:13 GMT

hi,

Check is s_werks is not empty during data fetch. if the key field passed it empty it is as good as full table scan.

could u tell us the no of records fetched using this select???

Re: SQL problem

Former Member — Sat, 15 Mar 2008 13:13:27 GMT

Hi,

I wanna add one more observation, there is a similar SQL before the one posted both fetching the same table & loading the same itab below is the pseudo code.

If flag = ' '.

sql fetching from RESB loading into I_MDRS having for all entries of itab.(but there is no check made before for all entries)

else.

exec sql one in the posting.

Any Ideas,

Thanks,

Edited by: chidambar dixit on Mar 15, 2008 2:13 PM

Re: SQL problem

Former Member — Sun, 16 Mar 2008 05:25:03 GMT

Hi Chidambar,

Check if the itab( which is used in 'for all entries' ) is not initial before the data fetch from RESB.

In MDRS Select:

1. make sure if s_werks is not initial. and is matnr empty acc to ur requirement???

2. avoid <> ,if possible , populate a range for kzvbr

3. avoid dbskz <> c_dbskz, and delete the unwanted records after the select.

Tune your where conditions(MDRS) for improvement.

Regards,

Madhumitha

Re: SQL problem

Former Member — Wed, 26 Mar 2008 09:23:21 GMT

Hi ,

you added the hint

* Oracle hints option was added to improve performance

%_hints oracle 'FULL( MDRS ) PARALLEL( MDRS, 4 )'.

This is the cause of your performance problem.

PARALLEL is a non-scalable "I take all I can get CPU resources" to make the task as fast as possible

(but not efficient as it could be considering a multi-user environment).

Consider it when you do mass data processing (full table or index scans needed because you have to process a significant amount of the table's rows) preferably done in over night jobs with reduced user load on the system

Solution:

1) Delete the hint

2) Create the missing index wich should contain all the fields that get non-Space values in the WHERE clause

3) read some good performance tuning books (start with the ORACLE docs on that topic)

Hope that helps