topic Re: SQL performance in Application Development and Automation Discussions

SQL performance

Former Member — Tue, 27 Dec 2005 02:07:55 GMT

Hi,

A: SELECT vbeln parvw kunnr

INTO TABLE gt_vbpa

FROM vbpa

FOR ALL ENTRIES IN gt_vbak

WHERE ( vbeln = gt_vbak-vbeln AND parvw = 'AG')

or ( vbeln = gt_vbak-vbeln and parvw = RE )

SELECT vbeln parvw kunnr

INTO TABLE gt_vbpa

FROM vbpa

FOR ALL ENTRIES IN gt_vbak

WHERE vbeln = gt_vbak-vbeln AND

(parvw = 'AG' or parvw = 'RE' )

I have two problems:

1. TO the two SQLs , Do i need create index including vbeln and parvw for best performance, why?.

2. Provided i have created a index including vbeln and parvw . What's difference between A and B ? why ?

Thanks for your help in advance.

Re: SQL performance

Former Member — Tue, 27 Dec 2005 02:30:09 GMT

IF you have index including vbeln and parvw the first statement is better than second. Because first statement will use that index(internally) not the second.In the first statment all the fields of the search criteria are part of the index.

Dont forget to put check gt_vbak[] is initial statement before select statement.

Re: SQL performance

Former Member — Tue, 27 Dec 2005 02:31:22 GMT

VBPA table has the following keys: VBELN, POSNR and PARVW and the order of the key is

1- VBELN

2- POSNR

3- PARVW

So if you has to read this table for these key I think you don't need to create an index.

Infact if you read this table using some of the key you'll use the primary index.

The primary index is index created by default with key field, so if you read VBPA using:

- VBELN, POSNR and PARVW

- VBELN, PSONR

- VBELN

you'll use the primary index, because your reading of that table is always based on order of the keys

But if you read VBAP using: VBELN and PRVW you won't the primary index because you don't respect the order of the key is build.

So you could do this reading:

SELECT vbeln parvw kunnr INTO TABLE gt_vbpa

FROM vbpa

FOR ALL ENTRIES IN gt_vbak

WHERE vbeln = gt_vbak-vbeln.

DELETE gt_vbpa WHERE ( parvw <> 'AG' AND

PARVW <> 'RE' ).

If you have created an index for VBELN and PARVW, you always read VBPA with only those two fields to use that index.

Remember each index want a certain space on server, because every time it inserts a new record in VBPA, it'll insert a new record in the index. So you should create an index only if you really need it.

In your case I thin you can read the table using only VBELN and then delete the record where partner is not equal to AG and RE, you should have a good performance.

Max

Message was edited by: max bianchi

Re: SQL performance

Former Member — Tue, 27 Dec 2005 04:44:31 GMT

Hi guixin,

1. TO the two SQLs , Do i need create index including vbeln and parvw for best performance, why?.

Yes absolutely right.

If the table does not have index on parvw,

and this field is used in where conditions

in other programs also, it is advisable

to make index.

Since VBELN is already index,

we can make index only on parvw.

WHY ?

Because the database will take

less time to find/search thru all records.

Index is just like the 1page index

in a book so that we can locate the CHAPTER

very quickly, without having to scan

thru each page manually.

2.Provided i have created a index including vbeln and parvw . What's difference between A and B ? why ?

gt_vbak

what is the purpose of this internalt table ???

yes, the main purpose is only for providing

a set of vbeln (for which data is required)

PARVW comes in to picture AFTERWARDS.

Hence SECOND Sql is conceptually correct.

3. More over second sql will be more faster

because the conditions are more fixed.

ie. both conditions (),()

are not having AND in between,

only one is having.

I hope it helps.

regards,

amit m.

Re: SQL performance

Former Member — Tue, 27 Dec 2005 05:48:08 GMT

> Hi guixin,

> 1. TO the two SQLs , Do i need create index including

> vbeln and parvw for best performance, why?.

> Yes absolutely right.

> If the table does not have index on parvw,

> and this field is used in where conditions

> in other programs also, it is advisable

> to make index.

> Since VBELN is already index,

> we can make index only on parvw.

ABSOLUTELY NOT. Creating an index only on parvw might in fact be counterproductive as a number of apps rely on the already created indexes. This might throw a loop.

> WHY ?

> Because the database will take

> less time to find/search thru all records.

> Index is just like the 1page index

> in a book so that we can locate the CHAPTER

> very quickly, without having to scan

> thru each page manually.

> 2.Provided i have created a index including vbeln and

> parvw . What's difference between A and B ? why ?

> gt_vbak

> what is the purpose of this internalt table ???

> yes, the main purpose is only for providing

> a set of vbeln (for which data is required)

> PARVW comes in to picture AFTERWARDS.

> Hence SECOND Sql is conceptually correct.

Depending on the DB and the SQL optimizer the second SQL might be the winner. (grabs the first key in the index and evaluates the second). However you might as well see no or little difference: it really depends on the DB's SQL optimizer...

> 3. More over second sql will be more faster

> because the conditions are more fixed.

> ie. both conditions (),()

> are not having AND in between,

> only one is having.

> I hope it helps.

> regards,

> amit m.

Re: SQL performance

Former Member — Tue, 27 Dec 2005 07:53:20 GMT

Hi, F.J. Brandelik

I think you are right. thank you.

Re: SQL performance

Former Member — Tue, 27 Dec 2005 09:04:33 GMT

Hi f.j. brandelik and guixin,

1. u are absolutely right.

2. > Yes absolutely right.

> If the table does not have index on parvw,

> and this field is used in where conditions

> in other programs also, it is advisable

> to make index.

In fact, i had not seen the table vbpa

and not seen the index on 3 columns.

Thats the reason i had mentioned as above.

Moreover i also said that :

> If the table does not have index on parvw,

2. If i had already seen the table,

i definitely would not have mentioned it.

Thanks for corrections and a very good in-depth

answer !

regards,

amit m.

Re: SQL performance

Former Member — Tue, 27 Dec 2005 11:19:26 GMT

Hi, Amit Mittal

Thanks.