topic Re: Complex queries in Open SQL in Application Development and Automation Discussions

Complex queries in Open SQL

Former Member — Wed, 21 Jan 2009 08:49:53 GMT

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

Re: Complex queries in Open SQL

Former Member — Wed, 21 Jan 2009 08:55:17 GMT

hi,

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

Re: Complex queries in Open SQL

Former Member — Wed, 21 Jan 2009 09:06:09 GMT

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?

Re: Complex queries in Open SQL

ThomasZloch — Wed, 21 Jan 2009 09:18:28 GMT

> 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

Re: Complex queries in Open SQL

Former Member — Wed, 21 Jan 2009 09:28:18 GMT

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

Re: Complex queries in Open SQL

Former Member — Wed, 21 Jan 2009 09:36:47 GMT

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.

Re: Complex queries in Open SQL

ThomasZloch — Wed, 21 Jan 2009 09:54:17 GMT

> 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

Re: Complex queries in Open SQL

Former Member — Wed, 21 Jan 2009 14:45:39 GMT

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

Re: Complex queries in Open SQL

Former Member — Wed, 21 Jan 2009 15:08:04 GMT

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.

Re: Complex queries in Open SQL

Former Member — Wed, 21 Jan 2009 15:15:18 GMT


> 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

Re: Complex queries in Open SQL

Former Member — Wed, 21 Jan 2009 15:29:41 GMT

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.

Re: Complex queries in Open SQL

Former Member — Wed, 21 Jan 2009 15:32:06 GMT

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

Rob

Re: Complex queries in Open SQL

Former Member — Wed, 21 Jan 2009 15:34:53 GMT

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

Re: Complex queries in Open SQL

Former Member — Thu, 22 Jan 2009 09:32:45 GMT

> 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

Re: Complex queries in Open SQL

Former Member — Thu, 12 Mar 2009 13:58:04 GMT

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