Explanation of how Webi parallel queries work in BI4.2.

Explanation of how Webi parallel queries work in BI4.2.

The main points about parallel queries that are important to be understood are as follows:

  • In the Webi Processing Server properties, you can specify the “Maximum Parallel Queries per document”. This means that when a Webi report is opened using that particular Webi Processing Server, the maximum number of queries that can be run in parallel for that Webi document is the number that is set here. The maximum number that can be set here is 64. If you would like to disable parallel queries altogether for Webi documents that are opened through a particular Webi Processing Server, then this value should be set to 1. You can set the property to be the same or different in each of the Webi Processing Servers. The key is that when a specific Webi Processing Server is used, it will respect the value that is specified for the particular Webi Processing Server that is being used to process that Webi document.
  • The maximum parallel queries that can be run at a given time for a specific relational connection that is being used in a specific Webi document is specified in the relational connection. The maximum value is 64 and the minimum value is 1 (meaning that parallel queries is disabled for this connection).
  • The maximum parallel queries that can be run at a given time for a specific OLAP connection that is being used in a specific Webi document is specified in the OLAP connection. The maximum value is 64 and the minimum value is 1 (meaning that parallel queries is disabled for this connection).  Please note that the maximum value that is specified here is for each of the queries that is using that OLAP connection within a specific Webi document.
  • When you are running Webi queries in a system, both of the above situations have to be respected in order for the queries to be run in parallel without any of the queries being queued. If a query is being queued due to the limit at either the Webi document level or at the connection level within a Webi document, when a position is opened then the query will be run.

A few of examples that reflect this behaviour are as follows:

  1. You have one Webi Processing Server with “Maximum Parallel Queries per document” set to 64. You then have 100 Webi documents that each have 8 queries defined that use the same relational connection so that when you refresh them, 8 queries will need to be refreshed for each of them. The one relational connection has the maximum parallel queries defined to be 8. Each of the 100 Webi documents is using the one relational connection. From the perspective of each of the Webi documents being able to run the 8 queries in that individual Webi document, this would be fine because the Webi Processing Server has the “Maximum Parallel Queries per document” parameter set to 64 which is greater than the 8 queries that are in each of the Webi documents. If all 100 of these Webi documents are refreshed at around the same time, then all of the queries should be able to be run in parallel. The reason for this is that the relational connection allows for 8 queries from a single Webi document to be run in parallel. Since each Webi document has 8 queries that use the single relational connection, each of the queries would be able to be run at the same time. Additionally, since the maximum parallel queries at the relational connection level is for each Webi document that is using the connection, all of queries from each of the Webi documents should be able to be run at the same time.
  2. You have one Webi Processing Server with “Maximum Parallel Queries per document” set to 64. You then have 50 Webi documents that each have 10 queries defined that use the same relational connection so that when you refresh them, 10 queries will need to be refreshed for each of them. The one relational connection has the maximum parallel queries defined to be 8. Each of the 10 Webi documents is using the one relational connection. From the perspective of each of the Webi documents being able to run the 8 queries in that individual Webi document, this would be fine because the Webi Processing Server has the “Maximum Parallel Queries per document” parameter set to 64 which is greater than the 10 queries that are in each of the Webi documents. If all 50 of these Webi documents are refreshed at around the same time, then only 8 of the queries would be able to run in parallel in each Webi document and the other 2 would be queued and one would start to run when one of the other queries is finished and the other query would run when another one of the queries is finished. Since the maximum parallel queries at the relational connection level is for each Webi document that is using the connection, each of the Webi documents would follow the same as the previous explanation (8 would run in parallel and 2 would be queued and run as other queries are finished).
  3. You have one Webi Processing Server with “Maximum Parallel Queries per document” set to 10. You then have 50 Webi documents that each have 8 queries defined that use relational connection 1 and have 8 queries defined that use relational connection 2. When you refresh each Webi document, a total of 16 queries will need to be refreshed. Relational connection 1 has the maximum parallel queries defined to be 6. Relational cnnection 2 also has the maximum parallel queries defined to be 6. In this situation, there is a limiting factor to refreshing the queries in parallel that is related to the “Maximum Parallel Queries per document” since there are a total of 16 queries that need to be refreshed and this value is greater than the “Maximum Parallel Queries per document” which is 10. There is also a limiting factor to refreshing the queries in parallel that is related to each of the relational connections which have the maximum parallel queries defined to be 6 which is less than the 8 queries that use each connection. If all 50 of the Webi documents are refreshed at around the same time, then only 10 of the queries would be able to run in parallel in each Webi document and the other 6 (8 queries for each relational connection * 2 connections – a limit of 10 “Maximum Parallel Queries per document” for the Webi Processing Server) would be queued. As each of the queries is returned from the database, one of the queries that is queued will be sent to the database until all of the queries in the queue are finished. Since the maximum parallel queries at the relational connection level is for each Webi document that is using the connection, each of the Webi documents would follow the same as the previous explanation.
  4. You have two Webi Processing Servers. One of the WIPS has the “Maximum Parallel Queries per document” set to 10. The other has the “Maximum Parallel Queries per document” set to 5. You have 2 Webi documents with each of them having 8 queries that use the same relational connection so that when you refresh them, 8 queries will need to be refreshed for each of them. The one relational connection has the maximum parallel queries defined to be 8. Both of the Webi documents are refreshed at around the same with one of the documents using the WIPS that has the “Maximum Parallel Queries per document” set to 10 and the other using the WIPS that has the “Maximum Parallel Queries per document” set to 5. When the Webi document that is using the WIPS that has the “Maximum Parallel Queries per document” set to 10 is refreshed, all 8 of the queries will be refreshed at the same time because the WIPS has the “Maximum Parallel Queries per document” parameter set to 10 which is greater than the 8 queries that are in the Webi document. Additionally, since the Webi document has 8 queries that use the single relational connection, from the connection perspective, each of the queries would be able to be run at the same time because the connection has the maximum parallel queries defined to be 8. When the Webi document that is using the WIPS that has the “Maximum Parallel Queries per document” set to 5 is refreshed, 5 of the queries will be refreshed at the same time and the other 3 will be queued. As a spot opens up when the previously running query finishes then one of the queued queries will be able to be run until all 3 of the waiting queries are finished. As can be seen in this example, if different Webi Processing Servers have different values for the “Maximum Parallel Queries per document” property, it will result in different ways that the queries will be run in parallel. If you would like the same behaviour for all of the Webi Processing Servers then the “Maximum Parallel Queries per document” property should be set the same on all of the WIPS.
  5. You have one Webi Processing Server with “Maximum Parallel Queries per document” set to 64. You then have 100 Webi documents that each have 8 queries defined that use the same OLAP connection.  4 of the queries that use the OLAP connection are pointing to query 1.  The other 4 queries are pointing to query 2.  The result is that when you refresh the Webi document, 8 queries will need to be refreshed (4 for query 1 and 4 for query 2) for each of them. The one OLAP connection has the maximum parallel queries defined to be 4. Each of the 100 Webi documents is using the one OLAP connection.  From the perspective of each of the Webi documents being able to run the 8 queries in that individual Webi document, this would be fine because the Webi Processing Server has the “Maximum Parallel Queries per document” parameter set to 64 which is greater than the 8 queries that are in each of the Webi documents. If all 100 of these Webi documents are refreshed at around the same time, then all of the queries should be able to be run in parallel. The reason for this is that the OLAP connection allows for 4 queries to be run in parallel for each of the different queries that are using the OLAP connection.  Since the Webi document has 4 queries that use OLAP query 1 and 4 queries that use OLAP query 2, all 8 of these queries would be able to be run at the same time.  Additionally, since the maximum parallel queries at the OLAP connection level is for each Webi document that is using the connection, all of queries from each of the Webi documents should be able to be run at the same time.

Of course, the caveats in all of the examples above are as follows:

  1. At the database level, all of the queries are able to be run at the same time and are not queued at the database side of things. Parallel queries at the Webi side just mean that Webi will submit the queries in parallel. How the database processes those queries is something that the database takes care of. Once the database has finished processing the query, the results will be returned to Webi and if there is a query that is queued on the Webi side then it will be able to be submitted to the database.
  2. The database needs to be able to handle the increased concurrent load that is put on it when Webi uses parallel queries.
  3. The BI system needs to be able to handle the concurrent processing of the queries so the system needs to be sized to handle the load.

Other Notes:

  • When a Webi document has queries where one query is dependent on the results of another query, then the query that is dependent on the results of another query will not be able to start running until the query that it is dependent on has finished executing.

Be the first to comment

Leave a Reply

Your email address will not be published.


*