disabling N'...' quoting for strings

[bpokosh]

I'm told that for our database using the following syntax is hurting performance:

SELECT FROM table WHERE field = N'...'

Because the field is a varchar (not unicode), being compared to a nvarchar value (unicode) is causing a performance hit.

Looking at the Quoting module it doesn't look like there is a way to override this behavior. Does it make sense to disable it when enable_default_unicode_types is false?

[metaskills]

We have covered this topic in great detail on the google mailing list. Please do a search and see if you can track down the thread. Basically...

1. The quoting is either all on are all off. Since the same quoting is used weather you are building conditions or inserting data. You can override the method if you want, but it means you will never be able to use unicode data in your queries.

2. The #enable_default_unicode_type is for migrations. I am not of the opinion that I want a config for this quoting behavior since there is never a good fit one way or the other. So if I had a config, people would be then say well I then need to temporarily reconfig it for this table since it does have unicode data/columns and I need it to work here, then not there. So leaving it as a method redefine that you can do in a config/initializer would be intentionally hard so you can understand and be responsible for the ramifications of this change.

3. I have been bitten by this before on VERY large tables when upgrading too. We just simply changed the data type and re-indexed. Far easier to move forward with convention, IMHO. If you disagree, your gonna need to solve it as you see fit using solutions above. Not hard, but comes with decisions and a cost.

4. If your asking if the quoting could be done per column etc. No, it's not that easy. I covered this in details in threads on the mailing list.

[metaskills]

I'll also point out this clever hack by @chudler https://gist.github.com/1473140 where he wraps certain parameters in this class so that they do not suffer the performance penalty on legacy DBs that index varchar columns.

[metaskills]

So given a class like this loaded via your core extensions or lib.

class NonNationalString < String
 def is_utf8?
   return false if calling_method == 'quote'
   super
 end
end

You would use that string class when passing down arguments so the adapter would not quote them.

MyModel.where(foo: NonNationalString.new('bar'))…

[ffabreti]

By now, is there any better solution than the above?
Because of quoting, I cannot access 'belongs_to' relationships without sqlserver-adapter adding N prefix to the queries (what ignores database indexes and make queries slowdown).

I tried monkey-patching to something like below, but no success.

module ActiveRecord
  module ConnectionAdapters
    module Sqlserver
      module Quoting
              def quoted_string_prefix
                  #QUOTED_STRING_PREFIX
                  ''
              end
      end
    end
  end
end

It gives error:

TinyTds::Error: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.: EXEC sp_executesql 'SELECT @@version'