> Stephen Dupre wrote:
> > The most common reason for this performance problem is:
> > 1) migration from CF5 (unicode datasource setting gets set to 'true'
> - table scan against varchar - known issue - see #2)
> > 2) having "Enable Unicode for data sources configured for non-Latin
> characters" ON.
> isn't that a bit contradictory? why would somebody who needs unicode turn on
> unicode but *not* use "N" datatypes?
Paul,
Good question. The answer is in point #1.
>> 1) migration from CF5 (unicode datasource setting gets set to 'true' - table
>> scan against varchar - known issue - see #2) <<
The CF5->MX MIGRATION that builds the neo-query.xml is supposed to turn off
UNICODE by default. It sets the value to "false".
Problem is ... the SQL Server driver setting takes either 1 or 0 and ignores
"false"... (bug we can't fix).
And the default for the unicode param (sendStringParametersAsUnicode) is 1
(ON). Keep this in mind.
So what you have with CF5 migrations is
1) a datasource checkbox for unicode in the CF admin that shows "UNCHECKED"
because the value ('false') doesn't match 1 or 0.
2) the DEFAULT from the driver getting you (because it doesn't understand
"false") turning unicode ON behind your back. This forces that implicit
conversion of any VARCHAR column query (where firstname='fred') and throws out
indexes.
The performance sucks and you can't figure it out from the CF end.
When I hear an immediate performance issue on a migrated (CF5->MX) app with SQL
Server, that's the #1 cause. Most people get around it by creating another
datasource during their investigation and inadvertantly seeing the problem go
away because new datasources have this unicode setting un-checked (and set to
0).
In another case, if you have ONE unicode column in the app (NTEXT), you might
use ONE datasource and turn this "unicode" setting "ON" in the SQL Server
datasource but it'll mess up 95% of your other queries with VARCHARS.
(including client variable lookups). You have to be acutely aware of your
datasources with SQL Server and use 2 of them if you have some mixed
unicode/non-unicode queries/INSERTS.
SQL Server datatype precedence screws you here too. (NVARCHAR higher than
VARCHAR)
So the moral is: Create 2 datasources with SQL Server JDBC and call one
"_Unicode" and one "_non-unicode" so no one trips over this problem (or at
least they'll ask why you did this before using them). For client vars, and
95% of your queries, use the non-unicode datasource. Use the unicode one only
when you're absolutely sure the column is NVARCHAR, NTEXT, etc.
Stephen Dupre
Adobe Systems, Inc
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion MX7 and Flex 2
Build sales & marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270855
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
