Problem with Select Statements

[sudhir_uppalapati]

Hi All,

I have a performance problem for my report because of the following statements.

How can i modify the select statements for improving the performance of the report.

DATA : shkzg1h LIKE bsad-shkzg,

shkzg1s LIKE bsad-shkzg,

shkzg2h LIKE bsad-shkzg,

shkzg2s LIKE bsad-shkzg,

shkzg1hu LIKE bsad-shkzg,

shkzg1su LIKE bsad-shkzg,

shkzg2hu LIKE bsad-shkzg,

shkzg2su LIKE bsad-shkzg,

kopbal1s LIKE bsad-dmbtr,

kopbal2s LIKE bsad-dmbtr,

kopbal1h LIKE bsad-dmbtr,

kopbal2h LIKE bsad-dmbtr,

kopbal1su LIKE bsad-dmbtr,

kopbal2su LIKE bsad-dmbtr,

kopbal1hu LIKE bsad-dmbtr,

kopbal2hu LIKE bsad-dmbtr.

*These statements are in LOOP.

SELECT shkzg SUM( dmbtr )

INTO (shkzg1s , kopbal1s)

FROM bsid

WHERE bukrs = ibukrs

AND kunnr = ktab-kunnr

AND budat < idate-low

AND shkzg = 'S'

AND umskz EQ ''

GROUP BY shkzg.

ENDSELECT.

SELECT shkzg SUM( dmbtr )

INTO (shkzg1su , kopbal1su)

FROM bsid

WHERE bukrs = ibukrs

AND kunnr = ktab-kunnr

AND budat < idate-low

AND shkzg = 'S'

AND umskz IN zspgl

GROUP BY shkzg.

ENDSELECT.

SELECT shkzg SUM( dmbtr )

INTO (shkzg1h , kopbal1h)

FROM bsid

WHERE bukrs = ibukrs

AND kunnr = ktab-kunnr

AND budat < idate-low

AND shkzg = 'H'

AND umskz EQ ''

GROUP BY shkzg.

ENDSELECT.

SELECT shkzg SUM( dmbtr )

INTO (shkzg1hu , kopbal1hu)

FROM bsid

WHERE bukrs = ibukrs

AND kunnr = ktab-kunnr

AND budat < idate-low

AND shkzg = 'H'

AND umskz IN zspgl

GROUP BY shkzg.

ENDSELECT.

SELECT shkzg SUM( dmbtr )

INTO (shkzg2s , kopbal2s)

FROM bsad

WHERE bukrs = ibukrs

AND kunnr = ktab-kunnr

AND budat < idate-low

AND shkzg = 'S'

AND umskz EQ ''

GROUP BY shkzg.

ENDSELECT.

SELECT shkzg SUM( dmbtr )

INTO (shkzg2su , kopbal2su)

FROM bsad

WHERE bukrs = ibukrs

AND kunnr = ktab-kunnr

AND budat < idate-low

AND shkzg = 'S'

AND umskz IN zspgl

GROUP BY shkzg.

ENDSELECT.

SELECT shkzg SUM( dmbtr )

INTO (shkzg2h , kopbal2h)

FROM bsad

WHERE bukrs = ibukrs

AND kunnr = ktab-kunnr

AND budat < idate-low

AND shkzg = 'H'

AND umskz EQ ''

GROUP BY shkzg.

ENDSELECT.

SELECT shkzg SUM( dmbtr )

INTO (shkzg2hu , kopbal2hu)

FROM bsad

WHERE bukrs = ibukrs

AND kunnr = ktab-kunnr

AND budat < idate-low

AND shkzg = 'H'

AND umskz IN zspgl

GROUP BY shkzg.

ENDSELECT.

[Former Member]

try to avoid endselect

[sudhir_uppalapati]

Hi,

Can you provide me the sample statement.

[Former Member]

Hi Sudhir,

Using SELECT SINGLE is the better way for performance. Firstly READ statement for single value rather than LOOP

Data:
  w_index type i value 1,
  w_lines type i.
Describe table ktab lines w_lines.
While w_index LE w_lines.
Read table ktab index w_index.
SELECT single shkzg SUM( dmbtr )
INTO (shkzg1s , kopbal1s)
FROM bsid
WHERE bukrs = ibukrs
AND kunnr = ktab-kunnr  " hope here you are passing a single value
AND budat < idate-low
AND shkzg = 'S'
AND umskz EQ ''
GROUP BY shkzg.
w_index = w_index + 1.
Endwhile.

With luck,

Pritam.

Edited by: Pritam Ghosh on Dec 30, 2008 10:48 AM

[Former Member]

Hi.

You should avoid the END select and Group by to improve your coding performance.

Regards.

Jay

[Former Member]

Hello,

To improve the performance,plz use SELECT single statement and if possible dependence on ur requirement its better reterive the data out side the loop statement and read it using read statement.

Regards,

Anil.D

[Former Member]

Hi ,

Check if you can replace the Select - End select of your programming logic and try some alternate way of achieving the same with a single fetch from the tables.

In this way the data base access will be greatly reduced and hence the perfomce can be increased.

Regards,

Radhika.

[former_member194613]

Please stop writing answers if you understrand nothing about database SELECTS!

All above recommendations are pure nonsense!

As always with such questions, you must do an analysis before you ask! The coding itself is perfectly o.k., a SELECT with an aggregate and a GROUP BY can not be changed into a SELECT SINGLE or whatever.

But your SELECTS mustr be supported by indexes!

Please run SQL Trace, and tell us the results:

I see 8 statements, what is the duration and the number of records coming back for each statement?

Maybe only one statement is slow.

See

SQL trace:

Siegfried

[Former Member]

>

> Please stop writing answers if you understrand nothing about database SELECTS!
> All above recommendations are pure nonsense!
> 
> As always with such questions, you must do an analysis before you ask! The coding itself is perfectly o.k., a SELECT with an aggregate and a GROUP BY can not be changed into a SELECT SINGLE or whatever.
> 
> But your SELECTS mustr be supported by indexes!
> 
> Please run SQL Trace, and tell us the results:
> 
> I see 8 statements, what is the duration and the number of records coming back for each statement?
> Maybe only one statement is slow.
> 
> See
> SQL trace:
> /people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy
> 
> 
> Siegfried

Nice point there Siegfried. Instead of giving constructive suggestion, people here give a very bad suggestion on using SELECT SINGLE combined with SUM and GROUP BY.

I hope the person already look at your reply before he try using select single and wondering why he has error.

Anyway, the most important thing is how many loop expected for those select statements?

If you have like thousands of loop, you can expect a poor performance.

So, you should also look at how many times the select statement is called and not only performance for each select statement when you're doing SQL trace.

Regards,

Abraham

[former_member217316]

Hi Sudhir

Please go through the SQL Trace results. Also, it'll be great if you avoid using Select..EndSelect and the group by statements. Moreoever, please get these Select Statements out of the loop. Incase you need them inside then you can put your data in an internal table instead of the variables and READ those itabs.

Hope this is useful.

Regards

Harsh

[Former Member]

Hi,

in u r all select statements are writing inside the loop,and u r using the select .... endselect.therese all statements problem with performance,dont use the select statement with in the loop .

Regards,

Madhu

[Former Member]

Hi Sudhir,

Instead of using these statements inside the loop,

you can use ranges as we can't use for all entries for aggregate functions other than count.

For refference look into the below example:

RANGES: r_cust FOR wa_ktab-kunnr.

LOOP AT ktab INTO wa_ktab "Assuming you are looping at ktab

  r_cust-sign = 'I'.
  r_cust-option = 'EQ'.
  r_cust-low = wa_ktab-kunnr.

  APPEND r_cust.
ENDLOOP.

"Instead of using delect stmts inside the loop use as follows
SELECT shkzg SUM( dmbtr )
INTO  TABLE <itab>  "Table contains fields shkzg1s , kopbal1s
FROM bsid
WHERE bukrs = ibukrs
AND kunnr IN r_cust "Use your ranges
AND budat < idate-low
AND shkzg = 'S'
AND umskz EQ ''
GROUP BY shkzg.

Hope this helps you.

Regards,

Manoj Kumar P

Edited by: Manoj Kumar on Jan 16, 2009 6:56 AM

[Former Member]

Hi Sudhir,

First thing you should always avoid select statements inside a loop. This effects the performance a lot.

Second thing is you are always hitting the BSID, BSAD tables again and again.

Instead you can do this.

you read complete BSID and BSAD tables into internal tables outside the loop. And use a read statement inside the loop where ever required this improves the performance a lot.

And one more thing is it is better to use UP TO 1 ROWS statement and ENDSELECT statement rather than using without up to 1 rows statement.

Regards

Madhu.