topic Re: performance problem at Select Query in Application Development and Automation Discussions

performance problem at Select Query

Former Member — Mon, 08 Sep 2008 11:59:10 GMT

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

Re: performance problem at Select Query

former_member585060 — Mon, 08 Sep 2008 12:01:20 GMT

Specify required fields instead of using *.

Re: performance problem at Select Query

ThomasZloch — Mon, 08 Sep 2008 12:02:23 GMT

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

Thomas

Re: performance problem at Select Query

Former Member — Tue, 09 Sep 2008 09:42:39 GMT

hi,

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

to in the select statement .

Re: performance problem at Select Query

Former Member — Tue, 09 Sep 2008 12:30:03 GMT

use ' select single * from t001a into table '

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

Re: performance problem at Select Query

Former Member — Fri, 12 Sep 2008 09:12:12 GMT

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.

Re: performance problem at Select Query

ThomasZloch — Fri, 12 Sep 2008 11:46:29 GMT

> 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

Re: performance problem at Select Query

Former Member — Fri, 12 Sep 2008 11:58:45 GMT

Hi,

Select only the required fields from table T001.

Then performance should improve.

Regards,

Amit Kumar Singh

Re: performance problem at Select Query

Former Member — Fri, 12 Sep 2008 13:06:35 GMT

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

Re: performance problem at Select Query

Former Member — Fri, 12 Sep 2008 14:41:30 GMT

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

Re: performance problem at Select Query

Former Member — Mon, 15 Sep 2008 08:31:27 GMT

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

Re: performance problem at Select Query

Former Member — Tue, 16 Sep 2008 20:13:09 GMT

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

Re: performance problem at Select Query

Former Member — Tue, 16 Sep 2008 20:24:26 GMT

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

Rob

Re: performance problem at Select Query

Former Member — Wed, 24 Sep 2008 06:59:42 GMT

Hello,

has someone an idea ?

Best regards

Heidi

Re: performance problem at Select Query

Former Member — Wed, 24 Sep 2008 07:17:35 GMT

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.

Re: performance problem at Select Query

Former Member — Fri, 07 Nov 2008 13:22:22 GMT