I ran into this old thread while researching cfqueryparam and performance
issues.
Stephen,
Your idea about 2 seperate datasources for unicode and non-unicode queries is
useful for some queries. But it does not solve issues with queries where
unicode and non-unicode fields are used in same query.
In our database, all config/generic tables/fields are varchar. Whereas only
fields that can possibly have unicode characters are nvarchar. Now when these
tables/fields need to be joined or otherwise used in a single query, I cannot
use cfqueryparam (or use cfqueryparam with 'String Format' checked in CFAdmin
datasource definition and cause performance issues).
The right solution would be for CF to have a datatype called cf_sql_Nvarchar
for when you want to specify the datatype as unicode.
Have anyone else run into this issue? How have you resolved this?
> > 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327336
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
