Salesforce

Result Database (Magic xpa 4.x)

« Go Back

Information

 
Created BySalesforce Service User
Approval Process StatusPublished
Objective
Description

Result Database (Magic xpa 4.x)

Result Database as Input Database

You can set the same value in the Result Database field as in the Input Database field.
In most cases, the underlying RDBMS allows an

INSERT INTO table AS SELECT...

statement, which copies all the data to a table in one command. This method is faster than opening a cursor. All of the records are retrieved from the client and inserted into the result table.

In such cases, the SQL gateway creates a table in the database and sends:

INSERT INTO temp_ table AS SELECT “direct SELECT statement”

The user may then scroll on that table to speed up the SELECT statement execution. This method is especially helpful when the result set is large because there is no retrieving and inserting of each record to the temporary table.

Result Database Different from Input Database

When the result database is different from the input database, a temporary table is created. A cursor is defined, and the records are retrieved and inserted one by one into the result table. The user may then scroll on that table.

Recommendations

When the result is large, it is best to use the option that makes the result database the same as the input database. Then the INSERT INTO AS SELECT statement will be used.

When the result is relatively small, it is best to use the result database as an ISAM database that will reside on the client. Then when you create the result and scroll on it, the work is only performed on the client, which reduces network traffic.

Use the memory gateway when the results are relatively small to enhance performance.

Restrictions

Space databases, MySQL, DB2 and ODBC cannot be used as the Result Database of a Direct SQL task. As of V3.1, Direct SQL can be used for execution commands (commands that do not retrieve records).

When using the Microsoft SQL 2005 database as the result database of DSQL, you may encounter problems. Since version: 3.0

The number of Blobs limited to various result databases are as follows:

Database

Maximum

Number of Blobs

MSSQL

10

SQLite

10

Oracle

10

DB2

10

Reference
Attachment 
Attachment