topic Re: Getting Time-Out at Select Query in Application Development and Automation Discussions

Getting Time-Out at Select Query

Former Member — Wed, 08 Aug 2007 12:38:15 GMT

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

Re: Getting Time-Out at Select Query

Former Member — Wed, 08 Aug 2007 12:47:31 GMT

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

Re: Getting Time-Out at Select Query

Former Member — Wed, 08 Aug 2007 12:47:53 GMT

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á

Re: Getting Time-Out at Select Query

Former Member — Wed, 08 Aug 2007 12:52:29 GMT

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

Re: Getting Time-Out at Select Query

Former Member — Wed, 08 Aug 2007 13:16:50 GMT

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

Re: Getting Time-Out at Select Query

Former Member — Wed, 08 Aug 2007 14:04:31 GMT

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

Re: Getting Time-Out at Select Query

Neslinn — Wed, 08 Aug 2007 15:06:17 GMT

hi,

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

Reward if this helps.

Regards,

Neslin.

Re: Getting Time-Out at Select Query

KjetilKilhavn — Wed, 08 Aug 2007 16:48:28 GMT

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.

Re: Getting Time-Out at Select Query

Former Member — Thu, 09 Aug 2007 03:13:20 GMT

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

Re: Getting Time-Out at Select Query

Former Member — Thu, 09 Aug 2007 11:16:38 GMT

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

Re: Getting Time-Out at Select Query

Former Member — Thu, 09 Aug 2007 13:10:31 GMT

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