You can translate the question and the replies:

Passing case statement as subquery in select statement.

We are automating function to get prior two business days excluding weekends using below logic : select * from dw_vdb.cib360_bv_daily where as_of_date in (select case getdayofweek(current_date) when 3 then addday(current_date, -4) when 2 then addday(current_date, -3) else addday(current_Date, -2) end) query works fine with hard coded dates but with case statement it is getting timed out. This is happening even for very simple base views. Appreciate your help. You can suggest alternative logic to get 2 previous business days passed to the select query. Thanks.
user
15-07-2021 13:40:03 -0400

3 Answers

Hi, The processing time can be improved if you can [delegate these functions](https://community.denodo.com/kb/en/view/document/Scalar%20functions%20list%20in%20a%20data%20source) to the source. Please check in your execution trace if these functions are delegated to the source. For this, click the bottom node associated with the data source. After selecting the node, on the right side of the execution trace, you will see Properties and Advanced Properties which you could expand using the ‘+’ symbol. These properties show you information about the connection, including the query sent to the database next to the ‘SQLSentence’ label. You can check these functions on the Virtual DataPort Administration tool by navigating to the Source configuration option under the Configuration tab of the datasource. Under the option ‘Delegate Scalar Functions list’, you can find the list of functions that can be delegated to your data source. The functions that are delegated vary from one database to another. Further, you can also use a CONTEXT clause to set the Query Timeout argument. For example, `SELECT * FROM <view_name> CONTEXT (‘QUERYTIMEOUT’=’900000’);` Hope this helps!
Denodo Team
15-07-2021 18:56:45 -0400
In this, 1. the subquery is not delegated to the source because it is executed from dual(). 2. when we execute this full query, it is loading more than 2ml records first (Reason for query timeout) and then it applies the subquery logic in where condition. So, the question is, is there anyway to get executed the sub query first and then the main query applies filter? Is there any other logic to help here? Appreciate your help.
user
19-07-2021 11:53:21 -0400
Hi, You can create a separate view for the sub query and then execute the main VQL query joining to the new view. Creation of a separate view for the case logic will prevent it being executed on each row, which seems to be where the performance hit is occurring. This will execute the logic once to create the result set from dual(), which can then be joined to instead of repeatedly executed. A query containing a subquery usually has an equivalent alternative using a JOIN instead of a subquery. If the subquery cannot be pushed to a data source, my recommendation is to avoid the use of subqueries and use the equivalent JOIN instead. This is because the use of subqueries in the WHERE clause in Denodo has some limitations as explained in the section ‘Avoid subqueries inside the WHERE clause’ of the [document](https://community.denodo.com/kb/en/view/document/Best%20Practices%20to%20Maximize%20Performance%20I%3A%20Modeling%20Big%20Data%20and%20Analytic%20Use%20Cases). Hope this helps!
Denodo Team
22-07-2021 17:57:43 -0400
You must sign in to add an answer. If you do not have an account, you can register here