Query in SE16N seems to be much faster than a similar one in a programm

[andreas_singer]

Hi experts,

I have a program which performs a DB selection from a single table with many conditions in the where clause. The values of the selection tables in the conditions come directly from the selection screen of the program. The select statement looks roughly like this:

SELECT * FROM MY_TABLE INTO MY_INTERNAL_TABLE

WHERE field1 IN selection_table1

AND field2 IN selection_table2

AND field3 IN selection_table3

......

AND field60 IN selection_table60

AND field61 = (custom_condition1 OR custom_condition2 OR custom_condition3).

In the normal case, only a few of the selection fields are filled by the user and therefore most of the selection tables are inital. But I assume that the database optimizier can handle this and makes an optimized statement with only the necessary conditions (the ones that are used/filled) out of it.

However, if I open the same database table with the transaction SE16N and provide a few selection values there, I have the feeling that the data is retrieved CONSIDERABLY faster there (for example 8-10 seconds in SE16N compared to 30-40 seconds for the same amount of data via the DB selection in my ABAP program).

Can anyone of you think of any reasons why this could be? Is a large SELECT statement with many conditions/selection tables (however only a few of them filled) inefficient?

[ThomasZloch]

The first trip to the database is always slower than subsequent ones. Did you run your program again right after doing the SE16N select?

Thomas

[andreas_singer]

That's clear. I tested both selections on different days (so to say, each of them as a "first" run) when no data of this db-table was buffererd.

Edited by: Andreas Singer on Aug 7, 2008 3:43 PM

[Former Member]

Have you checked each using ST05 and looked at the EXPLAIN?

Rob

[Former Member]

Hi Andreas,

to be sure, you should compare the SQL statements which gets fired against the database instead of ABAP statements with several ranges. When you execute the same SQL statement several times in a row, than both statements should show similar runtimes.

But if you are using ranges, then the amount of possible SQL statements is nearly infinite (because of the statement length limit it could not be really infinite). Therefore small differences in your ranges could lead to even huge runtime differences. There are many layers of the application evolved processing your ABAP statement. The ABAP statement plus your range definition get be evaluated by the database independent database interface, then the result gets may be rewritten by the database dependent database interface which also gets rewritten by the database query rewrite. Last but not least the resulting statement gets evaluated by the database optimizer which calculates an execution plan. Every stage could be choose a different path when you have small changes in your range. Therefore it is not always predictable for ABAP developers how the runtime evolves by small differences on selection screen.

The bottom line is: To compare runtimes, you should compare SQL statements instead of ABAP statements.

Kind regards

Ralph

[former_member194613]

if there are 60 fields then the number of possible combinations is extremely large.

And only a very small portion can be supported by indexes.

You can compare only identical statements, i.e. exactly the same select-options.

Best use the ST05, and compare statement details and not ABAP source.

And compare also execution plan.

Siegfried