Re: [SQLDownUnder] .net sql provider and automatic char to nchar conversion

[Rob Farley]

Thanks Aku...

I think you need to look at where you create your parameters. If
you're telling them to pass in nvarchar values, then you will need to
alter that. If the code within that if-statement ever executes, then
I'd be looking for what created the parameter that way.

Rob



On 12/02/07, Aku Sopanen <Aku.Sopanen@xxxxxxxxxx> wrote:
> Hi Rob,
>
> Firstly congrats on gaining your MVP status - good work!
>
> ----
>
> We don't use nvarchar as we only need english, so datatype of b is
> either char or varchar. This results in performance issue as char and
> nchar cannot be directly compared, and translation must take place
> beforehand.
>
> What I would like to figure out is where and why this conversion (SQL
> statement reformatting) takes palce and what can do about it.
>
> And yes, we have too many string comparison operations in our database,
> by not my design I like to add...
>
> I know I could convert every relevant field field into nchar, but is
> this really the only option?
>
> Also one of our greenfield projects had, I believe, a related issue with
> dynamic SQL for which the following hack applied.
>
> **********
>
> if ((fieldParameter is SqlParameter) && (fieldParameter as
> SqlParameter).SqlDbType == SqlDbType.NVarChar)
> {
>       (fieldParameter as SqlParameter).SqlDbType = SqlDbType.VarChar;
>       //###MKP TODO THIS IS A TOTAL HACK
>       //### Was getting internal error- Datafield reporting incorrect
> length RUN DBCC CHECK
>       //### This occurred when the same dataadpter was run with a
> parameter length 1 higher than
>       //### than its first run. - only solution was not to make varchars
> a parameter anymore.
>       clause = new Clause(string.Concat(FieldName, " ", _operator, " '",
> fieldParameter.Value + "'"), FieldName);
> }
> else
> {
>       clause = new Clause(string.Concat(FieldName, " ", _operator, " ",
> fieldParameter.ParameterName), FieldName);
>
> }
>
> **********
>
> Cheers
>
>
> -----Original Message-----
> From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
> [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Rob Farley
> Sent: Friday, 9 February 2007 5:50 PM
> To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
> Subject: Re: [SQLDownUnder] .net sql provider and automatic char to
> nchar conversion
>
> What type is 'b' in your database? Is it char/varchar, or
> nchar/nvarchar?
>
> If it's char/varchar, then N'string' could mean that it's trying to
> convert column b to nchar/nvarchar before doing the comparison, which
> will certainly kill performance.
>
> So you could try doing something like "cast('string' as varchar(50))"
> (or whatever the size should be). If it ends up translating it to
> "cast(N'string' as varchar(50))" then that won't really matter,
> because it'll do the conversion on the right-hand side of the
> equation, and peformance should be better again.
>
> Rob
>
>
>
> On 09/02/07, Aku Sopanen <Aku.Sopanen@xxxxxxxxxx> wrote:
> >
> >
> >
> >
> >
> > Gday,
> >
> >
> >
> > When I create a hyperion report sql sent to server gets reformatted
> with
> > n'string'
> >
> >
> >
> > e.g.
> >
> >
> >
> > select a from table where b = 'string'
> >
> >
> >
> > gets formatted as
> >
> >
> >
> > select a from table where b = N'string'
> >
> >
> >
> > and kills performance....
> >
> >
> >
> > Now testing reporting services this isue has come up again (With
> hyperion we
> > had to switch to ODBC)
> >
> >
> >
> > What can I do to block this from happening?
> >
> >
> >
> > Cheers to unsubscribe to this list, please send a message back to the
> list
> > with 'unsubscribe' as the subject. Powered by mailenable.com - List
> managed
> > by http://www.readify.net to unsubscribe to this list, please send a
> message
> > back to the list with 'unsubscribe' as the subject. Powered by
> > mailenable.com - List managed by http://www.readify.net
>
>
> --
> Rob Farley
> President - Adelaide SQL Server User Group
> rob_farley@xxxxxxxxxxx (Email and Msgr)
> http://msmvps.com/blogs/robfarley
>
>
> to unsubscribe to this list, please send a message back to the list with
> 'unsubscribe' as the subject. Powered by mailenable.com - List managed
> by http://www.readify.net
>
>
> to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
--
Rob Farley
President - Adelaide SQL Server User Group
rob_farley@xxxxxxxxxxx (Email and Msgr)
http://msmvps.com/blogs/robfarley


to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net