SYMPTOM:
Below is Common Problems and Solutions using Amazon Redshift driver in Strategy 10.x:
| | |
| | |
| | |
| | |
| | |
Using Catalog Functions
Because catalog functions, such as those listed here, are slow compared to other ODBC functions, their frequent use can impair system performance:
SQLColumns
SQLForeignKeys
SQLGetTypeInfo
SQLSpecialColumns
SQLStatistics
SQLTables
SQLGetTypeInfo is included in this list of expensive ODBC functions because many drivers must query the server to obtain accurate information about which types are supported (for example, to find dynamic types such as user defined types).
Selecting ODBC Functions
The guidelines in this section will help you select which ODBC functions will give you the best performance.
Using SQLPrepare/SQLExecute and SQLExecDirect
Using SQLPrepare/SQLExecute is not always as efficient as SQLExecDirect. Use SQLExecDirect for queries that will be executed once and SQLPrepare/SQLExecute for queries that will be executed multiple times.
Using SQLPrepare/SQLExecute for a statement that is executed only once results in unnecessary overhead. Furthermore, applications that use SQLPrepare/SQLExecute for large single execution query batches exhibit poor performance. Similarly, applications that always use SQLExecDirect do not perform as well as those that use a logical combination of SQLPrepare/SQLExecute and SQLExecDirect sequences.
Using Arrays of Parameters
Passing arrays of parameter values for bulk insert operations, for example, with SQLPrepare/SQLExecute and SQLExecDirect can reduce the ODBC call load and network traffic. To use arrays of parameters, the application calls SQLSetStmtAttr with the following attribute arguments:
SQL_ATTR_PARAMSET_SIZE sets the array size of the parameter.
SQL_ATTR_PARAMS_PROCESSED_PTR assigns a variable filled by SQLExecute, which contains the number of rows that are actually inserted.
SQL_ATTR_PARAM_STATUS_PTR points to an array in which status information for each row of parameter values is returned.
Note: ODBC 3.x replaced the ODBC 2.x call to SQLParamOptions with calls to SQLSetStmtAttr using the SQL_ATTR_PARAMSET_SIZE, SQL_ATTR_PARAMS_PROCESSED_ARRAY, and SQL_ATTR_PARAM_STATUS_PTR arguments.
Using the Cursor Library
If the driver provides scrollable cursors, do not use the cursor library. The cursor library creates local temporary log files, which are performance-expensive to generate and provide worse performance than native scrollable cursors.
The cursor library adds support for static cursors, which simplifies the coding of applications that use scrollable cursors. However, the cursor library creates temporary log files on the user’s local disk drive to accomplish the task. Typically, disk I/O is a slow operation. Although the cursor library is beneficial, applications should not automatically choose to use the cursor library when an ODBC driver supports scrollable cursors natively.
Retrieving Data
To retrieve data efficiently, return only the data that you need, and choose the most efficient method of doing so. The guidelines in this section will help you optimize system performance when retrieving data with ODBC applications.
Retrieving Long Data
Reducing the Size of Data Retrieved
Using Bound Columns
Using SQLExtendedFetch Instead of SQLFetch
Choosing the Right Data Type
Managing Connections and Updates
Managing Connections
You can significantly improve performance with connection pooling, especially for applications that connect over a network or through the World Wide Web. With connection pooling, closing connections does not close the physical connection to the database. When an application requests a connection, an active connection from the connection pool is reused, avoiding the network round trips needed to create a new connection.
Managing Commits in Transactions
Committing data is extremely disk I/O intensive and slow. If the driver can support transactions, always turn autocommit off.
Choosing the Right Transaction Model
Many systems support distributed transactions; that is, transactions that span multiple connections. Distributed transactions are at least four times slower than normal transactions due to the logging and network round trips necessary to communicate between all the components involved in the distributed transaction. Unless distributed transactions are required, avoid using them. Instead, use local transactions when possible.
Using Positioned Updates and Deletes
Use positioned updates and deletes or SQLSetPos to update data. Although positioned updates do not apply to all types of applications, developers should use positioned updates and deletes when it makes sense. Positioned updates (either through UPDATE WHERE CURRENT OF CURSOR or through SQLSetPos) allow the developer to signal the driver to "change the data here" by positioning the database cursor at the appropriate row to be changed. The designer is not forced to build a complex SQL statement, but simply supplies the data to be changed.
Using SQLSpecialColumns
Use SQLSpecialColumns to determine the optimal set of columns to use in the Where clause for updating data. Often, pseudo-columns provide the fastest access to the data, and these columns can only be determined by using SQLSpecialColumns.
Refer to Technical Document below for how to integrate Strategy with Amazon Redshift:
KB26614: Integrating MicroStrategy Analytics 9.4.x and newer platform with Amazon Redshift
Refer to Technical Document below for a list of certified drivers to connect to Amazon Redshift:
KB254423: Certified ODBC configurations to connect to Amazon Redshift across different Strategy versions
Refer to Technical Document below for Amazon Redshift data types and its mapping:
Amazon Redshift data types and its mapping to standard ODBC data types in MicroStrategy 10.x
Refer to Technical Document below for Connection options to enhance redshift driver performance:
Connection options to enhance redshift driver performance in MicroStrategy 10.x
Article Reference Number: KB441642
