How to improve select stmt performance without going for secondary index

[Former Member]

Hi friends,

I have a select statement which does not contains key fields(Primary index) in where condition. And I have to improve that select stmt performance without going for the secondary indexes.

Can you plese suggest the alternative way for this?.

Thanks in advance,

Ramesh.

[Former Member]

Hi,

It depends on the the scenario. Generally we can restrict the number of values by including dates and time etc.

santhosh

[Former Member]

Sorry friend, I didnt get you. Can you explain a bit.

[Former Member]

The probelm with a select stmt with out keys is that it could pull a lot of records from the table and there's no index aswell. The best way to reduce the load from the select is to reduce the number of records it would fetch. this can be done by using DATE fields in the table like ERDAT AUDAT so that only records from a certain period are obtained instead of all the records.

Hope you understood my pov

santhosh

[Former Member]

Thanks yar!

[Former Member]

Hi,

If , possible create a secondary index opf your own But if you have restriction on this, try to arrange the fields in where clause in the same order as they appear in the very table.

This will help the performance a bit.

Another issue, If your table doesn't contain any critical data or data in them are not updated frequently, you may go for Bufferring . it is a good alternate of Indexing with above limitations.

For details in bufferring , check, and all the sublinks.

[concept of buffering|http://help.sap.com/saphelp_nw04/helpdata/en/cf/21f244446011d189700000e8322d00/content.htm]

Regards,

Anirban

[Former Member]

Hi,

There are some statements which does not transfer data just for processing.

Foe example,

1. Aggregate functions,

2. Group By statement

and more.

If we use these statements, performance will increase.

Regards,

R.Nagarajan.

[ThomasZloch]

If you'd tell us the exact select statement (fields, table, where-condition), there might be a chance to suggest an alternative way to achieve the same result quicker via different, existing tables.

Thomas

[Former Member]

I dont know the exact select stmt. This question was posed by my higher official.

Can we go for table buffering?

Will it be the alternative for secondary index?

Edited by: pagidala Ramesh on Sep 29, 2008 1:12 PM

[ThomasZloch]

Table buffering is only suitable for tables that don't have too many entries are being changed rather infrequently. In general, you will see most customizing tables being buffered, but hardly any application data tables (maybe some master data, but no movement data).

So buffering cannot be the general answer here. Tell your boss it is impossible to have a simple answer for this problem, the actual case has to be analysed. For example, you can avoid creating a secondary index on VBRP fields AUBEL and AUPOS, if you know how to use table VBFA. In rare cases, a new secondary index cannot be avoided if performance is a big issue.

Thomas

[former_member194613]

Secondary indexes is not a bad thing, that is your solution an nothing else.

Primary key, the question is actually not whether you know any of the fields you must know the first field of the index otherwise, primary key will not work even if you the ten trailing fields. Buffer is technically connected to primary key, so no option, even if the table would fulfill the buffering conditions.

How large is you table actually? And how important is you statement, is it executed regularly or twice a year?

Siegfried

[Former Member]

> Secondary indexes is not a bad thing, that is your solution an nothing else.

I don't think there is a table nor a program, let alone an indexed one.

I think there is a piece of paper with this question written on it and some space below to answer on....

[ThomasZloch]

hmm, you might be right, would have been quite well disguised...in dubio pro reo

[former_member194613]

you are probably right ...

I often started that questions must be asked by specifically, after running SQL trace I see the following statement needs

1.2sec. How can I make it faster?

Then you could ask what do you expect.

1.197sec is also faster or should it run in 200msec?

Some of usuall recommendations give not much more than 1.197sec, definitely never 200msec.

Siegfried