topic Re: Improving an SQL Statement in Application Development and Automation Discussions

Improving an SQL Statement

Former Member — Tue, 17 Feb 2009 08:17:45 GMT

Hi All,

I have been hounded by my Admin Team to try and sort this SQL block out as it is taking a while to execute in Production..

SELECT * INTO CORRESPONDING FIELDS OF TABLE i_cats

FROM catsdb AS a

INNER JOIN catsps AS b

ON a_{counter = b}counter

FOR ALL ENTRIES IN i_data

WHERE a~arbid EQ i_data-arbid

AND a~raufpl EQ i_data-aufpl

AND a~raplzl EQ i_data-aplzl

AND a~workdate IN s_wdate

AND a~status EQ '30'

AND b~transfer EQ 'X'.

However, I really can' t think of a better way to do this? Help!

Re: Improving an SQL Statement

Former Member — Tue, 17 Feb 2009 08:19:43 GMT

Hi,

Dont use for all entries & INTO CORRESPONDING FIELDS OF

Instead use Join with three Tables...

Reduce two db hits to 1.

Best Way:

Create a Database veiw with all fields of the required table.

you can use the view in use program and Use select on the view.

NOTE:Views are buffered at application server.

Regards,

Edited by: GP on Feb 17, 2009 9:23 AM

Re: Improving an SQL Statement

Former Member — Tue, 17 Feb 2009 08:23:09 GMT

One thing that you can do is avoid SELECT * INTO CORRESPONDING

Write each column name u want to fetch into

SELECT col1 col2 .....

INTO TABLE I_CATS

Second thing..in internal table I_DATA..if possible delete duplicate entries in case any

this will help

Regards,

Prashant

Re: Improving an SQL Statement

Former Member — Tue, 17 Feb 2009 08:25:20 GMT

Hi Prashant, thanks but the i_cats table has many many fields hence I don't think this is feasible ?

Re: Improving an SQL Statement

Former Member — Tue, 17 Feb 2009 08:26:47 GMT

Hi,

Try this out:

1. Create Database view combining catsdb & catsps.

2. Create secondary index for catsdb using 'aufpl' field

3. Try to squeeze your for all entries table, by sorting it out and delete for any duplicate entries.

Regards,

Lim...

Re: Improving an SQL Statement

Former Member — Tue, 17 Feb 2009 08:57:20 GMT

It is always the same task, run the SQL trace, otherwise there is no way to improve the statement.

Check the explain, in which way is the statement processed, what index is used, how selective is the access etc. etc.

Leave the rest of the statement as it is, I do not recommend

+ to remove the INTO CORRESPONDING

+ I do not recommend a view

+ and the FOR ALL ENTRIES can also not be removed

But check, whether the FAE is empty or has a lot of duplicates.

Siegfried

Re: Improving an SQL Statement

ThomasZloch — Tue, 17 Feb 2009 09:11:59 GMT

When doing the trace, check especially if index CATSDB~6 (by ARBID) is being used for access, this seems to be the best one as far as I can tell by just looking at the table definition. Also check if that index is active in your database.

Thomas

Re: Improving an SQL Statement

former_member506713 — Tue, 17 Feb 2009 10:42:33 GMT

Do not use inner joins and FAE in a single statement.

You can increase the performance by creating a view from CATSDB and CATSPS and secondary index AUFPL for CATSDB.

Regards

Lucky

Re: Improving an SQL Statement

Former Member — Wed, 18 Feb 2009 13:46:28 GMT

Hi,

a view is a view is a view...

A view is nothing more than a STORED SQL statement (yes, and it can describe a join of tables or other complex staments). But it has nothing to do with performance, neither it's result is STORED anywhere inside the database.

There is no silver bullett to performance like "avoid this, instead do that" (however, there

are existing best practices about data retrieval - see for the guys in the top list of answers).

In the end it's reading from disks into database buffers (and if you are lucky it's already cached there)

minimizing I/O.

ST05 SQL trace gives the ONLY clue what execution path the database is likely to choose.

Start here to improve the statement...

bye

Re: Improving an SQL Statement

Former Member — Wed, 27 May 2009 15:34:42 GMT

done

Re: Improving an SQL Statement

Former Member — Wed, 27 May 2009 15:34:42 GMT

done