Exist general ABAP-specific tips associated with performance of large SELECT queries?

Particularly, can you really close for good the question of FOR ALL ENTRIES IN versus JOIN?

A couple of (pretty much) ABAP-specific hints:

Avoid Choose * where it isn't needed, attempt to choose just the fields which are needed. Reason: Every value may be planned several occasions throughout the procedure (DB Disk --> DB Memory --> Network --> DB Driver --> ABAP internal). It's not hard to save the CPU cycles if you do not require the fields anyway. Be cautious should you Choose * a table that consists of BLOB fields like STRING, this could totally kill your DB performance since the blob contents are often saved on different pages.

Don't Choose ... ENDSELECT for promising small to medium result sets, use Choose ... INTO TABLE rather. Reason: Choose ... INTO TABLE works just one fetch and does not keep your cursor open while Choose ... ENDSELECT will typically fetch just one row for each loop iteration.

For big result sets, make use of a cursor as well as an internal table. Reason: Just like above, and you will do not eat up an excessive amount of heap space.

Don't ORDER BY, use SORT rather. Reason: Better scalability from the application server.

Be cautious with nested Choose claims. While they may be very handy for small 'inner result sets', they're an enormous performance hog when the nested query returns a sizable result set.

Measure, Measure, Measure Never assume anything if you are concerned about performance. Produce a representative group of test data and run tests for various implementations. Learn to use ST05 and SM30.

There will not be a method to close your next question "for good". To begin with, For Those Records IN 'joins' a database table as well as an internal (memory) table while JOIN only works on database tables. Because the database knows nothing concerning the internal ABAP memory, the For Those Records IN statement is going to be changed to some WHERE claims - just try to make use of the ST05 to follow this. Second, you cannot add values in the second table when utilizing For Those Records IN. Third, remember that For Those Records IN always suggests DISTINCT. You will find a couple of other issues - make sure to consult the on-line ABAP reference, they all are listed there.

If the amount of records within the second table is small, both claims ought to be pretty much equal in performance - the database optimizer must preselect all values in the second table and employ a wise joining formula to filter with the first table. My recommendation: Go to whichever seamless comfort, create tweak your code to illegibility.

If the amount of records within the second table surpasses a particular value, Bad Things [TM] happen with For Those Records IN - the contents on the table are split up into multiple sets, then your totally changed (see above) and re-run for every set.

It's my job to stick to the rules mentioned within this pdf from SAP: "Efficient Database Programming with ABAP" It shows lots of tips in optimizing queries.

Regards

You will find also quite extensive tips and hints in transaction SE30. It even enables you (based on authorisations) to create code clips of your &lifier measure it.

Regrettably we can not close the "for those records" versus join debate because it is very determined by the way your landscape is to establish, wich database server you're using, the efficiency of the table indexes etc.

The simplistic response is allow the DB server do whenever possible. For that "for those records" versus join question what this means is join. Except every experienced ABAP programmer recognizes that it's rarely that easy. You need to try different situations and measure like vwegert stated. Keep in mind to measure inside your live system too, as sometimes the hardware configuration or dataset is considerably dissimilar to have entirely spun sentences inside your live system than test.

This should never be completely clarified.

ABAP statement for being able to access database is construed several occasions by different aspects of whole system (SAP and DB). Behavior of every component is dependent from component itself, its version and configurations. Primary a part of interpretation is completed in DB adapter on SAP side.

The only real viable method for reaching maximum performance is measurement on particular system (SAP version and DB vendor and version).