Performance issue

[Former Member]

Hi,

I have a Custom table for USERDETAILS, on which I am fetching data using

SELECT ecno bname department l1name l2name bukrs

FROM yusrdetail

INTO TABLE vt_yusrdetail1

WHERE essid IS not NULL

AND department IN s_dept

AND bukrs IN s_bukrs.

Here how can I improve performance of this Query with out creating index on DEPT and BUKRS.

INDEX is already existing on ESSID.

Here BNAME is the only Primary Key available on this table.

It is very urgent Plz....

Thanx

K P

[FredericGirod]

Hi K P,

Your select looks good, maybe, if you have a good idea of the number of result, you can be faster when you define your internal table. You just have to create the good size, that will be faster because SAP will not have to make dynamic allocation memory.

But, this solution is dangerous, cause you must know the number of lines you will have ...

data : vt_yusrdetail1 type table .... initial size ...

Rgd

Frédéric

[Former Member]

Hi try this

SELECT <b>bname ecno</b> department l1name l2name bukrs

FROM yusrdetail

INTO TABLE vt_yusrdetail1

WHERE essid IS not NULL

AND department IN s_dept

AND bukrs IN s_bukrs.

Hope this will help.

regards

vijay

[Former Member]

K P - I think the 'WHERE essid IS not NULL' is doing you in. It will have to read all records in the database.

Since you don't want to create a secondary index, you could try reading the entire table into an internal table and then deleting the entries you don't want. This <i>might</i> be faster but will use more memory.

Rob

[Former Member]

Hi

If essid is a numeric field and is a running number try providing a between clause instead of IS not NULL.

for e.g.

SELECT ecno

bname

department

l1name

l2name

bukrs

FROM yusrdetail

INTO TABLE vt_yusrdetail1

WHERE essid between 1 and 9999999999

AND department IN s_dept

AND bukrs IN s_bukrs.

NOT in the where clause always by-passes index.

Regards

Kalpana

[Former Member]

Declare a range for ESSID and do not fill that range. You have to add the ESSID field to the internal table. IS NOT NULL is not a good option to use, because most table fields will be initialized to a non-null value. Change your select as follows and this way you will make it use the index on ESSID.

SELECT essid ecno bname department l1name l2name bukrs
  FROM yusrdetail
  INTO TABLE vt_yusrdetail1
 WHERE essid IN r_essid.

DELETE vt_yusrdetail1 WHERE essid is initial
                    AND NOT ( department IN s_dept AND 
                              bukrs      IN s_bukrs ).