topic Re: How to improve Performance for Select statement in Application Development and Automation Discussions

How to improve Performance for Select statement

Former Member — Tue, 21 Apr 2009 04:56:36 GMT

Hi Friends,

Can you please help me in improving the performance of the following query:

SELECT SINGLE MAX( policyterm ) startterm INTO (lv_term, lv_cal_date) FROM zu1cd_policyterm WHERE gpart = gv_part GROUP BY startterm.

Thanks and Regards,

Johny

Re: How to improve Performance for Select statement

Former Member — Tue, 21 Apr 2009 05:10:50 GMT

Hi,

Do you really need the GROUP BY ? Try doing the processing after fetching records instead of GROUP BY .

Regards,

Deepthi

Re: How to improve Performance for Select statement

Former Member — Tue, 21 Apr 2009 05:51:43 GMT

If you have very large data in table zu1cd_policyterm then GROUP BY will work very slow. Else select data into in interal table and do processing.

Re: How to improve Performance for Select statement

Former Member — Tue, 21 Apr 2009 08:36:46 GMT

long lists can not be produced with a SELECT SINGLE, there is also nothing to group.

But I guess the SINGLE is a bug

 
SELECT MAX( policyterm ) startterm 
              INTO (lv_term, lv_cal_date) 
              FROM zu1cd_policyterm 
              WHERE gpart = gv_part 
              GROUP BY startterm.

How many records are in zu1cd_policyterm ?

Is there an index starting with gpart?

If first answer is 'large' and second 'no' => slow

What is the meaning of gpart? How many different values can it assume?

If many different values then an index makes sense, if you are allowed to create

an index.

Otherwise you must be patient.

Siegfried