You can translate the question and the replies:

VQL Query Shows a Long Time Spent in Data Serialization

Hello! We are using Denodo 7.0.20200310 and SQL Server 2017. We have a simple SELECT statement joining 2 base views that runs slowly in our test environment (52 seconds) and fast in our QA environment (<1 second). In looking at the trace information we can see that in the test environment the Data Serializaion Time is the obvious difference. Virtually all of the execution time is data serialization. Any suggestions on what to look at to correct this? thanks!
user
11-11-2021 09:40:12 -0500
code

2 Answers

Hi, In order check the cause of the increase in Data Serialization time in one environment and to improve the performance I would perform the following checks: * I would check if the appropriate [Join optimization](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/optimizing_queries/optimizing_join_operations/optimizing_join_operations) strategy is used. * I would ensure that all the relevant information needed for the query optimizer like primary keys, indexes, referential constraints or view statistics, is available. * I would also increase the maximum number of active connections in the connection pool configuration which would resolve the issues when there are too many connections to the data source. * Furthermore, I would also check for any network latency in the environment where the execution time is higher. You could take a look at the Knowledge Base Article [Best Practices to Maximize Performance IV: Detecting Bottlenecks in a Query](https://community.denodo.com/kb/en/view/document/Best%20Practices%20to%20Maximize%20Performance%20IV%3A%20Detecting%20Bottlenecks%20in%20a%20Query#h.h9hos2ydcqve) for more details on how to detect bottlenecks. You can also refer to the Knowledge Base Article [Best Practices to Maximize Performance II: Configuring the Query Optimizer](https://community.denodo.com/kb/en/view/document/Best%20Practices%20to%20Maximize%20Performance%20II:%20Configuring%20the%20Query%20Optimizer?tag=Best+Practices) for more information. Hope this helps!
Denodo Team
12-11-2021 02:16:04 -0500
code
Thanks for all the great info! In our particular situation, the root cause of the slowness in the test environment was that the SQL Server setting for compatibility level had been changed to 2017. When we changed it back to 2012 to match our QA environment, the performance issue disappeared.
user
15-11-2021 11:43:09 -0500
You must sign in to add an answer. If you do not have an account, you can register here