Primary Key - Optimization Doubt

[kesavadas_thekkillath]

Hi Friends,

Suupose i dont have a primary key field for a select query and if i write it like this

data:v_ebeln type ekko-ebeln value is initial.

select ebeln bukrs from ekko
into table it_ekko
where ebeln ge v_ebeln   <-------------
and bukrs in s_bukrs.

Do this improve performance ??

[Former Member]

Hi,

It will not increase performance, you should include some other fields to restrict the number of records fetched such as document type or category. So better leave vbeln from the where condition.

So what is the purpose of this query?

Regards

Karthik D

[kesavadas_thekkillath]

Hi Karthik ,

This was just a example ..

Just want tro know That when i dont have any ebeln to pass in the query, passing a value GE initial for ebeln , will it increase the performance atleast to some extent.

query 1 without ebeln.

select ebeln bukrs into table itab where bukrs in s_bukrs

query 1 with ebeln ge initial.

select ebeln bukrs into table itab

where ebeln ge v_ebeln

bukrs in s_bukrs

[former_member194613]

you should try your example in your system! There you can learn nmore than by asking here

Use the SQl-Trace.

The question is more complicated than you think.

The addition of the condition can influence the optimizer, but not all optimizers behave the same,

therefore check your own.

The optimizer does not see whether the value is initial or something else, it sees only the condition with a host variable. So it counts he condtion in the same way as a GE '99999' or whatever.

If there are additional conditions then this condition can influence the otimizer to use another access path, which can change the performance.

In your case it can happend that the access changes from full table scan to index access, but not neccessarily.

But in execution the condition does not help, it does not restrict anything, it can even happen that the

access becomes slower.

Siegfried

[Former Member]

>

> But in execution the condition does not help, it does not restrict anything, it can even happen that the 
> access becomes slower.
>
> Siegfried

Exactly, in my system the query with vbeln condition is taking more time than the one without that condition. So better don't give anything if you don't have primary key fields to pass.

Regards

Karthik D

[kesavadas_thekkillath]

Thanks Karthik and Siegfried,

As said it differs, from qmel its picking data a bit faster when passed with 'qmnum' but with ebeln in ekpo its slow as karthik said....Any ways Thanks for ur suggestions

Keshav

[kesavadas_thekkillath]

Got a clear View on this