I have lakhs of records in my database.When I am using the SAP standard function module C1F2_SUBSTANCES_READ for retreiving data it is taking time.I know all the data base tables .Shall I use to retreive data using select statements instead of the mentioned SAP standard function module.Could any one please explain which is best method to retrieve huge data and also explain reason?
Thanks and Regards,
If your Sure about exactly what data you want to fetch from the database, I would follow the below process.
1) I would search if there is any standard function module or Bapi to do this... The reason why i do this is becuase as SAP has created those standard function modules or Bapi, the first advantage would be the program has good performance ( Moreever it also depends on how do you handle that in your program ).
Moreover it handles varies business functionalities and authorizatiions etc..
2) If i dont find any standard function module or Bapi then if the data what you are selecting from database if it is used multiple times then i would create a zbapi or Zfunctionmodule and inlude my code in it... ( Here this custom function module can be used in many program : ie : modulerization ).
First of all, a function module may process the data and transform it into some other form that is stored in a database, it may perform authority checks and many more activities, however the overhead may be high. Therefore if you really do know all the required tables and information you need is available there, you may use a select statement. However, select statements must be written appropriately, for example the order of fields in a where clause is vital to determine an index to access a database table; access without the index uses linear search which is not effective. In case of large datasets, one huge select statement may lead to a situtaion where all the available memory is used and then it finishes with a short dump.