sort before search

[sujay_ranjan3]

Hi gurus,

What is the best way to use select statement to get most recently changed/created N number of documents from vbak.

I was wondering if there is a standard way to do this or else do a custom loop solution.

Thanks for any help.

[Former Member]

Try:

SELECT * FROM VBAK UP TO n ROWS
  INTO ...
  ORDER BY erdat DESCENDING.

Performance will be an issue.

Rob

[sujay_ranjan3]

Thanks Rob. is the order by before selection or after. Also would creating index help in speeding the process?

[Former Member]

the "order by" is part of the SELECT, not before or after.

I don't see how an index would help; you're selecting all records.

Rob

[Former Member]

CRM_2007 wrote:
is the order by before selection or after

Technically the ORDER BY is applied to the result set of the query (as part of the SELECT statement). Now [Open SQL|http://help.sap.com/abapdocu_70/en/ABENOPENSQL.htm] has the nice feature that your UP TO n ROWS clause is actually applied after executing ORDER BY, see the help on [UP TO n ROWS|http://help.sap.com/abapdocu_70/en/ABAPFROM_CLAUSE.htm#!ABAP_ADDITION_1@1@]:

If the addition ORDER BY is also specified, the rows of the hit list are sorted on the database server and only the number of sorted rows specified in n are passed to the result set. If the addition ORDER BY is not specified, n arbitrary rows that meet the WHERE condition are passed to the result set. 

In actual database SQL dialects (SAP calls this [native SQL|http://help.sap.com/abapdocu_70/en/ABENNATIVESQL.htm]) this can vary and then you would have to make sure to formulate your query in such a way that the result set is first sorted and then the row limit is applied (e.g. see explanation for [Oracle rownum queries|http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/pseudocolumns009.htm], where the naive query with limiting row numbers along with an order by clause does not work).

Rob wrote:
I don't see how an index would help; you're selecting all records.

Based on the requirement of the poster and your code example only the top N records should be returned (not <em>all records</em>). Thus ideally you want a query that does not read all data. I.e. with an index on creation date ERDAT, it would be trivial to find the N highest values (Oracle would do an <em>index range scan descending</em>). Otherwise the database is forced to do a full table scan. So index creation should improve your query (especially if you have lots of sales documents).

For retrieving the latest documents I'd also check out the other obvious alternative: Change documents stored in CDHDR (objectclas = 'VERKBELEG' for sales documents). However, you should check if CDHDR has an appropriate index supporting your query. Note that such an index is often (though for sure not always) present, because it's common feature among applications to be interested in the latest changes. However, even with an appropriate index it's worthwhile to run the statements through a SQL trace and compare the used resources (usually there's lots of change documents).

Cheers, harald