You can translate the question and the replies:

Denodo DVP 6.0 and SQL Server Performance

Community, I'm fairly new to the forumsd, so please be patient with me. I configured a SQL Server 2014 data source using the SQL Server 2014 JDBC driver and noticed poor performance characteristics when issuing a query using a varchar field as the search parameter. Let's say i have products table with Channel (Varchar (100)) as a field. I created a view called Internet Products which is a view filtered on Channel = 'Internet'. When i issue a query against this view, Denodo sends an adhoc query where the parameter is passed in as nvarchar(4000). Since denodo is passing in a parameter with a different data type than the source system, it causes SQL server to perform a table scan and ignore the index on "Channel". Example Query from Denodo: (@P0 nvarchar(4000)) Select * From Products where channel = @p0 FYI - in our environment, this means a difference in query execution by a significant factor. Denodo Query = 1:16 <mm:sec.ms> Same Query, but change the parameter data type to varchar = 0:00.500

3 Answers

Hi, By reading the description, it seems that you need to configure the source type properties of your base view fields to fit with the field type in MS SQL Server 2014. To do that, you can follow these steps: 1.Open your base view in Virtual DataPort Admin tool and click on Edit button. 2.Edit the source type property to fit with the field type in MS SQL Server 2014. 3.Finally save the base view by selecting the affected views build on top of this base view to be automatically updated with the new changes. If you only want to create a filter over a table, I recommend you to do it graphically in Virtual DataPort admin tool by following these steps: 1. Create a new base view over your table. 2. Create a selection view over the new base view, specifying the filter in the “Where Conditions” tab when creating the selection view. You can refer to this official documentation link to find more information [Viewing the Schema of a Base View](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/importing_data_sources_and_creating_base_views/viewing_the_schema_of_a_base_view#viewing-the-schema-of-a-base-view) I hope this helps you!
Denodo Team
23-05-2017 08:55:14 -0400
code
Denodo Team, The source type matches the source table. However, when Denodo issues the query it is sends sql a parameter of NVARCHAR(4000). To SQL Server this is an explicit conversion and forces the query optimizer to choose a table scan every time. If this is not configurable, then we have a serious problem with performance. The query when executed using the right data type for the parameter returns in less than 500 milliseconds and over 1 minute when using NVARCHAR as the datatype.
user
23-05-2017 11:46:16 -0400
All, The support team really came through. The solution was to change the Data Source property "Allow literal as parameter" from Yes to No. This made an immediate impact and now the query response is similar to that of queries run on SQL server. Thanks Support Team! -John
user
24-05-2017 09:07:43 -0400
You must sign in to add an answer. If you do not have an account, you can register here