Getting Time-Out at Select Query

[Former Member]

Dear All,

I am getting Time-Out at particular Select Query.

This error i am getting since last 3 days, before it was working fine. And there is no any change made to program in last 3 days.

select  a~qmnum
         matnr
         kunum
         iwerk
         spart
 into    corresponding fields of iviqmel
 from    ( qmih as a
           inner join qmel as b on b~qmnum = a~qmnum )
 for all entries in i_tab
 where qmart in ('FS', 'ZP')
       and iwerk   = i_tab-iwerk
       and matnr   = i_tab-matnr
       and kunum   = i_tab-kunum
       and spart   = i_tab-spart.
   append iviqmel.
 endselect.

In Program table QMEL also using the proper Index.

Can you please give me any suggestion that how to come out from this issue.

Regards,

Dharmesh

[Former Member]

Dear All,

I am getting Time-Out at particular Select Query.

This error i am getting since last 3 days, before it was working fine. And there is no any change made to program in last 3 days.

select  a~qmnum
         matnr
         kunum
         iwerk
         spart
 into    corresponding fields of iviqmel
 from    ( qmih as a
           inner join qmel as b on b~qmnum = a~qmnum )
 for all entries in i_tab
 where qmart in ('FS', 'ZP')
       and iwerk   = i_tab-iwerk
       and matnr   = i_tab-matnr
       and kunum   = i_tab-kunum
       and spart   = i_tab-spart.
   append iviqmel.
 endselect.

In Program table QMEL also using the proper Index.

Can you please give me any suggestion that how to come out from this issue.

Regards,

Dharmesh

[Former Member]

it may be due to....

select a~qmnum

matnr

kunum

iwerk

spart

into corresponding fields of iviqmel

from ( qmih as a

inner join qmel as b on b_{qmnum = a}qmnum )

for all entries in i_tab

where qmart in ('FS', 'ZP')

and iwerk = i_tab-iwerk

and matnr = i_tab-matnr

and kunum = i_tab-kunum

and spart = i_tab-spart.

append iviqmel.

endselect.

in ur select u need to mention a~ or b~ to the fields ur selecting from the database...

[Former Member]

Hi!

It occurs very rarely, but if your i_tab IS EMPTY (at the for all entries), then the SELECT will check ALL ENTRIES without any restriction and this can cause a dump.

Regards

Tamá

[Former Member]

Hi Tams,

Thanks for reply.

But I am checking that also...

I ahve written select query between...

If not i_tab[] is initial.
.....Query
endif.

Regards,

Dharmesh

[Former Member]

Hi

your useing code in <b>select and endselect</b>

that is the main problem

if u r sending some small amount of data then it works fine

if data load is heavy then it will go timed out and genarate short dump

if it is a standard program then just your basis people to reduce the parameter load

if it is a z program then change that select and endselect

and puto into table itab

then it will work fine

reward if usefull

[Former Member]

the SELECT looks OK. The only problem I can see is if the database is not choosing the correct index (MCM - material/plant in this case). Have you done a performance trace (ST05) to see which index is being used?

Rob

[Former Member]

Hi Rob,

I run the SQL Trace also, Query is using correct Index.

and I had also chenge the select query.

if not i_tab[] is initial.
    select a~matnr a~kunum a~spart
           b~qmnum b~iwerk
           into corresponding fields of table iviqmel
           from qmel as a inner join
                qmih as b
                on a~qmnum = b~qmnum
           for all entries in i_tab
           where a~qmart in ('FS', 'ZP')
             and a~matnr   = i_tab-matnr
             and a~kunum   = i_tab-kunum
             and a~spart   = i_tab-spart
             and b~iwerk   = i_tab-iwerk.
endif.

even its geving time out error.

Regards,

Dharmesh

[Former Member]

I wonder if it's time to update your database statistics or do a database re-org. Are other transactions agaisnt these tables slow as well?

Rob

[Neslinn]

hi,

If it was gng fine before. just check the RUNTIME PROFILE PARAMETER to some higher values.

Reward if this helps.

Regards,

Neslin.

[KjetilKilhavn]

Don't really know how significant this is, you can test it and see, but I would normally SELECT ... INTO CORRESPONDING FIELDS OF TABLE. As far as I know that is more efficient than SELECT ... APPEND ... ENDSELECT, in particular in your case, where you are appending without any processing inside the select loop.

I don't think it makes enough difference to make your program 'good', but I suppose any (easily gained) improvement is welcome when you have a performance problem.

There are lots of things you can try, but my vote goes to Rob's suggestion about finding out if the correct index is being selected. Perhaps all you need is a database hint.

Is your table I_TAB containing a lot of entries? That can be a problem in FOR ALL ENTRIES selections. Perhaps it would be possible to rewrite your program to use a join in order to avoid the extra traffic back and forth between application and database server.

Well, these were just some ideas. <u>You</u> will have to find out what works, I'm afraid that's in the nature of performance problems.

[Former Member]

Hi,

can you wirte down,

+ the key fields of both tables

+ the number of entries in your system

+ and the fields of used indexes if different from the primary key

If you checked the ST05 and explain, did you see the expected number of rows and the expected costs?

Siegfried