SELECT statement takes long time

[Former Member]

Hi All,

In the following code, if the T_QMIH-EQUNR contains blank or space values ,SELECT statement takes longer time to acess the data from OBJK table. If it T_QMIH-EQUNR contains values other than blank, performance is good and it fetches data very fast.

Already we have indexes for EQUNR in OBJK table.

Only for blank entries , it takes much time.Can anybody tell why it behaves for balnk entries?

if not T_QMIH[] IS INITIAL.

SORT T_QMIH BY EQUNR.

REFRESH T_OBJK.

SELECT EQUNR OBKNR

FROM OBJK INTO TABLE T_OBJK

FOR ALL ENTRIES IN T_QMIH

WHERE OBJK~TASER = 'SER01' AND

OBJK~EQUNR = T_QMIH-EQUNR.

Thanks

Ajay

[Former Member]

in select if u use primary key like in objk table OBKNR, OBZAE then it will improve performance in select statement

[Former Member]

Hi

You can use the field QMIH-QMNUM with OBJK-IHNUM

in QMIH table, EQUNR is not primary key, it will have multiple entries

so to improve the performance use one dummy internal table for QMIH and sort it on EQUNR

delete adjacent duplicates from d_qmih and use the same in for all entries

this will improve the performance.

Also use the fields in sequence of the index and primary keys also in select

if not T_QMIH[] IS INITIAL.

SORT T_QMIH BY EQUNR.

REFRESH T_OBJK.

SELECT EQUNR OBKNR

FROM OBJK INTO TABLE T_OBJK

FOR ALL ENTRIES IN T_QMIH

WHERE IHNUM = T_QMIH-QMNUM

OBJK~TASER = 'SER01' AND

OBJK~EQUNR = T_QMIH-EQUNR.

try this and let me know

regards

Shiva

[Former Member]

The number of entries with the blank value might be huge. To get rid of this better follow the below approach.

Better you retrieve all the data into single internal table then segregate internal table data based on blank entries and non blank entries.

hope this helps to improve performance.......

[Former Member]

Hi Ajay

As u have used EQUNR in the where condition, for blank values it selects all the values where EQUNR is blank. so it will surely take time to execute. The only thing u can do is add some more fields in where condition from that table if possible. Or u have to avoid blank entries and write a seperate coding for selecting blank values alone and append it to ur internal table.

[Former Member]

Hi ,

Thanks. I have hardcoded for TASER. I am not supposed to Modify the code.

For huge volume of Non Blank Entries, It takes lesser time.

Only for Blank Entries, It takes a lot time.

Thanks,

aJAY

[Former Member]

hi ,

use all the keys possibe in the select statement and use binary search in the sort statement . then u will get the best performance . this u can check in the tcode st05. use this select statement in the st05 and watch it first and give all the keys possibel then observe , u will find out the performance.

regards,

venkat.