topic Re: Problem with Select Statements in Application Development and Automation Discussions

Problem with Select Statements

sudhir_uppalapati — Tue, 30 Dec 2008 09:27:11 GMT

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.

Re: Problem with Select Statements

Former Member — Tue, 30 Dec 2008 09:29:50 GMT

try to avoid endselect

Re: Problem with Select Statements

sudhir_uppalapati — Tue, 30 Dec 2008 09:34:36 GMT

Hi,

Can you provide me the sample statement.

Re: Problem with Select Statements

Former Member — Tue, 30 Dec 2008 09:40:30 GMT

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

Re: Problem with Select Statements

Former Member — Tue, 30 Dec 2008 10:05:55 GMT

Hi.

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

Regards.

Jay

Re: Problem with Select Statements

Former Member — Tue, 30 Dec 2008 12:07:36 GMT

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

Re: Problem with Select Statements

Former Member — Tue, 30 Dec 2008 12:10:40 GMT

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.

Re: Problem with Select Statements

Former Member — Tue, 30 Dec 2008 12:35:03 GMT

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

Re: Problem with Select Statements

Former Member — Wed, 14 Jan 2009 04:17:07 GMT

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

Re: Problem with Select Statements

Former Member — Wed, 14 Jan 2009 08:45:24 GMT


> 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

Re: Problem with Select Statements

Former Member — Thu, 15 Jan 2009 09:34:33 GMT

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

Re: Problem with Select Statements

Former Member — Fri, 16 Jan 2009 05:45:18 GMT

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

Re: Problem with Select Statements

Former Member — Tue, 20 Jan 2009 10:05:37 GMT

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.