activerecord-sqlserver-adapter unicode缓慢的性能

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

我在3。2。18和mri - ruby -2。0轨道上

Gemfile:

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

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

$ gem列表| 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:

所以我发现问题在N个unicode转换中，因为当我没有它查询时，它真的很快，看:

   >> 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.

我已经看到了这个问题，但是我真的希望告诉sqlserver-adapter不要将任何内容转换为unicode。

1 个解决方案

#1

Found it.

找到了。

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

这里讨论了这个问题:禁用N'…"引用第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:

问题是引用utf-8字符串。除了上面的解决方案，也许你可以强制使用其他编码，这样字符串就不会被引用，比如:

> 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.

作为附加信息，MyModel引用的表没有定义primary_keys，只有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.

我注意到，当我对定义了primary_keys的表进行查询时，您查询的正是primary_keys，索引不会被忽略，即使使用unicode引用，查询也会快速运行。

#1