New Posts New Posts RSS Feed: VARCHAR treated as NVARCHAR
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

VARCHAR treated as NVARCHAR
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
aschaff View Drop Down
Newbie
Newbie
Avatar

Joined: 28-May-2009
Posts: 13 		Post Options Post Options   Quote aschaff Quote  Post ReplyReply Direct Link To This Post Topic: VARCHAR treated as NVARCHAR
    Posted: 20-Jun-2012 at 1:28pm
Hello.
For a while now we have been aware that DevForce uses parameterized queries and treats the string parameters as unicode, even if the entity was built from a table with varchar columns instead of nvarchar. In some cases this is causing us performance problems. In particular, when the column involved is (a) varchar, and (b) indexed but NOT part of the primary key of the table, then the index is NOT used resulting in terrible performance if the table has a lot of rows.

Here is an example of one of these queries that specifies nvarchar for the OrderNum and places an "N" prefix in front of the value ('1121') even though this is a varchar field:

exec sp_executesql N'select * from "dbo"."fOrderStep" where (("dbo"."fOrderStep"."OrderStepSequenceNum"=@P1 and "dbo"."fOrderStep"."OrderNum"=@P2))',N'@P1 float,@P2 nvarchar(4)',10,N'1121'

I need a way to prevent this treatment of varchar as nvarchar. Can anyone help me out?
-Adam
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 20-Jun-2012 at 3:22pm
Hi Adam,
 
In ADO.NET the "String" CLR type is considered a Unicode string, which is then interpreted as an nvarchar database type. So DevForce is just allowing this default to occur.   You can work around the problem with a custom "AdoProviderHelper" which will allow you to take control of the database type.

If you're using the SQLOLEDB provider you can implement the helper like this:
public class SampleProviderHelper : OleDbProviderHelper {
    public override DbType MapDbParameterType(DbType pDbType) {
      return pDbType == DbType.String ? DbType.AnsiString : pDbType;
   }
}

If you're using the SQLClient provider you'd instead subclass the SqlServerProviderHelper class:

public class SampleProviderHelper : SqlServerProviderHelper {.. }

Both OleDbProviderHelper and SqlServerProviderHelper are defined in IdeaBlade.Rdb.

For DevForce to find your custom implementation be sure that the assembly it's in is defined as a probe assembly for the RdbKey.
Back to Top
aschaff View Drop Down
Newbie
Newbie
Avatar

Joined: 28-May-2009
Posts: 13 		Post Options Post Options   Quote aschaff Quote  Post ReplyReply Direct Link To This Post Posted: 20-Jun-2012 at 6:46pm
Kim,
Thanks. That seems like a start. However, our database is a mix of both varchar and nvarchar, so neither choice (ansi or unicode) is correct all of the time. Ideally, I'd like to base it on the dbtype of the source sql column from which the entity column originated. If that is not possible, next best would be to test the column name, since our naming conventions provide a fairly reliable indicator. But I do not see any parameters to that override method that would help me to access the EntityColumn at issue. Any thoughts?

-Adam

p.s.
I guess my third choice, of last resort, would be to default to unicode (since that's what we have today) and test some kind of global that we can set and clear before and after the query is executed. Not pretty, but it's a lifeline I guess.
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 20-Jun-2012 at 7:38pm
This is going to be tough.  Unfortunately, there's no useful information, such as table or column name, coming into the provider helper that you can capture and use.  Sorry, option 3 may be the only one that will work.
Back to Top
ctoth View Drop Down
Newbie
Newbie
Avatar

Joined: 04-Mar-2013
Location: Fresno, CA
Posts: 22 		Post Options Post Options   Quote ctoth Quote  Post ReplyReply Direct Link To This Post Posted: 30-Jul-2013 at 10:44am
Do you have some more complete example about extending OleDbProviderHelper ? What else need to be done besides extending it?
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 30-Jul-2013 at 11:14am
The AdoProviderHelper is unfortunately not documented, other than class information in the help reference, and we don't have any samples showing the how/when/why of using it.
 
The code snippet in the second post in this thread is actually complete - you can copy the SampleProviderHelper class into a file, add the file to a project, and be sure the project's assembly is included in the probe assemblies for your RdbKey.  DevForce will use your implementation if its found.  There are a number of other members which can be overridden too.
 
If you're running into problems please provide more specifics on what you need to do or what's not working.
Back to Top
ctoth View Drop Down
Newbie
Newbie
Avatar

Joined: 04-Mar-2013
Location: Fresno, CA
Posts: 22 		Post Options Post Options   Quote ctoth Quote  Post ReplyReply Direct Link To This Post Posted: 30-Jul-2013 at 11:19am
Thanks kimj, that information is enough. So the assembly has to be in the list of probe assemblies in IdeaBlade.config.
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 30-Jul-2013 at 11:23am
Specifically for the RdbKey.  DevForce will look at the "global" probeAssemblyNames for some things, and the key-specified ones for others.  Here it will look for the key's probe assemblies.
Back to Top
ctoth View Drop Down
Newbie
Newbie
Avatar

Joined: 04-Mar-2013
Location: Fresno, CA
Posts: 22 		Post Options Post Options   Quote ctoth Quote  Post ReplyReply Direct Link To This Post Posted: 30-Jul-2013 at 11:29am
Oh! I missed that, thanks for warning me! That's important!
Back to Top
ctoth View Drop Down
Newbie
Newbie
Avatar

Joined: 04-Mar-2013
Location: Fresno, CA
Posts: 22 		Post Options Post Options   Quote ctoth Quote  Post ReplyReply Direct Link To This Post Posted: 01-Aug-2013 at 11:32am
Just to let you know it seems that in our current configuration we specify that assembly in the global probe assembly list (not for the RdbKey probe assembly list), and it seems to work that way too: when I profile the SQL queries I see varchar parameters passed. So we don't have an nvarchar/varchar conversion problem I think.
Back to Top
JoshO View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Location: United States
Posts: 86 		Post Options Post Options   Quote JoshO Quote  Post ReplyReply Direct Link To This Post Posted: 07-Jan-2015 at 8:21am
Posted on behalf of user toddgleason :

At first we tried the solution mentioned, involving a SqlServerProviderHelper (not sure if I got that class name right). This actually worked fine for most tables, but for any tables with XML columns, those would no longer come back correctly from the DB. They needed to be encoded in NVARCHAR and no longer were.
 
I then began investigating whether an IAdapterProvider solution was the way to go.  After fiddling with this, and tracing through DevForce code in Reflector, I realized that this was only mostly dealing with the object schema and not really involved with the actual parameters involved in a fetch operation.
 
The solution I ended up with seems to be good. It hinged on the fact that we subclassed the PersistenceManager with a company-specific version long ago, and have faithfully used our own class.  This is critical because we created "new" versions of the GetEntity()/GetEntities()-style methods. (These really should have been virtual to begin with; then our coverage would have been even more complete, but we think it's good enough for this case.)
 
Within our GetEntity()/GetEntities() methods, we "promote" the IEntityQuery into a company-specific class derived from RdbQuery. (We do this for EntityQuery and RdbQuery but not pass-thru queries.)  And in our RdbQuery subclass, we override the Fetch() method of RdbQuery to do everything the base class does, but also tweak the query parameter types:
  
 
        /// <summary>
 
        /// This method is not intended to be called directly from your code.
 
        /// </summary>
 
        /// <param name="pDataSet">The dataset holding fetched data</param>
 
        /// <param name="pDataSourceKey">The data source key containing connection information for the datasource.</param>
 
        /// <remarks>
 
        /// This method executes on the server side of the Persistence divide to
 
        /// retrieve data from the backend datasource.
 
        /// </remarks>
 
        public override void Fetch(DataSet pDataSet, IDataSourceKey pDataSourceKey)
 
        {
 
            AdoHelper adoHelper = ((RdbKey)pDataSourceKey).AdoHelper;
 
            RdbQuerySqlFormatter formatter = new RdbQuerySqlFormatter(adoHelper, (base.QueryStrategy == null) ? null : base.QueryStrategy.TransactionSettings);
 
            CanonicalSqlQuery pQuery = formatter.BuildCanonicalQuery(this);
 
            ParameterizedSql pParamSql = formatter.BuildSqlSelect(pQuery);
 
 
 
            // SQL has an issue with sending varchar columns as nvarchar; see
 
 
            // A global type mapping change can be done,
 
            // but it fails because XML columns need to be sent in Unicode instead of using the DB collation, and conversion to ANSI breaks compatibility with such columns.
 
            // So the solution is to instead, at fetch time, replace parameters marked as NVarChar with VarChar
 
            // It's unlikely we actually query by those XML parameters so this should be relatively safe.  This only affects
 
            // the parameters sent out in the query, not the return type mappings.
 
            // One other thing we do is that in our own PersistenceManager subclass's GetEntity()/GetEntities() calls, we "promote" all
 
            // EntityQuery and RdbQuery objects to our derived objects, to guarantee invoking this class.
 
            foreach (var param in pParamSql.Parameters)
 
            {
 
                if (param.DbType == DbType.String)
 
                    param.DbType = DbType.AnsiString;
 
            }
 
 
 
            using (formatter)
 
            {
 
                formatter.Fetch(base.EntityType, pDataSet, pParamSql, this.CommandTimeout);
 
                this.FetchSpans(pQuery, pDataSet, adoHelper, formatter);
 
                if (base.ContainsSubquery && !this.SuppressQueryInversion)
 
                {
 
                    this.FetchInverted(pDataSet, formatter, this);
 
                }
 
            }
 
        }
 
 
 
You may also note that in here we could put extra intelligence in if we needed to apply this logic conditionally, such as by parameter name, or we could add to the query class to control the behavior. 
 
The resulting solution has zero effect on the data type transfer in either direction; it only affects the query parameters, which is exactly what we want.
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 11-May-2015 at 3:13pm
Here's a more recent example of a custom provider helper which is able to detect column information. The example below detects Xml data types and excludes columns of this type from parameter mapping for insert/update/delete statements.

public class CustomProviderHelper : IdeaBlade.Rdb.SqlServerProviderHelper {

   // Reset ALL strings to Ansi strings for select/insert/update/delete parms
   public override System.Data.DbType MapDbParameterType(System.Data.DbType pDbType) {
     return pDbType == System.Data.DbType.String ? System.Data.DbType.AnsiString : pDbType;
   }

   // This is called by the RdbAdapterProvider when building insert/update/delete statements.
   // We have enough column information here to detect XML types, and reset the parameter.DbType.
   public override void QualifyParameter(System.Data.IDbDataParameter pParameter, System.Data.DataRow pSchemaRow) {

     // Do base logic
     base.QualifyParameter(pParameter, pSchemaRow);

     // This was typed as an ansi string by MapDbParameter, reset to correct Xml datatype.
     if ((Type)pSchemaRow["ProviderSpecificDataType"] == typeof(System.Data.SqlTypes.SqlXml)) {
        pParameter.DbType = System.Data.DbType.Xml;
     }

     // There may be other useful schema info here too - like pSchemaRow["DataTypeName"]
   }
}
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down