QueryDef's that require Access to process data locally can result in network bottlenecks since Access must request that Oracle transfer all rows of table data (forced serial read) over the network. Queries that execute on the Oracle server only return data that results from the query's execution, minimizing network traffic. In addition, Oracle performance tuning methods can be applied to objects that have been converted from MS-Access to Oracle.
Microsoft Access "Make Table" and "Append" queries are prime candidates for translation to Oracle PL*SQL procedures. These queries perform table operations that append data to target tables from other Oracle tables.
With Scriptoria, target tables may reside on the Oracle server or in the Access database. Which to choose depends on how the application utilizes the tables.
For example, you may wish to keep target tables in MS-Access if the data will be used repeatedly in subsequent reports that sort, select and group the same data, in various ways. This technique will likely result in less work for the Oracle server and less network traffic as well
With Scriptoria, you can choose to create Oracle Global Temporary Tables as target tables, or persistent ones. Global temporary tables ensure that users executing the same query do not interfere with each other, since each user gets their own copy of the table. Global table data is also automatically removed when the session is terminated.
After migrating to Oracle, all tables, other than the target table, involved in MakeTable and Append operations reside on the Oracle server and are most efficiently performed by the server.
Reporting applications are great candidates for converting MS-Access queries into Oracle views or REF CURSOR procedures and functions. You can use the Where-Used List to determine which queries are used only with MS-Access reports.
Access queries that employ non-standard Access built-in functions, like IIf() or Val() in WHERE, ORDER BY, and HAVING statements, can greatly impede post-migration performance; the Access Jet "Query Optimizer" must request that all table data be retrieved for local processing to evaluate queries containing these functions. Since Scriptoria converts built-in functions to equivalent Oracle functions, greatly improved performance can be achieved by converting Access queries containing special built-in functions to Oracle.
Queries are the foundation of most reports and embody the logic of the application. When replacing the MS-Access front-end application, significant investments in Access Query development can be preserved and new re-development averted by using Scriptoria to generate Oracle objects.