Closed gnarfle closed 1 year ago
Thanks for the detailed report. I'll try to look into it this week.
I'm having the same problem, but with the 5.0.7 version of the gem.
I did notice, that IF I used symbol syntax when constructing my query in rails it was correct. (no N'')
If I had a query that used a sql string, I had the incorrect meta type of N''
What are your thoughts?
Any update? We're still seeing this issue. In addition to the example I posted I'm also finding it on WHERE IN(field: [array]) queries too, oddly just on the first item in the array.
This issue isn't present in version 6.0 or 6.1 of the SQL Server adapter. It does happen in v5.2.1 as detailed in the original description.
SQL Server Adapter: 5.2.1 Rails: 5.2.6
SELECT [test3].* FROM [test3] WHERE [test3].[name] = 'asdf'
SELECT [test2].* FROM [test2] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = 'asdf'
SELECT [test1].* FROM [test1] INNER JOIN [test2] ON [test2].[test1_id] = [test1].[id] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = N'asdf'
SQL Server Adapter: 6.0.2 Rails: 6.0.4
SELECT [test3].* FROM [test3] WHERE [test3].[name] = 'asdf'
SELECT [test2].* FROM [test2] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = 'asdf'
SELECT [test1].* FROM [test1] INNER JOIN [test2] ON [test2].[test1_id] = [test1].[id] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = 'asdf'
SQL Server Adapter: 6.1.0.0 Rails: 6.1.4
SELECT [test3].* FROM [test3] WHERE [test3].[name] = 'asdf'
SELECT [test2].* FROM [test2] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = 'asdf'
SELECT [test1].* FROM [test1] INNER JOIN [test2] ON [test2].[test1_id] = [test1].[id] INNER JOIN [test3] ON [test3].[test2_id] = [test2].[id] WHERE [test3].[name] = 'asdf'
Closing this issue as it affects version 5.2 of the adapter which is no longer supported.
Issue
Recently we began the process of updating a rails 5.1 application to rails 5.2 and found that each time we tried, we would see horrible database performance and 7+ second queries slowing things way down. Eventually we traced this back to an issue we had in rails 4.x but had seemed to be fixed in 5.x, which is the adapter using N'' quoting on varchar (not nvarchar) columns. It seems that in this case we're seeing this happen when we add a where clause on a joined relation and not when we query a single table.
Expected behavior
We should use N'' quoting only on nvarchar columns and '' on varchar columns
Actual behavior
We are seeing N'' quoting used on varchar columns, leading to slow performance on large tables.
How to reproduce
I've managed to narrow this down to a case where it's going through two joins...
With the following three tables:
I then made the following 3 models for these tables:
With those models you can now run the following 3 queries and see that they all work ok, until you add in two joins:
Details
5.2.4.3
5.2.1
1.3.0
1.1.15