performance problem at Select Query

[Former Member]

Hello all,

I have a performance question. Which code can i use instead of this code

" select * from t001a into table it_tp_auth where bukrs in kd_bukrs ".

thus the program runs faster ?

Regards

Heidi

[former_member585060]

Specify required fields instead of using *.

[ThomasZloch]

cannot be optimized, as you're already using the primary key. What makes you think that this statement causes problems?

Thomas

[Former Member]

hi,

select * is taking more time instead specify the fileds u want

to in the select statement .

[Former Member]

use ' select single * from t001a into table '

Edited by: Rajnesh Dharmat on Sep 9, 2008 2:35 PM

[Former Member]

Hi Heidi,

The optimum solution is to fetch only the necessary fields , with all the key fields and they should be there in the order they are in the database table instead of using the select *.

Regards,

Shobana.K.

[ThomasZloch]

> and they should be there in the order they are in the database table

I'm still interested in some proof of this statement. Does the order really make a difference, or are you just guessing?

Thomas

[Former Member]

Thomas - I'm pretty sure you're right...in most cases. But this may actually have been true in earlier releases and may also hold true for non-Oracle, non-DB2 databases. But I don't recall seeing any performance advantage in the order in the WHERE.

Having said that, I generally try to put them in the order of the index I expect to use. This may guide future programmers if problems come up.

Rob

[Former Member]

Since T001A is fully buffered, it's pointless copying it into an internal table.

[Former Member]

Hi Thomas,

I don't think that you will get a benchmark of the Believers "SELECT - WHERE orders" - because it's the tradition of a myth : It poped up long before (and maybe with a little truth in it for databases from the stone age - or it was never true) and the myth is hard to eliminate.

And they will spread it out until forever without raising questions about it.

It's more easier than to benchmark

I never waste any thought about orders of SELECT or WHERE fields.

I do spent a lot of time for ordering the INDEX keys in a meaningful sequence.

Sometimes you can put the most selective field in the front (if IT's a mandatory field in queries - you have the ad-hoc query type that can blow up your efforts for index ordering completly)

bye

yk

[Former Member]

Hi all,

that was my estimation. But I go into wrong direction.

I must TC : FB1lN optimize.

My Problem:

if I select my interval for company code largely, the program (RFITEMAP) is very slow. What I can make in this report, so that preformance becomes better.

Example: ( selection criterion )

company code : 1 to 200 -

---

> very slow

company code: 1 ---> normal

-

---

My estimation:

if I write a report, with same selection screen. In this report

can I call with (Submit) FBL1N and transfer all entered values to FBL1N ?

**************************************************************

report:

report dummy.

tables: lfa.

select-options: bukrs for lfa-bukrs.

start-of-selection.

loop at bukrs into wa_bukrs.

SUBMIT FBL1N with p_param1 =wa_bukrs.

endloop.

***********************************************************

is this realizable ?

best regards

Heidi

Edited by: Heidi Heinzberger on Sep 16, 2008 10:13 PM

[Former Member]

If you submit this report with a company code and no other criteria, it will be slow.

Rob

[Former Member]

Hello,

has someone an idea ?

Best regards

Heidi

[Former Member]

Hi,

Select only the required fields from table T001.

Then performance should improve.

Regards,

Amit Kumar Singh

[Former Member]

I can not believe that there is problem related to the T001a table,

This table is fully buffered, so it should come from the buffer. Field lists, order or WHERE fields are completely irrelevant in this case.

Run it several times and run a SQL trace at the end, if it is still in the SQL trace then there is problem with your buffers.

If is come from the buffer then it is fast.

Maybe 'where bukrs in kd_bukrs' your in condition is empty, then it can happen that it reads fast a lot of things which can need some time, better fill the condition.

Actually no T-table should appear in the SQL trace, as far as I know they are all buffered!

> Example: ( selection criterion )

> company code : 1 to 200 -

---

> very slow

> company code: 1 ---> normal

Looks like buffer fill, and read from buffer.

Siegfried

P.S.: The Order of the fields in the WHERE is irrelevant. Even orders of tables in joins should be irrelevant, but I think there are execptions.

[Former Member]

s