Performance: Open SQL vs. Native SQL (Oracle)

[Former Member]

Hi everybody,

I have an interesting issue here. For a DB selection I use an Open SQL query from a table view into an internal table. It works fine, but the performance is not very well. The SELECT uses LIKE and wildcards (%) to search for customer master data (names and address fields).

Because of the bad performance I made some tests in transaction DB02 with native SQL, but exactly the same SELECT structure. It looks like this:

  SELECT *
    FROM zzrm_cust_s_hlp
   WHERE client = 100
     AND mc_name1      LIKE '<name>%'
     AND mc_name2      LIKE '<name>%'
     AND valid_from    <= <timestamp>
     AND valid_to      >= <timestamp>

Ok, now I tried exactly the same SELECT statement with the same data to search for (<name> and <timestamp) with Open SQL and Native SQL. The Difference is quite suprising, the Native SQL query is about 5-10 times faster (arount 1 sec) than the Open SQL query (around 5-10 sec). Even with the LIKE keywords and the wildcards.

Any ideas what could be the problem with the Open SQL query?

And: what can I do to achive the same performance as with the Native SQL query?

Kind regards and thanks in advance for any help,

Matthias

[Former Member]

If you have not done so, try using a SQL trace using ST05, then view the explain plan. It will show you how the query is being translated to native SQL.

There will be some overhead with Open SQL, since it has a layer between itself and the database - so it should be expected that it would be a bit slower. This also depends on how the query is being used. Are you using ENDSELECT, or using an array fetch (into table)? Array fetch will give you better DB performance, but increase the load on the application server since it needs to do the internal table handling.

[Former Member]

No problem exists with the OpenSQL... it is simply slower b/c it must be converted INTO native SQL before the statement is passed into the DB server.

Native SQL from an ABAP program is not converted (since it is already native) - hence it skips the pre-processing of converting.

OpenSQL's intention is Database portability. Meaning today, a company can be running on Oracle with OpenSQL in their ABAP. Then tomorrow, the company decides to change to SQL Server - NONE of the OpenSQL needs to be re-written/re-tested BECAUSE of the conversaion to Native SQL prior to calling the DB server.

If you have Native SQL in your code (and that native SQL is for an Oracle DB) and tomorrow, you switch to SQl Server - you will bery liekly need to re-visit and re-write that SQL to run against an SQL Server DB.

Hope that helps

[Former Member]

I have some doubt that you measurements are reliable, please run the SQL trace several times

and check the times in the SQL summary (left drop down box, summary by SQL statements).

How many records are selected? You should get much smaller numbers in both cases and

the should be much closer together. You should not check response times but database times.

Siegfried

[Former Member]

Hi all,

I have already traced the query in ST05, but I am not really smarter now. My idea was that since it's working quite fine in DB02 (copied SQL query 1:1 from ABAP source), the problem must be the Open SQL. Now I wrote a small test report with this SELECT statement two times. Once with Open SQL and the second time with Native SQL, both execution times are meassured.

The interesting thing now is: When I run the report the first time with some random values, then both - open and native SQL - are running slow. But when I return to selection screen and re-run (even with other random values!) the open SQL is still slow, but the native SQL select needs less than one second.

I am still not smarter though

Interesting thing is still the fact that when I copy the SELECT 1:1 to DB02 and run it there, it never needs more than 1 sec, regardless of the values I search for. I though maybe the problem could be the fact that I SELECT from a view (three tables), but I use the same view for selection in DB02, so this does not seems to be the problem either.

I am really out of ideas now.

Maybe you?

Thanks for your help!

[Former Member]

Matthias,

Not sure if you saw my explanation above.

OpenSQL is actual a macro language that needs to be converted into the native SQL for your current DB vendor and version.

B/c of this fact, OpenSQL will be slighter slower. BUT it is easily portable as explained above.

Does that help?

[Former Member]

Matthias,

Here is a link to SAP doc that details things. It speaks to the bypassing of the R/3 database interface when using Native.

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3969358411d1829f0000e829fbfe/frameset.htm

[Former Member]

Another one to review:

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3969358411d1829f0000e829fbfe/frameset.htm

[Former Member]

Hi John,

I do know about the purpose of Open SQL and that it has to be converted into Native SQL of the DBMS. My point is that when I run the SQL query via Open SQL in an ABAP report it takes about 30 seconds. When I copy the same SQL query 1:1 to the DB02 transaction and execute it there directly with exactly the same values for the WHERE clause, the SELECT takes less than one second.

That means the Open SQL version is more than 30 times (!!) slower than the Native SQL version. I am sure that conversion in ABAP from Open to Native SQL does not reason that huge difference, don't you think?

I even tried to use Native SQL in my report for the selction, but it still does require that long as in Open SQL. So my question is: what does the DB02 transaction so that is that much faster than the ABAP version?

Kind regards, Matthias

[Former Member]

Do a performance trace (ST05) on the two SELECTs and then use the EXPLAIN function to examine the relative costs.

Rob

[Former Member]

Hi Rob,

I did a SQL trace for both SELECT statements, now I am suprised

The SQL query entered into transaction DB02 has an estimated cost of 194, whereas the SQL query in ABAP has an estimated cost of 15711. Ok, now I know why it takes so much longer. But: how can I read this numbers, how to find out what I can change in order to achive a good performance?

Kind regards, Matthias

[Former Member]

The explain should show the actual SQL statements that were executed on the database. Would you please post both of these?

Rob

[Former Member]

Hi Rob,

this is the SQL statement from ABAP / Open SQL (slow):

SELECT
  *
FROM
  zzrm_cust_s_hlp
WHERE
  client = 100 AND mc_name1 LIKE 'WAGNER%' AND mc_city1 LIKE 'MÜNCHENSTEIN%'
  AND valid_from <= 20080227093243.0000000 AND valid_to >= 20080227093243.0000000

and this is the SQL statement from transaction DB02 (fast):

SELECT
  *
FROM
  "ZZRM_CUST_S_HLP"
WHERE
  "CLIENT" = :A0 AND "MC_NAME1" LIKE :A1 AND "MC_CITY1" LIKE :A2 AND "VALID_FROM" <= :A3 AND
  "VALID_TO" >= :A4

Kind regards, Matthias

[Former Member]

Unfortunately you can't really understand the costs, onyl there relation, and the most simple cost of a SELECT SINGLE with full primary key, cost = 1.

You need to understand the explain which is below the costs. There must be a difference if the costs are so different, different index, index access or full table scan, less key fields or ...?

Siegfried

[Former Member]

Hi Siegfried,

that means that Open SQL and Native SQL is definitely transformed before it's passed to the DBMS.

Very strang, I have to look how the DB02 transaction passes the query without transforming to DBMS.

Kind regards, Matthias

[Former Member]

Hi Matthias,

what you show are the statements not the explain. The statements should be identical,

and I think you mixed them up. The ABAP Open SQL Statement has the placeholders A1, A2 etc. not the native SQL. See buttom 'details' for the values of A1, A2 etc.

Anyway what you must compare is the execution plan in the explain, as I said below the costs not above.

Siegfried

[Former Member]

Ok, here is the the SQL explaination from the DB02 query:

SELECT STATEMENT ( Estimated Costs = 194 , Estimated #Rows = 1 )

       9 COUNT STOPKEY
         Filter Predicates

           8 NESTED LOOPS
             ( Estim. Costs = 193 , Estim. #Rows = 1 )
             Estim. CPU-Costs = 1,665,938 Estim. IO-Costs = 193

               5 NESTED LOOPS
                 ( Estim. Costs = 144 , Estim. #Rows = 98 )
                 Estim. CPU-Costs = 1,162,148 Estim. IO-Costs = 144

                   2 TABLE ACCESS BY INDEX ROWID BUT000
                     ( Estim. Costs = 51 , Estim. #Rows = 93 )
                     Estim. CPU-Costs = 468,764 Estim. IO-Costs = 51
                     Filter Predicates

                       1 INDEX SKIP SCAN BUT000~NAM
                         ( Estim. Costs = 6 , Estim. #Rows = 93 )
                         Search Columns: 1
                         Estim. CPU-Costs = 59,542 Estim. IO-Costs = 6
                         Access Predicates Filter Predicates

                   4 TABLE ACCESS BY INDEX ROWID BUT020
                     ( Estim. Costs = 1 , Estim. #Rows = 1 )
                     Estim. CPU-Costs = 7,456 Estim. IO-Costs = 1
                     Filter Predicates

                       3 INDEX RANGE SCAN BUT020~0
                         ( Estim. Costs = 1 , Estim. #Rows = 1 )
                         Search Columns: 2
                         Estim. CPU-Costs = 3,661 Estim. IO-Costs = 1
                         Access Predicates

               7 TABLE ACCESS BY INDEX ROWID ADRC
                 ( Estim. Costs = 1 , Estim. #Rows = 1 )
                 Estim. CPU-Costs = 5,141 Estim. IO-Costs = 1
                 Filter Predicates

                   6 INDEX UNIQUE SCAN ADRC~0
                     Search Columns: 4
                     Estim. CPU-Costs = 525 Estim. IO-Costs = 0
                     Access Predicates

And this is the one from the Open SQL query in ABAP:

SELECT STATEMENT ( Estimated Costs = 15,711 , Estimated #Rows = 29 )

       7 NESTED LOOPS
         ( Estim. Costs = 15,710 , Estim. #Rows = 29 )
         Estim. CPU-Costs = 3,021,708,117 Estim. IO-Costs = 15,482

           4 NESTED LOOPS
             ( Estim. Costs = 15,411 , Estim. #Rows = 598 )
             Estim. CPU-Costs = 3,018,711,707 Estim. IO-Costs = 15,183

               1 TABLE ACCESS FULL BUT020
                 ( Estim. Costs = 9,431 , Estim. #Rows = 11,951 )
                 Estim. CPU-Costs = 2,959,067,612 Estim. IO-Costs = 9,207
                 Filter Predicates
               3 TABLE ACCESS BY INDEX ROWID ADRC
                 ( Estim. Costs = 1 , Estim. #Rows = 1 )
                 Estim. CPU-Costs = 4,991 Estim. IO-Costs = 1
                 Filter Predicates

                   2 INDEX UNIQUE SCAN ADRC~0
                     Search Columns: 4
                     Estim. CPU-Costs = 525 Estim. IO-Costs = 0
                     Access Predicates

           6 TABLE ACCESS BY INDEX ROWID BUT000
             ( Estim. Costs = 1 , Estim. #Rows = 1 )
             Estim. CPU-Costs = 5,011 Estim. IO-Costs = 1
             Filter Predicates

               5 INDEX UNIQUE SCAN BUT000~0
                 Search Columns: 2
                 Estim. CPU-Costs = 525 Estim. IO-Costs = 0
                 Access Predicates

Of course I can see the difference.

But since the statements are identical, I don't understand why this difference exists

Thanks for your help!

Kind regards, Matthias

[Former Member]

BINGO!!!

The OpenSQL is 30 times longer b/c it is returning 28 more records.

Estimated #Rows = 29 in the OpenSQL

Estimated #Rows = 1 in the DB02 example.

[Former Member]

Change your open SQL to this and see what happens:

SELECT *
   FROM zzrm_cust_s_hlp CLIENT SPECIFIED
   WHERE client = 100
     AND mc_name1      LIKE '<name>%'
     AND mc_name2      LIKE '<name>%'
     AND valid_from    <= <timestamp>
     AND valid_to      >= <timestamp>

Rob

[Former Member]

> The OpenSQL is 30 times longer b/c it is returning 28 more records.

> Estimated #Rows = 29 in the OpenSQL

> Estimated #Rows = 1 in the DB02 example.

No! It returns the same amount of data.

But the expectation are totally different, it expects more data to be returned.

The cost based optimizer comes to different results an two very different access path are taken, which explains the difference in processing time.

Siegfried

[Former Member]

Hi all,

@Rob: adding the CLIENT to the SELECT query did not improve the performance.

@Siegfried: I understand that there is a full table scan ("TABLE ACCESS FULL BUT020") in the Open SQL query. But since it's the same SQL statement, why could that be? And more important for me: How can I solve it?

Kind regards, Matthias

[Former Member]

As a test, can you try using standard SAP view V_BUT000_ALL in your SELECT rather than your custom one. You may have to change the fields somewhat, Bit I'd like to know if it's any better.

If it is better, you can check your view against the SAP views and make them consistent with them.

Rob

Edited by: Rob Burbank on Mar 3, 2008 2:14 PM

[Former Member]

Just for the records, I have figured it out with SAP support:

I had to add the following line to my Open SQL statement:

%_HINTS ORACLE '&SUBSTITUTE VALUES&'.

(see OSS hint # 129385)

Kind regards, Matthias