$manifold$ Directive

The $manifold$ preprocessor directive instructs the system to use the Manifold SQL engine regardless of the context of the query or command window. The format of the directive is

-- $manifold$

The $manifold$ preprocessor directives must appear before the first statement of the query.

SQL can utilize other SQL engines besides the native Manifold query engine to execute a query. If a data source in a project has its own query engine, for example, a data source created using Oracle DBMS, SQL can execute queries using that data source's query engine.

There are two ways SQL knows which query engine to use:

The context of the query or command window - If a query is created within the hierarchy of a data source that has its own SQL engine, when that query is run the data source's native SQL engine will be used. We can launch a Command Window using a data source's native SQL engine by right-clicking on the data source and choosing New Command Window from the context menu. If a Command Window is launched or a query is created outside the hierarchy of a data source that has its own SQL engine then the Manifold SQL engine will be used.
The use of a specific directive - Including the directive $manifold$ in comments prior to the first statement instructs the system to use the Manifold SQL engine regardless of the context of the query or command window. Comments in SQL begin with two hyphens in sequence, -- . We can also use the EXECUTE statement with the ON option to specify execution by a particular data source's query engine.

Example: We right-click within a SQL Server data source in the Project pane and create the query:

SELECT * FROM dbo.t ;

When executed, that query will run in SQL Server using the SQL Server query engine with the results passed to Manifold. If we change the query to be:

-- $manifold$

SELECT * FROM dbo.t ;

That query will run using the native SQL engine, pulling data into the system from SQL Server as necessary from the dbo.t table.

When a query uses a data source's engine the entire query is sent to the data source for execution entirely within the data source along with a request from SQL for the results. That is a good strategy in cases where the data source may not be local, the connection to the data source is not fast, or the data source hosts a lot of data and the results from the query are expected to be small. For example, using a data source's query engine is a good idea if many tables involving millions of records are involved with the results expected to be a few hundred records. The data source can do the work of going through a lot of data and then send through the slower connection link back to SQL a relatively small amount of data.

There are some limitations on queries run within data sources on their native query engines. Such engines are not Manifold SQL so they can not use features specific to Manifold SQL such as functions and data types specific to Manifold SQL, enhance analytic performance via GPGPU, use virtual tables added by dataports, virtual system tables like mfd_root and mfd_meta and so on.

When a query uses the SQL engine but involves data from a remote data source, the SQL engine compiles the query into native structures and then lets these structures fetch data from the involved data sources for doing analysis within SQL. In some cases of well-known data sources, the Manifold SQL optimizer may send parts of the query out to the data source for execution on the data source if the optimizer believes that will be more efficient. Using the native Manifold engine allows using functions and data types specific to Manifold, combining data from multiple data sources of different types and other Manifold-specific features.

$manifold$ Directive

See Also