activerecord-sqlserver-adapter unicode slow performance

Question

I'm on Rails 3.2.18 and MRI-Ruby-2.0.0

Gemfile:

gem 'tiny_tds'
gem 'activerecord-sqlserver-adapter'

$ gem list | grep -E "tds|sql"

activerecord-sqlserver-adapter (3.2.12)
sqlite3 (1.3.9)
tiny_tds (0.6.2)

database.yml:

development:
  adapter: sqlserver
  database: DBTEST
  username: domain\username
  password: somepwd
  host: myhostname
  port: 1433
  timeout: 180000

Problem: Queries take too long to complete. See:

> MyModel.find_all_by_NRPIS '12700333166'
  MyModel Load (85869.3ms)  EXEC sp_executesql N'SELECT [MyTable].* FROM [MyTable] WHERE [MyTable].[NRPIS] = N''12700333166'''

So I see the problem is in N unicode conversions, because when I query without it, its really fast, see:

   >> ActiveRecord::Base.connection.select_all   "SELECT [MyTable].* FROM [MyTable] WHERE [MyTable].[NRPIS] = '12700333166'" }
   (62.5ms)  EXEC sp_executesql N'SELECT [MyTable].* FROM [MyTable] WHERE [TBCAGEDTrabalhador].[NRPIS] = ''12700333166'''

   >> ActiveRecord::Base.connection.select_all   "SELECT [MyTable].* FROM [MyTable] WHERE [MyTable].[NRPIS] = N'12700333166'" }
   (102324.0ms)  EXEC sp_executesql N'SELECT [MyTable].* FROM [MyTable] WHERE [MyTable].[NRPIS] = N''12700333166'''

What is happening here and how can I solve the issue ?

I have seen THIS QUESTION, but I would really like to tell sqlserver-adapter not to convert anything to unicode.

Fernando Fabreti · Answer 1 · 2014-07-20T20:32:39.350

Found it.

The issue has been discussed here: disabling N'...' quoting for strings #124

Problem is quoting of utf-8 strings. Besides from the solution above, maybe you can force other encoding so that strings don't get quoted, something like:

> a = MyModel.where "NRPIS = ?", '12700233166'.force_encoding("ISO-8859-1")
 MyModel Load (53.1ms)  EXEC sp_executesql N'SELECT [MyTable].* FROM [MyTable] WHERE (NRPIS = ''12700193166'')'

As additional info, the table which MyModel refers to has no primary_keys defined, only a Index for NRPIS.

I've notice that when I query against tables that have primary_keys defined and you query exactly the primary_keys, the index is NOT ignored and the query runs fast even with unicode quoting.

activerecord-sqlserver-adapter unicode slow performance

1 Answers1