topic Re: Select endselect utilization of application server in Application Development and Automation Discussions

Select endselect utilization of application server

aarif_baig — Tue, 23 Jul 2013 03:08:31 GMT

Hi Abapers,

Let say suppose there is a program where in we have below scenario

SELECT.

manipulation of data and appending it to internal table ( Now at this point where is the load on application server or database )

ENDSELECT.

Any suggestion or comments.

Br,

Aarif Baig.

Re: Select endselect utilization of application server

arindam_m — Tue, 23 Jul 2013 03:23:11 GMT

Hi,

What do you mean by

where is the load

The SELECT should open up a DB connection and a LOOP when paired with ENDSELECT to select the DB entries. what ever is done in middle would be done while this connection still there with the DB and then it closes only after the ENDSELECT is executed.

So if you plan to do too many logic or Application layer logic in between that would keep the open DB access and introduce a delay.

Cheers,

Arindam

Re: Select endselect utilization of application server

Former Member — Tue, 23 Jul 2013 03:26:41 GMT

It will create a low performance if you do open DB connection between SELECT ... END SELECT statement. Avoid this statement to get better performance of your code.

Re: Select endselect utilization of application server

aarif_baig — Tue, 23 Jul 2013 04:22:00 GMT

Hi Arindam,

Where is the Load?

What I meant was that where exactly are we working at this point DB layer or application layer.

But do you think that will effect the overall time taken to execute a report because if we do the same processing on application layer it will take the same time to do the processing there

Re: Select endselect utilization of application server

arindam_m — Tue, 23 Jul 2013 04:30:14 GMT

Hi,

Not from a perspective of total time taken but its more about the resources blocked.

You could do it both ways. But there will be unnecessary blocking of the DB resources when you are doing a processing in the middle of DB access. Its advisable to finish the DB tasks then carry on with the application server. The only lag time might be the constant switching required to manage the whole execution.

Cheers,

Arindam

Re: Select endselect utilization of application server

Former Member — Tue, 23 Jul 2013 05:03:48 GMT

Hi,

When SELECT is used the load will be on DB server and the database table in the SELECT statement will be open until ENDSELECT gets executed.For manipulations of data with work areas and internal table inside SELECT and ENDSELECT the load will be on application server.

Re: Select endselect utilization of application server

former_member184455 — Tue, 23 Jul 2013 07:10:56 GMT

Hi Aarif,

in an ABAP system the ABAP work process is the user of the database. As long as there is no database commit (implicit, when the work process is rolled out at the end of an UI step or by an RFC - or explicit, by a COMMIT WORK), the work process won't 'release' the DB connection.

Therefore it is of no harm to use SELECT ... ENDSELECT with processing in between as compared to reading first everything from database into a internal table and then doing the processing. What might become critical are database changes that are done during a very long running transaction, since here Redo-Logs and DB-Locks might pile up.

Best Regards, Randolf

Re: Select endselect utilization of application server

aarif_baig — Tue, 23 Jul 2013 11:20:25 GMT

Hi Randolf,

"the work process won't 'release' the DB connection." that means the database resources will still remain allocated even if we don't use select endselect.

Re: Select endselect utilization of application server

Former Member — Tue, 23 Jul 2013 11:53:40 GMT

Hello Aarif Baig ,

For example if 3 entries satisfy our requirement using where class criteria in the table then

Select and endselect will hit the database 3 times , if we use " select into table " then it will hit database only once .

Re: Select endselect utilization of application server

aarif_baig — Tue, 23 Jul 2013 13:07:26 GMT

Hey Madhu,

it will not hit database thrice, please check a blog related to this on SCN I will try to find it and will update you

Re: Select endselect utilization of application server

former_member184455 — Tue, 23 Jul 2013 14:46:49 GMT

Hi Aarif,

Yes. A problem that could arise if you have a SELECT ... ENDSELECT running for hours (in this case you should think about packaging anyway) is that on database platforms that keep a before image (table content at moment of first SELECT) the redo logs could overflow when the current or other users modify the database table that is read.

From the application server point of view, the SELECT ... ENDSELECT is more memory-friendly, since it does not load so much data for processing.

Best Regards, Randolf