Last Update: 2014-05-01 08:02:17 -0700

Performance Enhancements

  • Dataset literalization for simple datasets is now faster by creating a per-adapter SQL literalization method instead of having all adapters share a generic method with higher overhead. Sequel.split_symbol now caches results globally. Symbol literalization is now cached per Database.

    Combining these three optimizations, here are the performance increases compared to 4.9.0 for a couple example datasets:

    ds1 = DB[:a]
    ds2 = DB[:a].select(:a, :b).where(:c=>1).order(:d, :e)
            .sql     .all (1 row)
    ds1     140%     11%
    ds2     187%     32%
  • Regular association loading now uses a placeholder literalizer in most cases, for up to an 85% improvement when loading simple associations.

  • Eager loading associations using Dataset#eager now uses a placeholder literalizer in most cases, for up to a 20% improvement when eager loading simple associations.

  • Eager loading associations with limits using Dataset#eager now uses a UNION-based strategy by default. After extensive testing, this was found to be the fastest strategy if the key columns are indexed. Unfortunately, it is a much slower strategy if the key columns are not indexed. You can override the default UNION strategy by using the :eager_limit_strategy association option.

    On some databases, execution time of UNION queries with n subqueries increases faster than O(n). Also, there are limits on the number of subqueries supported in a single UNION query. Sequel chooses a default limit of 40 subqueries per UNION query. You can increase this via the :subqueries_per_union association option.

  • Dataset#import and multi_insert can now insert multiple rows in a single query on H2, HSQLDB, Derby, SQLAnywhere, CUBRID, SQLite, Oracle, DB2, and Firebird, which should be significantly faster than previous versions that issued a separate INSERT query per row.

  • The many_to_many setter method in the association_pks plugin now uses Dataset#import to insert many rows at once, instead of using a seperate query per insert.

  • The jdbc adapter's type conversion has been rewritten to be more similar to the other adapters, setting up the type conversion procs before iterating over results. This increases performance up to 20%.

  • The jdbc/oracle adapter now defaults to a fetch_size of 100, similar to the oci8-based oracle adapter, significantly improving performance for large datasets.

New Features

  • Database#transaction now supports an :auto_savepoint option. This option makes it so that transactions inside the transaction block automatically use savepoints unless they use the :savepoint=>false option. This should make testing transactional behavior easier.

  • Model.prepared_finder has been added. This has an API similar to Model.finder, but it uses a prepared statement instead of a placeholder literalizer. It is less flexible than Model.finder as prepared statements have fixed SQL, but it may perform better.

  • Common table expressions (WITH clauses) are now supported on SQLite 3.8.3+.

  • :correlated_subquery has been added as an eager_graph and filter by association limit strategy for one_to_one and one_to_many associations. In certain cases it was found that this is faster than the :window_function limit strategy. It is the default filter by associations limit strategy on databases that do not support window functions.

    Filtering by limited associations using a correlated subquery strategy does not work in all cases, but it should handle most cases correctly.

  • The prepared_statement_associations plugin now handles one_through_one and one_through_many associations.

  • Sequel now emulates support for offsets without limits on MySQL, SQLite, H2, SQLAnywhere, and CUBRID.

  • In the jdbc adapter, the Database#fetch_size accessor and :fetch_size option can be used to automatically set the JDBC fetch size for JDBC Statement objects created by the database.

  • Dataset#with_fetch_size has been added to jdbc adapter datasets, setting the fetch size to use on ResultSets generated by the dataset. This generally has the effect of overriding the Database fetch_size setting.

  • On MySQL 5.6.5+, Sequel supports a :fractional_seconds Database option, which will use fractional seconds for timestamp values, and have the schema modification code create timestamp columns that accept fractional timestamps by default.

  • Database#call_mssql_sproc on Microsoft SQL Server now handles named parameters:

    DB.call_mssql_sproc(:sproc_name, :args => {
      'input_arg1_name' => 'input arg1 value',
      'input_arg2_name' => 'input arg2 value',
      'output_arg_name' => [:output, 'int', 'result key name']
  • Database#drop_view now supports an :if_exists option on SQLite, MySQL, H2, and HSQLDB.

  • Database#drop_table now supports an :if_exists option on HSQLDB.

  • A :filter_limit_strategy association option has been added, for choosing the strategy that will be used when filtering/excluding by associations with limits. For backwards compatibility, Sequel will fallback to looking at the :eager_limit_strategy option.

  • A :server_version Database option is now supported on Microsoft SQL Server, which will use the value given instead of querying for it.

Other Improvements

  • Dataset::PlaceholderLiteralizer arguments are how handled correctly when emulating offsets via the row_number window function on DB2, MSSQL <=2012, and Oracle.

  • Dataset::PlaceholderLiteralizer now handles DelayedEvaluation objects correctly.

  • Offset emulation is skipped if static SQL is used on Access, DB2, and MSSQL <=2008.

  • Additional disconnect errors are now recognized in the postgres adapter.

  • The :foreign_key_constraint_name option is now respected when adding a foreign key column to an existing table on MySQL.

  • Sequel now attempts to work around a bug on MySQL 5.6+ when combining DROP FOREIGN KEY and DROP INDEX in the same ALTER TABLE statement.

  • Dataset#for_update is now respected on H2.

  • Timestamp with local time zone types are now returned as Time/DateTime objects on jdbc/oracle.

  • Model.include now has the same API as Module.include.

  • Model#marshallable! now works correctly when using the tactical_eager_loading plugin.

  • The pg_array_associations plugin now attempts to automatically determine the correct array type to use, and explicitly casts to that array type in more places.

  • The auto_validations plugin now handles models that select from subqueries.

  • The association_pks plugin does no longer creates getter and setter methods for one_through_one associations.

  • bin/sequel now uses the Sequel code in the related lib directory. This makes it easier to use from a repository checkout.

Backwards Compatibility

  • AssociationReflection#associated_dataset now returns a joined dataset for associations that require joins (e.g. many_to_many). Anyone using this directly for associations that require joins probably needs to update their code.

  • Model.associate now adds the association instance methods instead of relying on the def_#{association_type} method doing so. Anyone using custom association types probably needs to update their code.

  • Model.eager_loading_dataset, .apply_association_dataset_opts, and .def_{add_method,association_dataset_methods,remove_methods} are now deprecated.

  • Key conditions for associations requiring joins have been moved from the JOIN ON clause to the WHERE clause. This should be optimized the same by the database, but it can break tests that expect specific SQL.

  • Dataset#_insert_sql and #_update_sql are now private instead of protected.

  • The install/uninstall rake tasks have been removed.

  • Model association and association reflection internals have changed significantly, if you were relying on them, you'll probably need to update your code.

  • Database transaction internals have changed significantly, if you were relying on them, you'll probably need to update your code.

  • Dataset literalization internals have changed significantly, with the Dataset#*_clause_methods private methods being removed. Custom adapters that used these methods should switch to using the new Dataset.def_sql_method method.

  • Common table expressions are no longer enabled by default in Sequel. External adapters for databases that support common table expressions should define Dataset#supports_cte?(type) to return true.

  • Support for RETURNING is no longer determined via introspection. External adapters for databases that support RETURNING should define Dataset#supports_returning?(type) to return true.

  • The new jdbc adapter type conversion code may not be completely compatible with the previous code. The currently known case where it is different is on jdbc/postgresql, when using an array type where no conversion proc exists, the returned object will be a ruby array containing java objects, instead of a ruby array containing ruby objects. It is recommended that jdbc/postgresql users using array types use the pg_array extension to avoid this issue.