Connection key
You can use the Connection key setting from the Property Panel to ensure that a single query to multiple data sources is executed if all connections are compatible. The connections are compatible if the same database type is used and the following conditions are met.
-
For the MS SQL Server, MySQL, and Oracle databases either of these rules must be met:
-
Connection keys are left blank and connection settings are identical. Note that for the MS SQL and MySQL databases, the Database Name setting is ignored.
-
Connection keys are identical (depends on connection strings).
-
-
For other database types, such as Excel, Hadoop, OData Feed, and more, all connection keys are either identical or left blank and connection settings are fully identical.
Query types
Insight supports the following query types: Insert select, Single select, and Multiple.
- Insert select query
- If all document sources and the Data DB source are compatible, Insight executes a single query to read data from all the sources and to write it into the Data DB during data load. The Data DB connection settings are used to execute this query.
- Single select query
- If all document sources are compatible, but the Data DB source is not compatible, a single query to read data from all the sources to the memory is executed, and then the data is written into the Data DB from the memory. The connection from a root source is used to execute the query.
- Multiple queries
- If some document sources are not compatible, multiple queries are executed to read data from each source to the memory, then to write the data into the temporary tables in the Data DB, and finally to write the data into the main table.
The query type can be automatically selected by Insight based on connection settings and a connection key, or you can manually set the query type for each document. We recommend that you use the automatic query type.
General rules
The Insert select query type can improve the performance significantly, because it causes the data to remain on the SQL server instead of reading the data to the memory like in other modes. We recommend that you configure data sources in such a way that the Insert select query is used if possible. Otherwise, configure the data sources to execute the Single select query. To achieve this, leave the connection key blank and set the same connections strings (settings) for the sources. If it is not possible (for example, for the Oracle database sources due to different schemes and users), set the same connection key for the data sources.
In a situation when the connection settings are the same but executing a single query to multiple data sources is not an option, you can set different connection keys to execute a Singe select query or multiple queries, as appropriate. For example, in the Microsoft Azure SQL Databases environment, a single query cannot be executed to different databases, so multiple queries will be required.