You can translate the question and the replies:

View statistics questions

I recently inherited Denodo admin duties and am investigating a scheduled job which is failing. The job gathers a list of all base tables in Denodo, then runs the following VQL: ``` CALL GENERATE_SMART_STATS_FOR_FIELDS('SMART_THEN_ATSOURCE_THROUGH_VDP','BaseTableName', NULL, true); ``` Where "BaseTableName" is the name of a base table passed to the VQL. I have read the page on [GENERATE_SMART_STATS_FOR_FIELDS](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/stored_procedures/predefined_stored_procedures/generate_smart_stats_for_fields), and understand that its intent is to gather and store the statistics of fields in base views. What I don't understand is, what's the purpose of that? How is it beneficial, and what can said statistics be used for? And now for part two...figuring out why it's failing. Some of the views return one or more of the following errors: > ERROR: View without search methods > java.sql.SQLException: View without search methods; > Error while executing the query". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. > > Error: Executing the query "CALL GENERATE_SMART_STATS..." failed with the following error: "ERROR: Error gathering the statistics of the view: Wrong stats query resultset > java.sql.SQLException: Error gathering the statistics of the view: Wrong stats query resultset; > > Error: Executing the query "connect database eicore; CALL GENERATE_SMART_STATS..." failed with the following error: "ERROR: Error: new Time out processing data > Error in some access (results of query could not be completed). I have experienced the "view without search methods" error before, but never on base tables. On the "Wrong stats query resultset" error, I found another answer which suggested this is due to [limitations of the statistics gathering process.](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/optimizing_queries/cost-based_optimization/gathering_the_statistics_of_views#limitations-of-the-gathering-statistics-process). However, the table in question is a SQL Server table which does not contain TEXT, NTEXT, UNIQUEIDENTIFIER, or SQLVARIANT fields. As for the time out error, can we increase the time limit? That table is very large and does take an unusual amount of time to query.
Statistics generate_stats VDP search methods resultset timeout
user
31-10-2019 13:51:14 -0400
code

5 Answers

Hi, Denodo Statistics are used to tune down the cost based optimizations and accurately estimate the cost. To achieve good estimations of both the required number of I/O operations and the network transfer cost, it is crucial to have available at least the following statistics for the view used in the data source query: * Number of total rows of the view * Field statistics of all the fields of the view that are used in the query. Note that a field might not be referenced in the actual query performed, but may be required in a lower view. * For these fields, it is mandatory to provide at least the number of distinct values. Refer to this [document](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/optimizing_queries/cost-based_optimization/tuning_the_cost-based_optimization_process) for more details Regarding the errors: 1. java.sql.SQLException: View without search methods typically means that you are launching a query against a view that requires more filters against fields. That means the view has more mandatory parameters than provided in the query therefore, make sure all the filters have been provided in the scheduler job. 2. I had the 'Wrong stats query resultset' error when the schema of the view and that of the underlying datasource were out of sync. You could try running a source refresh (open the view > under the Edit tab to the right), and see if there are any updates to the view schema. 3. I was able to reproduce the particular error “Error: new Time out processing data Error in some access(results of the query could not be completed)”, only in the case when the client throws a timeout. Therefore, you can edit the query timeout parameter of the scheduler VDP datasource by going to Data Sources>Edit data source and edit the query timeout. Query timeout is the maximum time (in milliseconds) that Scheduler will wait for the statement to be completed. If not indicated (or the value 0 is received), then it waits until execution is complete (by default 0). You can refer to the document [here](https://community.denodo.com/docs/html/browse/7.0/scheduler/administration/creating_and_scheduling_jobs/data_sources/vdp_data_sources) for more details Hope this helps!
Denodo Team
06-11-2019 20:35:44 -0500
code
That helps a lot, thanks! As a follow up... 1. The views giving the "view without search methods" error are base views, so no parameters are even possible. Is there anything else that could be the cause? 2. It turned out the views that were failing with the "wrong stats query resultset" error are no longer used. Thus we have excluded them from the stats refresh process. 3. Query timeout was set to -1 for the corresponding data sources. I'm not sure what the intent of -1 was, but I removed that value, as per the documentation, that should allow Denodo to wait until execution is complete. If it doesn't, I'll report back with details.
user
02-12-2019 12:14:50 -0500
Hi, Regarding your first point, I was able to have mandatory parameters(fields) in a base view. To change the obligatoriness of a parameter, you can go to Options>Search Methods and edit the Madatory column of Query Capabilities table to OBL. You will have three options to select from the dropdown- obligatory (OBL), optional (OPT) or not supported (NOS), you can select OBL. Make sure the parameter has multiplicity greater-than-zero. To have more details, you can refer to this document on[ query capabilities](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/advanced_configuration_of_views/query_capabilities#query-capabilities) and [search methods ](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/advanced_configuration_of_views/query_capabilities#search-methods). Having said that I would like to mention that the “View without search methods” error occurs only when a view is queried without complying with its mandatory search conditions. Therfore, make sure you have the job quering the views which comply to its mandatory search condition. Hope this helps!
Denodo Team
03-12-2019 13:38:17 -0500
code
Following up one more time, on the timeout error. Removing the value didn't stick...I checked the data source again and it was set to -1. I set it to 0 instead, as per the documentation, that should force Denodo to wait until execution is complete. Yet still certain views are failing with "Error: new Time out processing data Error in some access (results of query could not be completed)." Is there anywhere else that query timeout might be set?
user
20-01-2020 11:24:00 -0500
When that error occurs for jobs which gather statistics, it could mean that the stored procedure timeout is being reached in the VDP server. The generation of stats is done through stored procedures and as such, it is affected by this parameter. This parameter can be found and adjusted with an admin user through “Administration > Server Configuration > Stored Procedures” in the VDP Administration Tool.
Denodo Team
06-02-2020 00:08:36 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here