topic Re: Performance issue in Application Development and Automation Discussions

Performance issue

Former Member — Wed, 23 Nov 2005 10:39:09 GMT

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

Re: Performance issue

FredericGirod — Wed, 23 Nov 2005 10:51:33 GMT

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

Re: Performance issue

Former Member — Wed, 23 Nov 2005 10:51:38 GMT

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

Re: Performance issue

Former Member — Wed, 23 Nov 2005 14:27:24 GMT

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

Re: Performance issue

Former Member — Wed, 23 Nov 2005 14:56:44 GMT

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

Re: Performance issue

Former Member — Wed, 23 Nov 2005 15:09:21 GMT

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