Secondary index

[Former Member]

Hi,

I have created a secondary index ZA2 for table LTAK with fileds LATER, PASSD and KQUIT.

In my Where clause:

Select * into <itab> from LTAK

where lgnum = plgnum AND

Kquit = 'X' and

passd = space

%HINTS MSSQLNT 'TABLE %TABLE% INDEX([LTAK~ZA2])'.

Is there anything wrong with Where clause, because i have not seen the better performence.

Thanks in advance.

narasimha

[RichHeilman]

If you created the index on the following fields in this order,

LATER

PASSD

KQUIT

Then you should have LATER as the first field in your WHERE clause, otherwise your index is worthless.

Select * into <itab> from LTAK
<b>where later = 'whatever'
  and  Kquit = 'X' 
  and passd = space</b> 
%HINTS MSSQLNT 'TABLE %TABLE% INDEX([LTAK~ZA2])'.

If you WHERE clause was like this, then you would see a performance boost.

Regards,

Rich Heilman

[Former Member]

Hi Rich,

Thanks for your quick response. I forgot to mention LATER in the where clause.

my code:

Select * into <itab> from LTAK

where lgnum = plgnum AND

Kquit = 'X' and

LATER IN (1,2,3) and

passd = space

%HINTS MSSQLNT 'TABLE %TABLE% INDEX([LTAK~ZA2])'.

I there any problm if field sequnce is different.

Thanks.

[Former Member]

You must include all the fileds exactly the index you created and you can verify in the SQL trace whether the index picked or not while selecting the data.

Thanks,

Vamshi

[RichHeilman]

Again, the order of the fields in your WHERE clause is important. It must match the same order of the fields in your index. Then you will see a performance boost.

Regards,

Rich Heilman

[Former Member]

Is it required to add LGNUM filed into the secondary index?

Thanks,

Narasimha

[RichHeilman]

I would, this will definitly help in your performance. Is there a reason why you didn't add it to begin with?

Regards,

Rich Heilman

[Former Member]

Hi Rich,

There is no reason, actually the above select statement is a standard SAP statement in the function module L_TO_DN_READ. We are tying to improve the performence for the standard select statement.

Thanks,

Narasimha.

[Former Member]

There already is an index on lgnum and kquit for table LTAK. I don't know how much performance would improve if you add another field.

But since it's a standard SAP program, open an OSS note and let SAP analyze it.

Rob