topic Re: WHERE condition in Application Development and Automation Discussions

WHERE condition

Former Member — Thu, 13 Dec 2007 22:24:07 GMT

Hello experts,

Is there any diffrence in performance between the following two statements ?

SELECT BUKRS BELNR GJAHR AUGBL

INTO TABLE TEMP_BSEG

FROM BSEG

WHERE BUKRS IN S_CCODE

AND BELNR IN S_BELNR

AND GJAHR IN S_GJAHR.

and

SELECT BUKRS BELNR GJAHR AUGBL

INTO TABLE TEMP_BSEG

FROM BSEG

WHERE BUKRS IN S_CCODE

AND BELNR IN S_BELNR

AND GJAHR IN S_GJAHR

AND KOART = 'K'.

Re: WHERE condition

Former Member — Thu, 13 Dec 2007 22:31:30 GMT

Hi,

Use the 2nd option

SELECT BUKRS BELNR GJAHR AUGBL
INTO TABLE TEMP_BSEG
FROM BSEG
WHERE BUKRS IN S_CCODE
AND BELNR IN S_BELNR
AND GJAHR IN S_GJAHR
AND KOART = 'K'.

Regards,

Satish

Re: WHERE condition

Former Member — Thu, 13 Dec 2007 22:32:39 GMT

If your requirement is to take only BSEG records with KOART = 'K', I would do it the second way. Performance shouldn't really be an issue.

Rob

Re: WHERE condition

Former Member — Thu, 13 Dec 2007 22:35:06 GMT

I think there should be some difference in the performance of the first statement.

1st is better when compared to the second select statement, being in the first select statement you are using 3 key fields where as

in the 2nd select statement 2 key fields and one non key field in the where condition

where as in the second select statement you are directly takeing only particular type of values corresponding to non key field ('K') so it should be better..

Performance wise i am not sure about the considerable performance difference in both the statements

though u can check the performance of these 2 statements in ST05.

Re: WHERE condition

Former Member — Thu, 13 Dec 2007 22:39:30 GMT

Naveen - the second select uses the same three key fields as the first (the full primary key). It also uses another non-key field. I would expect that this would place a very small extra load on the database server, but this should be outweighed by the fact that the database will be returning fewer records.

Additionally, less work will have to be done on the application server side because the records already have the extra filter applied.

Rob

Re: WHERE condition

Former Member — Thu, 13 Dec 2007 22:42:20 GMT

Yup exacty Rob.... I didn't see 1st key field in the second select statement... my mistake

Re: WHERE condition

Clemenss — Thu, 13 Dec 2007 22:56:52 GMT

Hi K N,

almost everything has been said.

But you are still better selecting not from BSEG but from BSAS if you are lookin for cleared documents (AUGBL filled). BSAS is the secondary index table. Less fields, löess records. And if you have the GL account, you can make good better of the index.

Regards,

Clemens

Re: WHERE condition

Former Member — Thu, 13 Dec 2007 23:15:55 GMT

Hi,

i have some 140 million records in BSEG where i need to pick AUGBL field.

So, which method u suggest ?

using the second statement above ?

or Creating indexes on BSIK and BSAK tables ?

Re: WHERE condition

Former Member — Fri, 14 Dec 2007 03:36:53 GMT

Why would you want to create a secondary index on BSIK and BSAK for a single SELECT? Either of your other two solutions is better.

One or the other of the first two will be best (by a small amount), but the only way to say for sure is for <u>you</u> to try both with a variety of SELECT options.

Having said that, there already is a secondaryindex on both BSIK and BSAK for BUKRS, BELNR and GJAHR.

Rob

Message was edited by:

Rob Burbank

Re: WHERE condition

Former Member — Fri, 14 Dec 2007 04:21:17 GMT

Thanks everyone for sharing their views.

I have tried the second query and have no prolems so far.

Re: WHERE condition

Clemenss — Fri, 14 Dec 2007 11:57:04 GMT

Hi Rob,

no, you do not have to create a secondary index. BSIS and BSAS are secondary index tables for BKPF/BSEG. They have far less entries and far less fields than BKPF/BSEG. Access using those tables is faster even if you don't supply full primary key.

It is almost always better to use the secondary index tables before or instead of accessing BKPF/BSEG.

BSID Accounting- Secondary Index for Customers

BSIK Accounting- Secondary Index for Vendors

BSIM Secondary Index, Documents for Material

BSIS Accounting- Secondary Index for G/L Accounts

BSAD Accounting- Secondary Index for Customers (Cleared Items)

BSAK Accounting- Secondary Index for Vendors (Cleared Items)

BSAS Accounting- Secondary Index for G/L Accounts (Cleared Items)

There is just one minor precondition: You got to know what you want

Regards,

Clemens

Re: WHERE condition

Former Member — Fri, 14 Dec 2007 14:46:37 GMT

Clemens - are you saying that it would be faster to go to BSIS or BSAS using BUKRS, BELNR and GJAHR than it would be to go to BSEG with the same fields, even though there is no secondary index on those fields in BSIS or BSAS?

Rob

Re: WHERE condition

Clemenss — Fri, 14 Dec 2007 15:06:52 GMT

Rob,

yes, roughly:

BSIS and BSAS have primary key fields

BUKRS

HKONT

AUGDT

AUGBL

ZUONR

GJAHR

BELNR

BUZEI

For BSIS, AUGDT and AUGBL are always initial.

Sure you are better off if you can supply the GL account in HKONT. But even if not, BSIS/BSAS are transparent tables with far less records and much better access.

Regards,

Clemens

Re: WHERE condition

Former Member — Fri, 14 Dec 2007 15:10:36 GMT

Clemens - have you actually tried this?

Rob

Re: WHERE condition

Former Member — Fri, 14 Dec 2007 20:15:52 GMT

I wrote a small program that compares the various suggestions:

REPORT ztest MESSAGE-ID 00.

TABLES: bkpf, bsis, bsas, bseg.

DATA: bkpf_int TYPE TABLE OF bkpf,
      bseg_int TYPE TABLE OF bseg,
      bsis_int TYPE TABLE OF bsis,
      bsik_int TYPE TABLE OF bsik.

DATA: start     TYPE i,
      end       TYPE i,
      dif       TYPE i.

SELECT * FROM bkpf INTO TABLE bkpf_int UP TO 1000 ROWS.

DO 5 TIMES.
  GET RUN TIME FIELD start.

  SELECT * FROM bsik INTO TABLE bsik_int
    FOR ALL ENTRIES IN bkpf_int
    WHERE bukrs = bkpf_int-bukrs
      AND belnr = bkpf_int-belnr
      AND gjahr = bkpf_int-gjahr.

  SELECT * FROM bsak APPENDING TABLE bsik_int
    FOR ALL ENTRIES IN bkpf_int
    WHERE bukrs = bkpf_int-bukrs
      AND belnr = bkpf_int-belnr
      AND gjahr = bkpf_int-gjahr.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for indexed SELECT from BSIK/BSAK  :', dif,
              'microseconds'.

  GET RUN TIME FIELD start.

  SELECT * FROM bsis INTO TABLE bsis_int
    FOR ALL ENTRIES IN bkpf_int
    WHERE bukrs = bkpf_int-bukrs
      AND belnr = bkpf_int-belnr
      AND gjahr = bkpf_int-gjahr.

  SELECT * FROM bsas APPENDING TABLE bsis_int
    FOR ALL ENTRIES IN bkpf_int
    WHERE bukrs = bkpf_int-bukrs
      AND belnr = bkpf_int-belnr
      AND gjahr = bkpf_int-gjahr.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for unindexed SELECT from BSIS/BSAS:', dif,
              'microseconds'.

  GET RUN TIME FIELD start.

  SELECT * FROM bseg INTO TABLE bseg_int
    FOR ALL ENTRIES IN bkpf_int
    WHERE bukrs = bkpf_int-bukrs
      AND belnr = bkpf_int-belnr
      AND gjahr = bkpf_int-gjahr.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for indexed SELECT from BSEG       :', dif,
              'microseconds'.

  GET RUN TIME FIELD start.

  SELECT * FROM bseg INTO TABLE bseg_int
    FOR ALL ENTRIES IN bkpf_int
    WHERE bukrs = bkpf_int-bukrs
      AND belnr = bkpf_int-belnr
      AND gjahr = bkpf_int-gjahr
      AND koart = 'K'.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for indexed SELECT from BSEG       :', dif,
              'microseconds (includes KOART)'.

  SKIP 1.
ENDDO.

The SELECT against BSEG using KOART generally gave the best time. The unindexed SELECT against BSIS and BSAS was always the worst. But I don't think it matters, because the records that he wants won't be in these tables.

Rob

Re: WHERE condition

Clemenss — Sat, 15 Dec 2007 16:36:48 GMT

Rob,

thanks for your efforts.

Maybe my suggestion is not good in any case. We had a similar task where we know the GL account number - this helped for good results accessing BSIS/BSAS.

And we have an extremely slow system here and the number of BSEG data records is huge.

"i have some 140 million records in BSEG where i need to pick AUGBL field."

Your test program did not run against 140 million BSEG records, did it?

That's what you have in BSAS primary key.

Cleared items will almost always get posted to GL accounts. You may identify the relevant GL accounts and enhance the selection.

Regards,

Clemens

Re: WHERE condition

Former Member — Sat, 15 Dec 2007 19:19:46 GMT

A few things:

In database access, the most important thing to do is to use an index effectively. There is no way that going to BSIS and BSAS without an index would be faster than going to BSEG with the key.

As the number of records in BSEG grows, the number of records in BSIS/BSAS will grow linearly, so trying to go to these tables will get worse with more records, not better.

The original poster clearly said that he wanted to SELECT with KOART = 'K'. This means that he wants vendor accounts, not GL accounts. There is a secondary index in BSIK/BSAK on BUKRS, BELNR and GJAHR so that would be better than going to BSIS and BSAS with these fields. But you'll notice that I included this table in the sample program that I wrote and even in this case going to BSEG was faster.

It's a common misconception that going to BSEG is slow.

Rob