Enable Unicode=slow performance

[dkerr]

My site is running CF MX6.1 and SQL Server 2000 using a JDBC driver. When I
edit the DSN in CF Admin to enable unicode, performance drops off big time and
the site hangs! When I uncheck the enable unicode, performance is great again.

I really need to "globalize" my app and must have unicode enabled so that my
cfqueryparam inserts chinese, arabic, etc. I don't want to have to add in the
Ns to all my queries.

I turned unicode on for another DSN/different DB and performance was fine and
the chinese/arabic work just fine, so it seems to be something specific to one
DSN/DB.

Why does turning on unicode crash the app? Is there anyting I can change in
the DSN/DB/CFADMIN to get performance back to normal or even improve it. The
site is unusable with unicode turned on so I'd really appreciate the help.

Thanks,
Don

[PaulH]

i think maybe you misunderstand the use for that DSN option. maybe
[url]http://www.sustainablegis.com/blog/cfg11n/index.cfm?mode=entry&entry=F9553D86-20[/url]
ED-7DEE-2A913AFD8651643F might help, basically turning on this option doesn't
make your text data unicode. it simply controls how cfqueryparam, etc. behave.

there shouldn't be any performance hit associated with using this option, all
it does is add "sendStringParametersAsUnicode=true" to the JDBC url. i suggest
it's something else. what JDBC driver? are the required columns "N" datatypes?

[dkerr]

Thanks Paul. I am trying to get cfqueryparam to work and I did find this blog
posting prior to submitting my post. That is why I was trying to enable
unicode in the DSN. I'm using the SQL Server DNS that comes with MX6.1
including an update in the macromedia_drivers.jar file. I forget which version
right now. All of my columns are nvarchar, ntext, etc. datatypes.

Yet, I still get a major performance/hang up of the site when I check off this
box. I was thinking that there might be something specific to the DNS or
Database settings that would cause it to hang. I tried restarting CF and SQL
Server but the problem persists.

One of my other DSN's work fine on the same server.

Any other ideas on things to check?

Thanks!
Don

[PaulH]

well i guess simple things first. delete, re-create that DSN. after that you
better double check your datatypes as i think that's the only thing that might
effect performance (though never to the point of hanging). if you pass unicode
data in a WHERE clause against a non-N datatype column, you could force a table
scan vs using an index (reverse is true too i think). i guess you better also
look into whan the indexes on that table were created (ie did you change the
schema to use unicode w/out re-indexing). though i think that's more of a long
shot.