Progress
DataServer
for ORACLE Guide
SQL and Progress Queries
The DataServer allows you to use different approaches for querying an ORACLE database. Your application might be able to take advantage of their strengths depending on the kind of query you are writing and the kind of data you are accessing. The approaches are:
- Progress 4GL — The DataServer generates optimal PL/SQL for DEFINE QUERY and FOR EACH statements, but you can use the QUERY-TUNING option to customize the queries that the DataServer passes to ORACLE.
- Progress SQL SELECT — When you use a SQL SELECT statement in a Progress procedure, the DataServer passes the SQL to ORACLE. This approach can improve performance, especially when counting records, and allow you to access certain types of data more effectively, such as aggregates.
- Progress SQL-92 — Do not use SQL-92 syntax in applications that access the DataServer. The Progress SQL Engine (which compiles SQL-92) is not integrated into the DataServer architecture.
- ORACLE PL/SQL — If you want to use specialized query syntax supported only by PL/SQL, you can use RUN-STORED-PROC send-sql-statement to send the syntax to ORACLE. Use this approach to modify the data definitions of your ORACLE database from the Progress client, for example. Or, if you want to use BEGINS as a search criterion, a PL/SQL query can result in better performance. Note, however, that Progress and PL/SQL queries produce different results when accessing CHAR data because Progress uses bind variables. Use the QUERY-TUNING NO-BIND-WHERE option in the Progress query for results that are more similar to results from a PL/SQL query.
Another factor to keep in mind when deciding which technique to use for issuing queries is whether a query is better served by being processed by the client or the server. A Progress 4GL query is processed by the client (except in the cases of most joins), and SQL SELECT statements or PL/SQL statements are processed by the server (the ORACLE DBMS).
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |