Rails 3.1 and Prepared Statement Support
Ken Collins
FYI, I have a "Rails31" branch up now that passes the current state of rails 3.1 using DBLIB/TinyTDS.
https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/tree/Rails31
The changes for the ODBC should be pretty easy and I'll circle back to it shortly as well as test things for 2005 and SQL Azure. I am also waiting for Aaron or someone else on core to do one more pull request too. https://github.com/rails/rails/pull/302
Tho riding on the edge is not for everyone, I did want to get a head start into the prepared statement support that will be coming. I did a lot of reading on the topic of Execution Plan Caching and Reuse <http://msdn.microsoft.com/en-us/library/ms181055.aspx> and setteled on using the sp_executesql stored procedure as it was a solid way of using TSQL only vs binding us to a specific low level connection mode. I played around with things like sp_get_query_template and sp_create_plan_guide but those would have made the connection user need admin level perms as well as require a lot of work to round trip things to and from the server. With sp_executesql we get good plan reuse too. This topic may be new to some, so let me demonstrate.
@client = TinyTds::Client.new :host => 'mydb.net', :username => 'sa'
500.times do
@client.execute("SELECT TOP(1) * FROM [table] WHERE [id] = #{rand(50000)}").do
end
That will execute 500 queries, all of which have different separate query plans in SQL Server's cache. See for your self.
SELECT [usecounts], [cacheobjtype], [objtype], [text]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY [usecounts] DESC
Assuming you have the permissions, flush the cache with this. "DBCC FREEPROCCACHE WITH NO_INFOMSGS". Now here is what would happen in ActiveRecord 3.1 with the latest adapter.
500.times do
@client.execute("
EXEC sp_executesql N'SELECT TOP(1) * FROM [datatypes] WHERE [id] = @0',
N'@0 int',
@0 = #{rand(50000)}
").do
end
This will create a single plan in the cache that is reused. Given this contrived example on my computer, the first 500 selects take 2.3 seconds while the second 500 take less that one. Now I do not purport to be a SQL Server wizard and I would enjoy some good ole fashion QA and feedback on the 3.1 code base.
- Cheers,
Ken
k...@metaskills.net
I just pushed the changes to the Rails31 branch to support ODBC. A few
more notes about sp_executesql. It will silently truncate strings for
inserts as it appears that was made with ansi warning to off. Also,
the latest 3.1 adapter only supports the 0.9992 and up for Ruby ODBC
since that had optimizations to make returning native ruby primitives
in row sets along with correct time zone support. Please update if you
plan on using this.
Any cheers or jeers???
- Ken
On Apr 24, 8:21 pm, Ken Collins <k...@metaskills.net> wrote:
> FYI, I have a "Rails31" branch up now that passes the current state of rails 3.1 using DBLIB/TinyTDS.
>
>
> The changes for the ODBC should be pretty easy and I'll circle back to it shortly as well as test things for 2005 and SQL Azure. I am also waiting for Aaron or someone else on core to do one more pull request too.https://github.com/rails/rails/pull/302
k...@metaskills.net
FYI, I just pushed the v3.1.0.beta1 gem to rubygems.org.
http://rubygems.org/gems/activerecord-sqlserver-adapter/versions/3.1.0.beta1
- Ken
k...@metaskills.net
Here are some simple benchmarks I did today. Both simple and complex
SQL seem to get a HUGE performance boost!
https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/test/profile/query_plan_simple.rb
https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/test/profile/query_plan_complex.rb