Improving database insert performance: parameterized queries

MicroStrategy's support for parameterized queries can improve performance in scenarios that require the insertion of information into a database. The scenarios that can benefit from the use of parameterized queries include:

•

Reports that combine data from multiple data sources using MicroStrategy MultiSource Option. For information on MultiSource Option, see Accessing multiple data sources in a project.

•

MicroStrategy data marts that are stored in a database other than the database used for the main data warehouse. For information on creating and using data marts, refer to the Advanced Reporting Guide.

•

Metrics that use functions that are evaluated by the Analytical Engine. For information on functions, refer to the Functions Reference.

•

Custom groups that use banding qualifications that are evaluated as normal calculations. For information on custom groups, refer to the Advanced Reporting Guide.

Parameterized queries are SQL queries that can use placeholders for data. Using placeholders allows these queries to be re-used. A common application of this re-usability is to combine multiple inserts of data into a database as a single query. The following is an example of a parameterized query:

INSERT INTO DMTABLE (Customer_ID, Customer_Name) VALUES (?, ?)

Combining multiple INSERT statements into a single query can improve the performance of inserting data into the database. The steps below show you how to enable the use of parameterized queries in MicroStrategy.

If you enable parameterized queries for a Netezza database that includes data that uses the WCHAR data type, this can cause some characters to be returned incorrectly as question mark (?) characters. If you encounter this type of data inconsistency, you can configure the Use Column Type Hint for Parameterized Query VLDB property to return the WCHAR data accurately. For information on configuring this VLDB property, see the System Administration Guide.

Prerequisites

•

Parameterized queries are only supported by certain databases. Refer to your third-party database documentation to ensure that your database can support parameterized queries.

•

A database instance has been created. This database instance must connect to the database to enable support for parameterized queries.

To enable the use of parameterized queries

1

In MicroStrategy Developer, log in to a project source with a user account that has administrative privileges.

2

From the Folder List, expand Administration, then expand Configuration Managers, and then select Database Instances. Database instances for the project source are displayed.

3

Right-click a database instance and select Edit. The Database Instances Editor opens.

4

To the right of the Database connection area, click Modify. The Database Connections dialog box opens.

5

On the Advanced tab, select the Use parameterized queries check box.

6

If you are enabling parameterized queries for one of the databases listed below, you must also include the following parameters:

•

To enable parameterized queries for Oracle 10g, Oracle 10gR2, Oracle 11g, Oracle 9i, Sybase Adaptive Server 12.x, or Sybase ASE 15.x, type the following parameter in the Additional connection string parameters field:

EnableDescribeParam=1

•

To enable parameterized queries for Teradata 12.0 or Teradata V2R6.2, type the following parameter in the Additional connection string parameters field:

EnableExtendedStmtInfo=Yes

7

Click OK to accept your changes and close the Database Connections dialog box.

8

Click OK to close the Database Instances Editor.