Complex queries in Open SQL

[Former Member]

Moved to performance forum by moderator

Hi Experts,

This is more of a discussion rather than a question. I would like to know the advantages of Open SQL. It prevents you from writing complex queries, the kind of queries that you can write in native sql.

The biggest disadvantage I feel is that you have to fetch data into internal tables and loop. This takes a lot of processing time when you have a report having a lot of lines. A complex query using complex joins and subqueries will always perform better than having to loop and process data and simple things like generating sequence.

The best thing about open sql is the way it handles select options.

I would like to get your opinions and suggestions how to write complex queries in ABAP with some code snippets if possible.

Warm Regards,

Abdullah

Edited by: Matt on Jan 21, 2009 6:54 PM

[Former Member]

hi,

Refer to this link [Open SQL vs Native SQL|http://www.itcserver.com/blog/2006/06/26/open-sql-vs-native-sql/]

[Former Member]

can I get a link to any site that explains about writing complex queries in open sql or any book available in the market on the same?

[ThomasZloch]

> The biggest disadvantage I feel is that you have to fetch data into internal tables and loop. This takes a lot of processing time when you have a report having a lot of lines. A complex query using complex joins and subqueries will always perform better than having to loop and process data and simple things like generating sequence.

Well, you don't have to work with internal tables, you can as well use SELECT ... ENDSELECT into a workarea and write your lines inside the loop.

> The best thing about open sql is the way it handles select options.

It's a nice feature, but even better is that as an ABAP developer you (normally) need not care about the details of the underlying database.

Thomas

[Former Member]

Hi

I've often used the OPEN SQL only when a very large number of records could be extracted from a database, because it can split the data to be extracted in many packages and then works with a single package.

This solution can often improve the performance: I corrected a job it took about one day,now it takes only 40 minutes.

Max

[Former Member]

select - endselect will query the database repeatedly and wont be any different from using a loop on internal table. the performance might still degrade. A complex query is still a single query.

[ThomasZloch]

> select - endselect will query the database repeatedly and wont be any different from using a loop on internal table.

That's not true. See the last reply in the following thread. Or can you back up your statement with text references or own runtime measurements?

I have already asked to have your thread moved to that forum, looking forward to the discussions there.

Thomas

[Former Member]

From the help:

"All Native SQL statements bypass SAP buffering. Automatic client handling is not performed."

However, native SQL allows you to do some database specific operations (like translating to upper case).

Rob

[Former Member]

I don't understand why SAP hasn't tried to meet in the middle in terms of certain runtime functionality

Most databases use substring, case, if/then evaluation, etc. So why can't SAP implement a substring, uppercase, etc, that can be translated in the DBI to the native equivalent?

A lot of coding could be avoided if SAP would go in this direction.

I (I'm dating myself) coded COBOL in the early 90s. There are constructs in COBOL, which is probably older than most people in this forum, that are more powerful than ABAP. For example, REDEFINES, Structure levels.

[Former Member]

>

> Most databases use ...

I think open SQL has to be all things to all database platforms. If any platform doesn't support a construct, then it can't be used in open SQL.

Rob

[Former Member]

Right. But my point is if Oracle uses (for example) UPPER() and DB2 uses TO_UPPER(), why can't the DBI translate an operator in OpenSQL its the native equivalent? That seems pretty easy to do.

ABAP's database interface is about the weakest I've ever worked with.

[Former Member]

Well, my point was - what if any platform has no upper function at all?

Rob

[Former Member]

Agreed. They would have to be common across all platforms.

[Former Member]

> select - endselect will query the database repeatedly and wont be any different from using a loop on

> internal table.

that is a very common misunderstanding, but not true, it uses the arry interface, but gives you the possibility to react on every line. But interaction with DB is in blocks.

The biggest advantage of Open SQL is the implementation of the table buffer, every Open SQL Statement checks the buffers first.

Other advantage, all statements changing table definitions are not allowed.

And of course, as already said, it is a set of commands available on all DB platforms certified for SAP software, i.e. IBM, Oracle, Max DB and Microsoft.

Complex queries, even with the available joins you can write very very complex queries ... sometimes too complex. I see not advantage for a competetion of writing the most complex statement

Siegfried

[Former Member]

thanks a lot every one. The best option here I feel is to use native SQL for my scenario.