topic Re: Reducing Data base selection time in Application Development and Automation Discussions

Reducing Data base selection time

Former Member — Thu, 10 Dec 2009 04:21:42 GMT

Hi,

I need to get the Excise Documnets which are not Billed. For that I've used following logic. But it is taking much time to get the data.

SELECT a~exnum

a~exyear

a~exdat

a~rdoc2

SUM( a~exbed )

SUM( a~ecs )

SUM( a~exaddtax1 )

b~vbtyp

b~knumv

b~bukrs

b~kunrg

FROM j_1iexcdtl AS a INNER JOIN vbrk AS b

ON a_{rdoc2 EQ b}vbeln

INTO TABLE lt_excise WHERE a~exdat IN s_fkdat AND

a~status EQ 'C' AND

a~exdat ge '20090401' AND

NOT EXISTS ( SELECT vbeln FROM vbrk WHERE vbeln EQ a~rdoc3 AND

vbtyp EQ 'M' ) AND

b~vbtyp EQ 'U' AND

b~bukrs IN s_bukrs AND

b~kunrg IN s_kunrg

GROUP BY a~exnum

a~exyear

a~exdat

a~rdoc2

b~vbtyp

b~knumv

b~bukrs

b~kunrg.

Pl help to reduce the time for database selection.

Regards,

Rajiv. V

Re: Reducing Data base selection time

Former Member — Thu, 10 Dec 2009 04:43:55 GMT

Hi,

Try to split this into two.

First try to fetch the sales order which are required.

Then use this sales order in second query to find excise related data with for example for all entries.

This will improve the performance.

Regards,

Sunny

Re: Reducing Data base selection time

Former Member — Thu, 10 Dec 2009 04:57:07 GMT

Hi Vaddepati,

Also dont use aggregate functions like SUM . It also causes performance issues.

Regards

Abhii

Re: Reducing Data base selection time

former_member329859 — Thu, 10 Dec 2009 05:13:07 GMT

HI,

Try this,

Firstly take data using select * from table command on transperent tables,

and within the loop use select single and read table commands

Regards

Gaurav

Re: Reducing Data base selection time

Former Member — Thu, 10 Dec 2009 05:15:51 GMT

Hi,

To decrease the load on DB ,you can process Group by and SUM functions in Loop Endloop of resulting table.

Avoid using Subqueries with not condition.I read ,NOT compromises the index and starts linear search.

There are already many discussions on usage on For all entries and Join .So search forum for more details.

The performance of join and For all entries are context oriented.


SELECT a~exnum
a~exyear
a~exdat
a~rdoc2
SUM( a~exbed )                 "  Avoid using SUM use Control break in loop endloop
SUM( a~ecs )
SUM( a~exaddtax1 )
b~vbtyp
b~knumv
b~bukrs
b~kunrg
FROM j_1iexcdtl AS a INNER JOIN vbrk AS b
ON a~rdoc2 EQ b~vbeln   "Rdoc2 is available in secondary index ,I am not sure whether this affects the performance ,EXPERT advice is required     
INTO TABLE lt_excise WHERE a~exdat IN s_fkdat AND      
a~status EQ 'C' AND                             
* a~exdat ge '20090401' AND                 "if possible, try to use  indexed fields from VBRK 
NOT EXISTS ( SELECT vbeln FROM vbrk WHERE vbeln EQ a~rdoc3 AND   "Avoid using Not this would compromise the index or use Delete itab on resulting table
vbtyp EQ 'M' ) AND                                                                                
b~vbtyp EQ 'U' AND   
b~bukrs IN s_bukrs AND
b~kunrg IN s_kunrg
GROUP BY a~exnum                       " Avoid Group by 
a~exyear
a~exdat
a~rdoc2
b~vbtyp
b~knumv
b~bukrs
b~kunrg.