You can translate the question and the replies:

WHERE Clause in query

I have created dervied view's using Baseview views and query also has an where clause at the end on one the Base view. When I run the new derived view the where condition is executed at the end. I dont see this helping peformance. please let me know, If i have to enable any options for this. Please see the example below. Derived View - VQL : SELECT BV1.A , BV1.B, BV2.A, BV2.B FROM BASEVIEW_1 BV1 INNER/OUTER JOIN BASEVIEW_2 ON BV1.A = BV2.A AND BV1.B = 'ABC' How I see in query plan(being executed) Source1 - Query SELECT * FROM BASEVIEW1 BV1 Source2 - Query SELECT * FROM BASEVIEW1 BV2 Join Query SELECT BV1.A , BV1.B, BV2.A, BV2.B FROM BV1 INNER/OUTER JOIN BV2 ON BV1.A = BV2.A AND BV2.B = 'ABC' Exepected: Source1 - Query SELECT * FROM BASEVIEW1 BV1 WHERE BV1.B = 'ABC' [ This reduces the no of rows to be processed and decresses query execution time] Source2 - Query SELECT * FROM BASEVIEW1 BV2 Join Query SELECT BV1.A , BV1.B, BV2.A, BV2.B FROM BV1 INNER/OUTER JOIN BV2 ON BV1.A = BV2.A
WHERE CONDITIONS Sql performance query plan
user
10-01-2018 02:57:43 -0500
code

1 Answer

Hi, There could be a few factors at work here, but if your base views are coming from a relational database or a parameterized web service, then you can achieve what you are expecting. If your Base Views are using relational database(s) as their Data Source(s), Denodo should be able to delegate the WHERE clause to the source. Check the [Data Source Configuration Properties](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/importing_data_sources_and_creating_base_views/data_source_configuration_properties) to ensure that you are allowing delegation of the phrases of your SQL sentence in the manner that you expect. If BASEVIEW1 and BASEVIEW2 are pulling from different data sources, the [Cost-Based Optimization](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/optimizing_queries/cost-based_optimization/cost-based_optimization) engine may need to be enabled for this virtual database or have statistics gathered on the base views in order to make the best choices on how to execute this query. You may also want to take a look at the other means of [Optimizing Queries](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/optimizing_queries/optimizing_queries), particularly the section on Data Movement if the number of rows in BASEVIEW1 is a big part of your concern. Keep in mind, however, that certain types of Data Sources, such as delimited filea, can not process a WHERE clause themselves, so that needs to be handled by Denodo. I hope this helps!
Denodo Team
10-01-2018 14:12:41 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here