topic Re: Primary Key - Optimization Doubt in Application Development and Automation Discussions

Primary Key - Optimization Doubt

kesavadas_thekkillath — Sat, 13 Jun 2009 06:44:23 GMT

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 ??

Re: Primary Key - Optimization Doubt

Former Member — Sat, 13 Jun 2009 07:13:09 GMT

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

Re: Primary Key - Optimization Doubt

kesavadas_thekkillath — Sat, 13 Jun 2009 07:20:10 GMT

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

Re: Primary Key - Optimization Doubt

Former Member — Sat, 13 Jun 2009 08:06:11 GMT

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

Re: Primary Key - Optimization Doubt

Former Member — Sat, 13 Jun 2009 09:07:53 GMT


> 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

Re: Primary Key - Optimization Doubt

kesavadas_thekkillath — Sun, 14 Jun 2009 08:56:32 GMT

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

Re: Primary Key - Optimization Doubt

kesavadas_thekkillath — Tue, 16 Jun 2009 06:32:30 GMT

Got a clear View on this