topic Re: performance required in select statement in Application Development and Automation Discussions

performance required in select statement

Former Member — Tue, 17 Jul 2007 00:58:43 GMT

hi gurus,

my select statement below is taking around 15 sec to execute, need your help in this regard.

select single * from tablename where no = itab-no.

Thanks,

Re: performance required in select statement

Former Member — Tue, 17 Jul 2007 01:00:32 GMT

Hi Vijay,

Can you jsut paste the correct code.

And also as this is select single and if you are having the whole key in where clause then it is the fastest, but if you don't have the whole key then do not use SELECT single instead use SELECT UPTO 1 ROWS.

Regards,

Atish

Re: performance required in select statement

Former Member — Tue, 17 Jul 2007 01:15:11 GMT

hi atish,

SELECT SINGLE * FROM VIQMEL WHERE EQFNR = ITAB-AUFNR..

Re: performance required in select statement

Former Member — Tue, 17 Jul 2007 02:40:44 GMT

You can try with select upto one row..

SELECT * FROM VIQMEL into VIQMEL up to 1 row

WHERE EQFNR = ITAB-AUFNR..

when you use select single on databse view ,that to you are selecting the all fields,the field EQFNR it may have many same records,so use select upto 1 row.

check the below link :

http://help.sap.com/saphelp_nw04s/helpdata/en/a5/298aa13dae11d3a98200a0c9449261/frameset.htm

Thanks

Seshu

Re: performance required in select statement

Former Member — Tue, 17 Jul 2007 02:51:05 GMT

Hi Vijay,

The most costly statement is select statement, so you should take precautions to use it.

In your case though you are not providing the whole statement, What i can suggest you is

1. Instead of select * , select only the filelds required in the order they are there in the table.

2. In the where clause if possible give the entire primary key in the same order as it is there in the table.

3. If you are using fields other than primary key, create a secondary index.

Reward points if useful,

Aleem.

Re: performance required in select statement

Former Member — Tue, 17 Jul 2007 03:53:26 GMT

VIQMEL is not a table itself - it is a view of tables QMIH, QMEL and ILOA.

the field you are searching on (EQFNR) is a non-key field on table ILOA which is not an index field either.

This will be very slow depending on the size of the tables.

Is there any other data you can use to search by? perhaps it would be quicker to get extra key data for this view by reading some other tables first?

If not, you may need to add an index to ILOA for this field - I would check with SAP as adding indexes to standard tables such as this which already appears to have several indexes may have unforseen impacts on standard processing. Generally it is all right, but an extra index for the system to maintain can cause problems sometimes.

Re: performance required in select statement

Former Member — Mon, 23 Jul 2007 08:31:25 GMT

thanks