Skip to Content
author's profile photo Former Member
Former Member

SELECT QUERIES IN ABAP.....

Can you give some idea about writing efficient select queries in ABAP?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 24, 2005 at 06:09 AM

    Hi kishan,

    i can give you some hint from one document, please reward point.

    The hit list is the set of rows determined by the WHERE clause. In other words, it is the set of table rows that the database system must take into account to respond to the query. Don’t confuse the hit list with the solution set, which is the set of rows (not necessarily from the table the query deals with) the database system returns to the program. Notice that in many cases the query’s hit list and the solution set are different.

    What is the reason behind this rule? A smaller hit list saves disk I/Os by the database system and often reduces network load. Both of these factors have a strong influence on your program’s runtime performance.

    How can you use ABAP to limit a hit list? First, use a WHERE clause whenever possible. Second, always try to describe the full search condition (not just part of it) within a single WHERE clause. ABAP provides a variety of arithmetical and logical operators to do this. Compared with your application program, the database server is usually the better place to evaluate search conditions.

    Let me give you an example: Run a query against the flight table to produce a list of all flight dates for Lufthansa flight 0300 in 1998. Air carrier and air connection are known and can easily be used within the WHERE clause of the SELECT statement. The relevant year is also given, but at first glance it is difficult to build an appropriate search condition. Faced with this situation, you could be tempted to use the ABAP CHECK statement.

    CHECK evaluates the subsequent logical expression. If it is true, the processing continues with the next statement. CHECK with a negative outcome terminates the current SELECT-ENDSELECT loop to start with the next row of the solution set, if there is one. Considering that fldateis a type D(YYYYMMDD) column of the sflighttable, a correct fragment of an ABAP program could look like this:

    SELECT fldate

    FROM sflight INTO xdate

    WHERE carrid = 'LH'

    AND connid = '0300'.

    CHECK xdate = '1998'.

    WRITE: / xdate.

    ENDSELECT.

    With ABAP in general, CHECK does the job of pattern matching in character variables. But in the WHERE clause of a SELECT statement, pattern matching also can be done easily by applying the LIKE operator. Within the search pattern, two characters have a particular meaning: “_” stands for any one character and “%” stands for any character string. With this in mind, a better solution would be:

    SELECT fldate

    FROM sflight INTO xdate

    WHERE carrid ='LH'

    AND connid ='0300'

    AND fldate LIKE '1998%'.

    WRITE: / xdate.

    ENDSELECT.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Rule 5: Remove the load from the database.

      In the R/3 server architecture, there is only one database server but any number of application servers. Although you cannot add extra database servers to your R/3 System, you can add more application servers to improve scalability. Therefore, you’ll want to remove any unnecessary load on your database. Here are some ideas to achieve this:

      • Avoid repeated reading of the same data.

      • Check whether a SELECT is really needed before an UPDATE is made.

      • Decide whether the data is needed in a certain order? If so, does the desired sorting correspond to the index used? If not, use the ABAP SORT statement instead of the ORDERBY clause of SELECT. This moves load from the database to the application servers.

      • Find out about “table buffering” on the R/3 application server in the R/3 library (use the Help Menu from the main menu).

  • Posted on Oct 24, 2005 at 06:10 AM

    Hi,

    here're some examples in se38:

    SE38 - environment - performance examples - SQL Interface

    Andreas

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 24, 2005 at 06:19 AM

    These are few options to make your query effecient

    1.Optimize the size of the result set (i.e., the number

    of rows that the statement returns) user <b>WHERE</b>.

    2. Optimize the amount of data to be transferred

    from the database to the application server.

    Use Select * only when you require all datas otherwise use SELECT field1 field2 ....

    3. Optimize the number of data transfers.

    Dont use ---

    SELECT SINGLE * FROM ztab

    WHERE f1 = fieldvalue.

    use like this--

    SELECT * FROM ztab INTO TABLE itab. and then

    READ TABLE ztab WITH KEY

    f1 = fieldvalue.

    4. Optimize the time required to search the database

    and retrieve the result set. Use <b>not eq to</b> rarely

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.