topic Performance with BSAD Table in Application Development and Automation Discussions

Performance with BSAD Table

Former Member — Wed, 27 May 2015 12:36:34 GMT

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

Re: Performance with BSAD Table

Former Member — Wed, 27 May 2015 12:55:15 GMT

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.

Re: Performance with BSAD Table

Former Member — Wed, 27 May 2015 13:05:52 GMT

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

Re: Performance with BSAD Table

Former Member — Wed, 27 May 2015 13:13:01 GMT

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

Re: Performance with BSAD Table

Former Member — Thu, 28 May 2015 05:06:45 GMT

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.

Re: Performance with BSAD Table

Former Member — Thu, 28 May 2015 12:53:34 GMT

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

Rob

Re: Performance with BSAD Table

RaymondGiuseppi — Thu, 28 May 2015 13:59:20 GMT

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

Re: Performance with BSAD Table

Jelena_Perfiljeva — Thu, 28 May 2015 22:33:02 GMT

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.

Re: Performance with BSAD Table

matt — Fri, 29 May 2015 04:15:49 GMT

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.

Re: Performance with BSAD Table

Former Member — Fri, 29 May 2015 10:53:32 GMT

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.

Re: Performance with BSAD Table

Former Member — Fri, 29 May 2015 13:30:30 GMT

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

Re: Performance with BSAD Table

Former Member — Tue, 02 Jun 2015 08:38:38 GMT

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

MANDT, AUGBL

Active

CONVAR

21.03.2013

BSAD~Z01

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

Please suggest.

Regards,

Lokeswar.

Re: Performance with BSAD Table

Former Member — Tue, 02 Jun 2015 13:31:30 GMT

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

Rob

Re: Performance with BSAD Table

Former Member — Tue, 02 Jun 2015 13:36:56 GMT

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.

Re: Performance with BSAD Table

Former Member — Sun, 07 Jun 2015 06:16:56 GMT

This issue is fixed now,

Closing Thread.

Thanks all.

Re: Performance with BSAD Table

Former Member — Mon, 08 Jun 2015 15:20:42 GMT

would you mind sharing the solution?

Rob

Re: Performance with BSAD Table

Former Member — Mon, 08 Jun 2015 17:50:19 GMT

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.