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: 1278 		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: 1278 		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
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down