Performance with BSAD Table

[Former Member]

Hi All,

I have searched through SDN and google no helpfull solution.

I have a requirement to query on BSAD table with key fields BUKRS,ZUONR,GJAHR.

Select VBELN

FROM bsad

      INTO TABLE lt_bsad

      FOR ALL ENTRIES IN im_zuonr

      WHERE bukrs = IM_ZUONR-bukrs

      AND zuonr = im_zuonr-zuonr

      AND gjahr = im_zuonr-gjahr

      AND shkzg = 'S'

      AND rebzg NE 'V'.

If I query with KUNNR , then output is faster  but my requirement is only based on BUKRS,ZUONR,GJAHR.

How to increase the performance.

Please suggest.

regards,

Lokeswar

[Former Member]

No sure how you have come to this where clause. BSAD are meant to be queried for customers and we don-t have customer in your where clause.

But please check this

Check the entries in BSAD and if ZUONR and BELNR are same ( It is same in my system as value wise. Size we can handle via code ) you can use BUKRS , BELNR(Instead of ZUONR) and GJAHR in your where clause. There is a standard index BSEG~5 already created. The select should pick up that index. Check this via SQL trace in ST05.

It will not be the best but atleast will have Index range scan which is way better than the current select.

[Former Member]

Hi Lokeswar,

Did you already try to use any "table view" like BKPF_BSAD? I'm not sure whether it'll work in your case but these views are increased by performance.

Best Regards,

Benjamin

[Former Member]

There's really not much you can do.

But you might look at that REBZG NE 'V'. This field is used in an index, but won't be used because of the NE condition. If there are only a few values for this field in BSAD, you might be able to turn it into an EQ condition.

And that's about it.

Rob

[Former Member]

Hi Rob,

Thanks a lot for your inputs .

but here REBEZ consists lot of numbers other than 'V',

I need to query on BSAD with out KUNNR field.

Pls suggest.

[Former Member]

Like I said, there's not much you can do other than run this in the background.

Rob

[RaymondGiuseppi]

Where does this list of assignment values (ZUONR) come from, if it is from some SD or FI table, you should also got customer number?

Regards,

Raymond

[Jelena_Perfiljeva]

It's a tough one. Without any additional keys, like KUNNR, LIFNR, HKONT it's pretty much no-win. Sometimes secondary index helps, but BSAD already has 6 in our system, so YMMV.

Where does this requirement come from exactly? I agree with Raymond - there might be a better alternative.

Otherwise - looks like a prime candidate for Simple Financials.

[matt]

Isn't the obvious solution to put another index on BUKRS,ZUONR,GJAHR.. So long as the indexes are distinct, it's unlikely to cause a problem - though of course testing is required.

The only time I've encountered an issue with an additional index was with the terrible WHERE clauses generated by SAP Data Services, that were so unnecessarily convoluted that the optimiser got thoroughly lost.

[Former Member]

Hi All,

Thanks for suggestions ,

Here my requirement is to fetch open and close items based on Assignment Number (ZUONR).

Its related to FI module.

[Former Member]

Well, I'd look for other solutions before adding an index.

If you could limit the selection by document or posting date, that would help.

After a while, programs may become obsolete and not run any more, but the index created for it tends to remain and has to be updated every time a document is created.

Rob

[Former Member]

Hi All,

I would be thinking to use existing secondary index in BSAD table along with skipping KUNNR field.

is it good to use secondary index in select query along with primary keys which are not in secondary index and along with Hint.

for ex :

Z01

0

MANDT, AUGBL

Active

0

CONVAR

21.03.2013

BSAD~Z01

E

is it good to write select query with BUKRS , AUGBL , GJAHR .

Please suggest.

Regards,

Lokeswar.

[Former Member]

This is quite a bit different from your first SELECT. Why the change?

Rob

[Former Member]

Hi Rob,

I have two requirements one is based on BUKRS,ZUONR,GJAHR and other is based on BUKRS , AUGBL , GJAHR as inputs.

either of the select query on BSAD table is taking time.

[Former Member]

This issue is fixed now,

Closing Thread.

Thanks all.

[Former Member]

would you mind sharing the solution?

Rob

[Former Member]

Hi Rob,

In my functionality we are triggering RFC FM based on Business area .

In the input to my Select Query I have added customer range table.

Based on Business area customer number starts with business area.

for ex : for India business area , customer number will start with IN*.

Here customer range table will be filled with I CP IN*

This increased some performance.

Regards,

Lokeswar Reddy Byni.