Hi there,
Our business is having a problem when using a queryEntity in Appian for a certain field. It converts that field to an NVARCHAR(4000) (I'm assuming because it's a text field) even though our Database and Data Type have it listed as VARCHAR(50). In SQL Server, when comparing an NVARCHAR to a VARCHAR, it causes an implicit conversion of all data in the VARCHAR column which is a big performance hit for us.
Is there any way to make sure that the parameter uses VARCHAR(50) instead of NVARCHAR(4000)?Thank you
Discussion posts and replies are publicly visible
A bit of a guess here but it's likely an issue that can be seen/resolved in the XSD for the data type you are using. When you've got the data type open go to the cog in the top left corner then download XSD. Can you post the full <element> tag of the field that is giving you an issue? See below for an example.
<xsd:element name="error" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo> </xsd:annotation> </xsd:element>
I checked that already but here's the XSD for the Element.
<xsd:element name="UniquePatientId" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="UniquePatientId", nullable=false, columnDefinition="VARCHAR(50)")</xsd:appinfo> </xsd:annotation> </xsd:element>
Thanks. Where specifically do you see that the data type is NVARCHAR(4000)?
I think we need more context on how you're using this. One thing to keep in mind is that within Appian, text values don't have limits on character lengths - the limits are entirely to determine how to connect to the database correctly. Because of that, a!queryEntity() doesn't do a cast between types or define a certain length - regardless of what length the database has, the value in Appian is just a text.
Based on your question, it sounds like the problem may be occurring in the database and not in Appian. How are you doing the comparison between fields of NVARCHAR to VARCHAR? Is this for a database view (and if so, can you share the definition of the view)?
When I look at our code in Azure Portal, I can see that the query has turned into this SQL:
In our Appian, when I use Ad Hoc Test in Appian rules (in this case, for the queryEntity), our text input box has a limit of 4,000 characters. The problem is in the database but it's happening when Appian's query is translated into SQL code.
This query is specifically for a table. The SQL definition for the column is this:
[UniquePatientId] VARCHAR (50) DEFAULT (CONVERT(VARCHAR(50), newid())) NOT NULL
I feel like I'm missing something. What query turned into SQL? Now that we're in Azure Portal (new info here!) this introduces another place where a conversion could occur. Not saying it is Azure Portal but just that it's another place where it could be.
Can you confirm if nvarchar is in the database (not using Azure Portal)? I feel the XSD in Appian has mostly likely eliminated Appian as the issue but it would be good to confirm what the actual DB says. Your earlier comments suggest it also shows varchar(50) but I'm not 100% sure.
Also, if there are any views involved in this set up it would be good to confirm those too.
A text field in Appian is in no way related to any SQL code. The characterLimit parameter does only control the numbers of characters a user can type into the field.
Hi jimmyn253 Did you find any resolution to this ?We are also facing the same problem. Due to varchar to nvarchar conversion, DB is not picking the right index to filter data from table. And it is causing huge performance issues sometimes. In our case also, its SQL Server.
You can leverage your connection string to your db to prevent the connection from generating your queries with N prefixes for strings.
sendStringParametersAsUnicode=false for SQL Server
sendStringParametersAsUnicode=false
By default, this is set to true.
If the sendStringParametersAsUnicode property is set to true, which is the default value, string parameters are sent to the server in UNICODE format. If sendStringParametersAsUnicode is set to false string parameters are sent to the server in an ASCII/MBCS format, not in UNICODE.
You should not use this setting if your application / db stores unicode data or could do so. If you want to have the best of both worlds, you can leverage a second data source that has this set to false for tables and views where unicode support is not needed, and the other where the default / true is set for tables and views that should support unicode.
According to Appian's supported JPA annotations there is no support for @Nationalized which if supported may allow you to properly set this flag at the connection level and then have your nationalized columns prepared with nationalized columns.
@Nationalized
I will note that hibernate does support this annotation, so it stands to reason this would work as Appian uses (or did use) hibernate, but I have not tested this.
If storage space is less of a concern, an alternative to this connection based approach would be to convert your columns to nationalized versions which would avoid your implicit conversion performance issues. If you do that only on columns you index or search against then you should be able to find a middle ground between storage costs and performance at scale. As always I strongly recommend you test all of these options yourself to determine the best solution for your individual needs.
Reference Material: