Best Practices

The following are some guidelines and recommendations that can help you optimize connector performance.

Design Queries to Retrieve Product Data from the Minimal Schema

The Products table in the Magento data store is available through two schemas: Complete and Minimal. The Minimal schema restricts the connector’s access to the data in the Products table, reducing the number of API calls used when retrieving data from that table. To optimize connector performance and avoid unnecessary API calls when retrieving data from the Products table, query the table from the Minimal schema whenever possible.

For example:

SELECT * FROM Minimal.Products WHERE Price < 1000

If you need complete access to the data in the Products table, query the table from the Complete schema instead.

Note:

Unlike the Products table, other Magento tables are available only through the Minimal schema, and you have full access to all the data. For these tables, the connector uses the Minimal schema by default, so specifying the schema is optional.

Design Queries that Use Query Folding

Query folding is a feature that enables the connector to delegate a comparison operation so that it is processed on the server instead of the client. To optimize connector performance, design your queries to filter the data using columns and operators for which folding is supported. For a list of foldable columns in each table in the schema, see the Simba Magento JDBC Data Connector Reference Guide.

The connector supports query folding for the following operators:

  • AND
  • IN
  • IS NULL
  • LIKE
  • NOT (see the note below)
  • <>
  • =
  • >
  • >=
  • <
  • <=

Important:

  • Query folding is supported for the NOT operator only if it is combined with the IS, LIKE, or IS NULL operators in the same WHERE clause. For more information, see Enhanced Query Syntax.
  • Query folding is disabled for the entire query if any part of the WHERE clause contains an operator that does not support folding.

When possible, the only columns used in WHERE clauses should be columns that support query folding. If a WHERE clause uses a column that cannot be folded, the connector requests all data from the specified table, and then discards the unnecessary data. This process can consume many API calls and significantly decrease performance.

In situations where you cannot use a WHERE clause with a column that supports folding, consider using SELECT TOP N statements to restrict the scope of your query.

Design Queries that Use the Top N Command

If query folding is not supported in the table that you need to query, you can optimize connector performance by designing queries that use the Top N command.

Normally, when you execute a query that filters on a column that is not foldable, the connector retrieves all the data and then filters the data to return the appropriate result set. This process can be time-consuming and cause the connector to invoke many API calls. If you execute a query using the Top N command, then the connector retrieves only the specified data, and returns additional data only when you make an API call to retrieve the next page of data.

It is recommended that you use the Top N command when querying data from a table that does not contain foldable columns. To verify if a particular table contains any foldable columns, see the insightsoftware Simba Magento JDBC Data Connector Reference Guide.

Related topics