topic Performance improvement in select query in Application Development and Automation Discussions

Performance improvement in select query

mahesh_jagnani — Fri, 18 Jun 2010 09:42:24 GMT

Hi,

I have to improve the performance of the one custome program. I applied SQL trace & found out that below extract of code is taking maximum time

SELECT a~znumdos

a~zdatctx

a~zmotctx

a~gpart

b~vkont

d~zidbarcly

d_{zligne d}zstatulig

b~zmontdact

c~zrmot_statut

d~zcanal

g~crdat

p~schedpid

f~ktokl

FROM zr025sar AS a

INNER JOIN zr026sar AS b ON b_{znumdos = a}znumdos

INNER JOIN zr027sar AS c ON c_{znumdos = a}znumdos

AND c_{vkont = b}vkont

INNER JOIN zr021sat AS d ON c_{zcanal = d}zcanal

AND c_{zcontrat = d}zcontrat

AND c_{zligne = d}zligne

INNER JOIN but000 AS g ON a_{gpart = g}partner

INNER JOIN fkkvkp AS f ON f_{vkont = b}vkont

AND f_{gpart = g}partner

LEFT OUTER JOIN zr136sar AS p ON p_{znumdos = a}znumdos

APPENDING TABLE t_ctr_data_2

WHERE a~zcanal IN r_canal

AND a~zdatctx NE c_null_date

AND a~zdatctx < l_date_act

AND a~zmotctx IN r_motife

AND a~zetadoss IN r_dossier

AND b~zdateevent < l_date_his

AND d~zstatulig IN r_ligne

AND d~zdatstatulig NE c_null_date

AND d~zdatstatulig LE l_date_mdf

AND d~zplantarif IN r_zptarif

AND d~zstatugcr IN r_statutgcr

AND f~ktokl IN r_categ.

How can i use the for all entries in above code.. Will it enhance the performance of the code?

Re: Performance improvement in select query

Former Member — Fri, 18 Jun 2010 10:00:45 GMT

hi,

create a view with all the tables mentioned in the select with " INNER JOIN ", this should improve the performance. Also avoid using 'NE" in where clause. Don't use table "zr136sar" in the view creation as it joined using 'LEFT OUTER JOIN". first fetch data from the view which you are going to create and then fetch data from "zr136sar" using FOR ALL ENTRIES.

Thanks

Phani

Re: Performance improvement in select query

venkatasriram_mygapula — Sun, 20 Jun 2010 10:53:00 GMT

Hi ,

Why dont you use FOR ALL ENTRIES for 3 tables. Select the data from 3 tables into internal table and write the select quirey for next 3 tables with FOR ALL ENTRIES on first internal table

Re: Performance improvement in select query

Former Member — Sun, 20 Jun 2010 20:15:38 GMT

unfortunately nobody can help you here.

Your task depends on the actually used select-options (IN), on the actual distribution of the data and the available indexes on the z-tables.

I would neither recommend you a view, which is just a dictionary stored join and also not a FOR ALL ENTRIES, which will not increase the performance but reduce duplicates at the end, whcih can change even the result.

You must analyse the statement in detail and I would recommend you to get some support by somebody more experienced.

Siegfried

Re: Performance improvement in select query

mahesh_jagnani — Mon, 11 Apr 2011 08:16:46 GMT