Questions on the most efficient select query..

[Former Member]

What is the difference between the two select query & please explain y is the 2nd select query more efficient??

DATA: MAX_MSGNR type t100-msgnr.

MAX_MSGNR = '000'.

SELECT * FROM T100 INTO T100_WA

WHERE SPRSL = 'D' AND

ARBGB = '00'.

CHECK: T100_WA-MSGNR > MAX_MSGNR.

MAX_MSGNR = T100_WA-MSGNR.

ENDSELECT.

-

---

DATA: MAX_MSGNR type t100-msgnr.

SELECT MAX( MSGNR ) FROM T100 INTO max_msgnr

WHERE SPRSL = 'D' AND

ARBGB = '00'.

[Former Member]

What is the difference between the two select query & please explain y is the 2nd select query more efficient??

DATA: MAX_MSGNR type t100-msgnr.

MAX_MSGNR = '000'.

SELECT * FROM T100 INTO T100_WA

WHERE SPRSL = 'D' AND

ARBGB = '00'.

CHECK: T100_WA-MSGNR > MAX_MSGNR.

MAX_MSGNR = T100_WA-MSGNR.

ENDSELECT.

-

---

DATA: MAX_MSGNR type t100-msgnr.

SELECT MAX( MSGNR ) FROM T100 INTO max_msgnr

WHERE SPRSL = 'D' AND

ARBGB = '00'.

[Former Member]

Hi,

The 2nd one is efficient,

Because

1>1st One have used select * that means it is selecting all the records from the table,

But

in 2nd one u r selecting just one field.

2>you have used select ....endselect in first one. It degrades your performance.

Regards

Sandipan

[Former Member]

hi the second one is good and it is picking one field so it is good than the all feilds..

for checking this ,

goto st05 and enter the name of the first program execute it .

goto st05 and enter the name of second program execute it .

u will watch the difference.

venkat.

[Former Member]

Hi,

If u r using first select query it will select the all values from the table after that it will compare but second one is very good one why becasuse secon query thay r using aggregate function so that it will work fast. performance wise it will best.

Regards,

S.Nehru

[Former Member]

The second select gets the result in an instance..

where as the first select .. endselect .. has to select and

compare both the values ...

[Former Member]

Ya sandipan that is true but in 2nd select query is it not necessary to write this condition:

CHECK: T100_WA-MSGNR > MAX_MSGNR.

if not then y???

Please explain...thanx

[Former Member]

Hi,

In second query max function takes care of that.It returns maximum value

from table which meets condition

Regards

[Former Member]

Hi,

the MAX function is the alternative for this.

[vinod_vemuru2]

Hi,

First never use Check statement in the Select.

Next thing Select ... end select.

Coming to ur question In case of first select it will fetch each record from the data base and compares that record value with the variable(MAX_MSGNR) and assigns the value to the variable. And this process will continue till the select reads all the records of the data base. Also these operations happens on the data base server. SO this query not only affect ur program but also others who is accessing the same data base.

Second query is most efficient because of the aggregate function MAX. Here it will fetch all the records in single go and checks the max value for that column using

optimising algorithm. So number of checks, assignments(single assignment) and fetches will be less compared to first select. This is the main reason. Hope this clarified ur doubt.

Another thing is in first query we are selecting all the fields where as in second we are selecting only one field(required)

Thanks,

Vinod.

Edited by: Vinod Kumar Vemuru on Mar 13, 2008 4:55 PM