Just for Understanding the Where Condition

[Former Member]

Taking an example of EKBE Table consider the sequence of fields in which they are....

my query is if i fetch data like

<b>1.</b>

select fields into table itab from ekbe where vgabe = '1' and bewtp= 'E' and bwart = '105' and budat in s_budat and werks in s_werks.

now seeing this query this doesn't has any index now supposing i have index on BUDAT, now if fetch all data from EKBE based on BUDAT and then deleting the data from internal table which are not required like this

<b>2.</b>

select fields into itab from ekbe where budat in s_budat.
delete itab where vgabe ne '1'.
delete itab where bewtp ne 'E'.
delete itab where werks ne s_werks.

which will be faster as per performance.... or i have to give the where conditions as per the Sequence of the fields in the Table.

ur views are welcomed this is just for clearing the concept related to some performance issues in my Reports.

thanx in advance

abhishek suppal

[Former Member]

first one should be better as it will reduce the load on database by filtering data from the where condition.

[Former Member]

Hi abhishek,

1. have to give the where conditions as per the Sequence of the fields in the Tabl

ABSOLUTELY NOT REQUIRED.

We can give in any sequence.

-

---

your queries / requirement

1 and 2 don't match.

1 is selecting records

2 is deleting records

why do u want to delete ?

-

---

if u want to delete,

then don't select only in the first place !

-

---

Otherwise, doing operations

at the internal table level, is more faster,

bcos

a) working on internal tables

is done on application server (not on db server)

and hence, we don't tax the db server.

hence, it is more faster !

regards,

amit m.

Message was edited by: Amit Mittal

[Former Member]

Amit

u have told me that 2nd option will be faster

but if i fetch data based on BUDAT then it fetches suppose

10,000 records, and if i put the other conditions it fetches only 5000 records, then how can it be like this

that the 2nd option will be faster aggreed that the index is available on BUDAT but it fetches more records then reqd....

it may be the typing error for the requirements,

i hope u understand the thing wat i want to know. pl. ignore that part....

abhishek suppal

Message was edited by: Abhishek Suppal

[Former Member]

Hi again,

1. Im sorry for the misunderstanding.

2. Once again see my EDITED Message

where i have said 1st is better.

3. BUT

It is seen that

sometimes 1 is fast,

and sometimes 2 is fast.

4. Theoretical and common sense approach

says that

we should filter all data

at the SQL level only !

5. Performance is based upon so many factors

a) table records

b) indexes available

c) index avaialable on All WHERE fields

6. WRT to point 3,

if C is not available,

sometimes option 2 gets more faster

depending upon the number of records.

7. Bottom line is that

OPTION 1 is EFFICIENT / BETTER / FASTER

regards,

amit m.

[Former Member]

Hi Abhishek,

You can give all criterias in select statement itself.. i.e.

select fields into itab from ekbe where budat in s_budat and vgabe = '1' and bewtp= 'E' and bwart = '105' and werks in s_werks.

If you have budat as index then you should put it first and then other fields.

Regards,

Komal.

[Former Member]

Hi Abhishek /Amit,

Actually you can not say which one will be better looking at the query alone. It will also depend on the type of buffering used by the table. e.g if the table is fully buffered .. then the entire table will anyways be buffered into the application server, in which case there will not be multiple access to db server and Query1 can be faster. (This is not a absolute reasoning, other factors may change the scenario)

while if u see at the query level Query 1 will lead to multiple access to DB server as mentioned by Amit and thus the performance will not be as good as second one , ( in case of other buffering types )

1. Why query 1 will lead to multiple access is becasue this query will be broken down into separate multiple queries becasue of "in"

Also the extent of filtering provided by the different where clauses will effect the choice between the two queries.

In conclusion u can decide the query performance only after anlysing the amount of data fetched by

"select fields into itab from ekbe where budat in s_budat"

and depending upon the buffering type and amount of data fetched, u will have to decide which one is optimum for ur requirment

PS :Amit if u note the second query is not a deletion what he is doing is to fetched all the records based on BUDAT and then remove all the records which does not match the other where clause, thus providing the same output.

regards,

Sumeet Mishra